Solved

ADODB Connection

Posted on 2001-09-13
14
226 Views
Last Modified: 2010-05-02
I'm trying to create a "generic" connection to either an Access or SQL db depending on the needs.

I am new to VB COM development but have been writing ASP and VBScript for some time.

Here is my Code where I am attempting this.  I'm not sure if I'm going about this the right way or not.  Any assistance is appreciated.

'*****************************************

Public Property Set DBConnectionSettings(ByVal strSource As String, ByVal strCatalog As String, ByVal strUserID As String, ByVal strPassword As String)
   
    If intDBType = 1 Then
       
        With oConn
            .Provider = "SQLOLEDB.1"
            .ConnectionString = "Password=" & strPassword & ";User ID=" & strUserID & ";Initial Catalog=" & strCatalog & ";Data Source=" & strSource & ";"
        End With
       
     
    ElseIf intDBType = 2 Then
       
        With oConn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & strSource
        End With
       
    End If
End Property
Public Property Get DBConnectionSettings()
    Provider = DBConnectionSettings.Provider
    ConnectionString = DBConnectionSettings.ConnectionString
End Property

                                   
Public Function GetDBConnection() As Object
    With oConn
        .Provider = DBConnectionSettings.Provider
        .ConnectionString = DBConnectionSettings.ConnectionString
        .Open
    End With
End Function
0
Comment
Question by:raizon
  • 7
  • 7
14 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 6480108
Are you trying to build a connection component, or simply to establish a connection and use it inside a VB app?

When you set properties like you indicate, it implies that you're building or enhancing a component for use outside the component.  This is not really necessary, but not necessarily bad.


Also, you public function is not returning anything since you're not setting the function name to anything; also, returning an object is not usually preferred; instead you probably want to return a connection datatype:

Public Function GetDBConnection() As Connection
' where is oConn defined???
   With oConn
' where is DBConnectionSettings defined?
       .Provider = DBConnectionSettings.Provider
       .ConnectionString = DBConnectionSettings.ConnectionString
       .Open
   End With

   GetDBConnection = oConn
End Function
0
 
LVL 5

Author Comment

by:raizon
ID: 6480551
The connection is going to be used inside of the component in other methods.  I've got 4 other methods that will use the connection.  But I want the developer who uses the component to be able to specify the connection type, db name, etc...

Thanks for the tip on the connection datatype.
0
 
LVL 5

Author Comment

by:raizon
ID: 6480856
Also I'm getting the following error on the Public Property Set... line

"Defenitions of Preperty procedures for the same property are inconsistent, or property procedure has an optional parameter, a Param Array, or an invalid Set Final Parameters"
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6480884
I think that maybe an explanation on how I would prepare such a project would help, but I'll have to get back to you in a while (later today or tomorrow.)

Meanwhile, the concept of the Property Set/Let/Get is to assign/retrieve data from a class' internal "memory."

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6480984
OK...

What you want to do here is to create an object that establishes a connection, then holds that connection internally until released, right?

I would start by using the class wizard (do you know how to get that?) to create a class with the following (public) pieces:

Property - DBSource
Property - DBCatalog
Property - DBUser
Property - DBPassword
Method - MakeConnection
Method - ReleaseConnection

Private method DBConnectionSettings(ByVal strSource As String, ByVal strCatalog As String, ByVal strUserID As String, ByVal strPassword As String)

...
The idea here is that each property of a class is a single value, and you're trying to treat a method as a property.

Instead, what you should do is to create a series of properties to hold the necessary values, then make a method available to process those properties to establish the connection.  To make the method convenient, you could also allow the user to specify those properties and essentially override the property values.

I would do this as follows:

Public Sub MakeConnection(ByVal Optional strSource As String, ByVal Optional strCatalog As String, ByVal Optional strUserID As String, ByVal Optional strPassword As String)
  ' If any values were omitted, use the values from the corresponding properties.
  if IsMissing(strSource) then
    strSource = DBSource
  end if
  if IsMissing(strCatalog) then
    strCatalog = DBCatalog
  end if
  if IsMissing(strUserID) then
    strUserID = DBUserID
  end if
  if IsMissing(strPassword) then
    strPassword = DBPassword
  end if

  ' Now establish the connection and save it in an internal module-level variable
' Private oConn as Connection -- defined at top of class module
   With oConn
       .Provider = DBConnectionSettings.Provider
       .ConnectionString = strSource & ";" & strCatalog & ";" & strUserID & ";" & strPassword & ";"
       .Open
   End With
End Sub

' If you really want, you could turn the following into a private function that returns a string
'DBConnectionSettings.ConnectionString

...
with oConn
...
.ConnectionString = DBConnectionSettings(strSource, strCatalog, strUserID, strPassword)
...

Private function DBConnectionSettings(ByVal Optional strSource As String, ByVal Optional strCatalog As String, ByVal Optional strUserID As String, ByVal Optional strPassword As String) as string
  DBConnectionSettings = strSource & ";" & strCatalog & ";" & strUserID & ";" & strPassword & ";"
End Function


---
Does that help, or simply confuse???
0
 
LVL 5

Author Comment

by:raizon
ID: 6482621
After some reading I've learned that using Properties will make the app statefull which is something that I don't want to do since this is going to be a web based COM.

So this is what I have.  I believe this may do what I want.

I'm going to test it and will let you know if it works.  If you have any ideas please let me know.  And thanks for the help thus far.  I've learned from it.  

Private Const m_strDBSource As String = "glossary.mdb"
Private Const m_strDBCatalog As String = "Glossary"
Private Const m_strDBUser As String = "glossary"
Private Const m_strDBPassword As String = "user"
Private Const m_strDBProvider As String = "Microsoft.Jet.OLEDB.4.0"



Public Function GetDBConnection(Optional ByVal strSource As String, Optional ByVal strCatalog As String, _
    Optional ByVal strUserID As String, Optional ByVal strPassword As String, Optional ByVal strProvider As String)
 ' If any values were omitted, use the values from the corresponding properties.
 If IsMissing(strSource) Then
   strSource = m_strDBSource
 End If
 If IsMissing(strCatalog) Then
   strCatalog = m_strDBCatalog
 End If
 If IsMissing(strUserID) Then
   strUserID = m_strDBUser
 End If
 If IsMissing(strPassword) Then
   strPassword = m_strDBPassword
 End If
 If IsMissing(strProvider) Then
   strProvider = m_strDBProvider
 End If

 ' Now establish the connection and save it in an internal module-level variable
' Private oConn as Connection -- defined at top of class module
  With oConn
      .Provider = strProvider
      .ConnectionString = strSource & ";" & strCatalog & ";" & strUserID & ";" & strPassword & ";"
      .Open
  End With
End Function
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6483014
Actually, for a stateless item, I'd suggest receiving everything as you suggest, including what you need to process.  The concept is this:

1) Receive all connection information and request info
2) Establish the connection
3) Submit the request
4) Process the data
5) Close the recordset and connection

This would probably be implemented as below (with nothing optional since there will be no persistent values ... unless you simply want to supply default values)

' Step 1 - receive
Public Function GetDBConnection( _
  ByVal strSource As String, _
  ByVal strCatalog As String, _
  ByVal strUserID As String, _
  ByVal strPassword As String, _
  ByVal strProvider As String, _
  ByVal strRequest As String)

' Also add error trapping
  On Error Goto GetDBConn_Err

' Step 2 - connect
  Dim oConn as Connection
  set oConn = new Connection
  With oConn
     .Provider = strProvider
     .ConnectionString = strSource & ";" & strCatalog & ";" & strUserID & ";" & strPassword & ";"
     .Open
  End With

' Step 3 - request
  Dim rst as Recordset
  set rst = new Recordset
  rst.open strRequest, oConn.ConnectionString

' Step 4 - process
  while not rst.eof
    ' move data from rst.fields(###) to wherever
    rst.movenext
  wend

' Step 5 - close
  rst.close
  set rst = nothing
  oConn.close
  set oConn = nothing
  exit sub

' rest of error trapping
GetDBConn_Err:
  msgbox "An error occurred: " & err.description
end sub
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:raizon
ID: 6483045
I have 4 other Functions that are calling the GetDBConnection function opening/proccessing the Recordset and Closing/Destroying the RecordSet and Connection.


In my testing when trying to access the GetDBConnection method I'm getting this error.

Object variable or With block variable not set

Still testing and learning.

Here is the code I'm using.

<%
Set objGlossComp = Server.CreateObject("DRsControls.Glossary")
     
Set     objDBConn = objGlossComp.GetDBConnection
     With objDBConn
          .strSource = "PC124976"
          .strCatalog = "Glossary"
          .strUser = "sa"
          .strPassword = ""
          .strProvider = "SQLOLEDB.1"
     End With
     
     Response.Write objGlossComp.GetList("a")
     
Set objDBConn = Nothing
Set objGlossComp = Nothing
%>
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6483112
Well, I don't see anything specifically wrong with the code, and I've never really worked with ASP, but in VB I always dim my variables to ensure that they have the datatype I expect.  objGlossComp is not dimmed, and would probably only be type Object so that wouldn't necessarily help, but by default I think it becomes a Variant.

As for "DRsControls.Glossary", I don't know what that is...is it the control you created?  I think you need to fix your GetDBConnection function so that it returns a connection object, and the last thing it does is assigns the valid connection to your function:

Public Function GetDBConnection(...) As Connection
...
  Set GetDBConnection = oConn
Exit function

See if that works...can you also determine on what line the error occurs?
0
 
LVL 5

Author Comment

by:raizon
ID: 6483138
DRsControls.Glossary is my control I created.


I added that.  Thanks for reminding me.

in ASP the only variable type is Variant.  The error is on

Set objDBConn = objGlossComp.GetDBConnection

I believe its somewhere in my function but not sure.


Public Function GetDBConnection(Optional ByVal strSource As String, Optional ByVal strCatalog As String, _
   Optional ByVal strUserID As String, Optional ByVal strPassword As String, Optional ByVal strProvider
As String) As Connection

' If any values were omitted, use the values from the corresponding properties.
If IsMissing(strSource) Then
  strSource = m_strDBSource
End If
If IsMissing(strCatalog) Then
  strCatalog = m_strDBCatalog
End If
If IsMissing(strUserID) Then
  strUserID = m_strDBUser
End If
If IsMissing(strPassword) Then
  strPassword = m_strDBPassword
End If
If IsMissing(strProvider) Then
  strProvider = m_strDBProvider
End If

' Now establish the connection and save it in an internal module-level variable
' Private oConn as Connection -- defined at top of class module
 With oConn
     .Provider = strProvider
     .ConnectionString = strSource & ";" & strCatalog & ";" & strUserID & ";" & strPassword & ";"
     .Open
 End With
 
 GetDBConnection = oConn
End Function
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6483165
Well, it looks good, so if you can't figure out the error line, try adding some debugging lines such as:

msgbox "#1 - Got here!"
...
msgbox "#2 - Got here!"

or whatever ASP supports for sending messages (possible to the document.)
0
 
LVL 5

Author Comment

by:raizon
ID: 6488031
Almost there.

I'm getting the error on

GetDBConnection = oConn

I've debugged it all the way through the function.  The connection is being opened and the error occurs when trying to return the connection.

The error again is

"Object variable or With block variable not set"


Here is the function as it standa now.

Public Function GetDBConnection(Optional ByVal strSource As String, Optional ByVal strCatalog As String, _
    Optional ByVal strUserID As String, Optional ByVal strPassword As String, Optional ByVal strProvider As String) As Connection

'On Error GoTo Err_GetDB
   
    Set oConn = New Connection
   
    ' If any values were omitted, use the values from the corresponding properties.
    If IsMissing(strSource) Then
        strSource = m_strDBSource
    End If
    If IsMissing(strCatalog) Then
        strCatalog = m_strDBCatalog
    End If
    If IsMissing(strUserID) Then
        strUserID = m_strDBUser
    End If
    If IsMissing(strPassword) Then
        strPassword = m_strDBPassword
    End If
    If IsMissing(strProvider) Then
        strProvider = m_strDBProvider
    End If

    ' Now establish the connection and save it in an internal module-level variable
    ' Private oConn as Connection -- defined at top of class module
   
   
       
    With oConn
        .Provider = strProvider
        .connectionstring = "Data Source=" & strSource & ";Catalog=" & strCatalog & ";User ID=" & strUserID & ";Password=" & strPassword & ";"
        .Open
    End With
       
    GetDBConnection = oConn
   
'Err_GetDB:
    'GetDBConnection = Err.Description
   
End Function
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 100 total points
ID: 6488090
Since the connection variable holds an object, you need 'set':

Set GetDBConnection = oConn

0
 
LVL 5

Author Comment

by:raizon
ID: 6488382
doh.

Something so obvious.

Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now