Problem understanding ADO connection debugging

[AndyLD]

Hello!

I am new to VisualBasic, but I need to do some work on a VB project. Currently I am struggling with this problem:
A Module connects to a local Access database. The connection is a ADODB.Connection(). The connection seems to work

Public conn As New ADODB.Connection()

Function Connect() As Boolean
		On Error GoTo Connect_Err
		conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\database.mdb;Jet OLEDB:Database Password=myOtherPassword"
		conn.Open()
        Connect = True
        conn.Close()
		Exit Function
Connect_Err: 
		Connect = False
	End Function

At least Connect is set to "True";

My Problems:
1. I would like to add the following check to some parts of the code:

If (conn.State = adStateOpen) Then

... Visual Basic 2008 Express Edition tells me, "adStateOpen" is not declared. I tried to find a reference for the Class ADODB, to get the correct properties and methods for the object the Connection() method return, but I could not find such a reference for this class.

2. I am not sure about the usage of the conn.Open and conn.Close methods. I have a function, that is called many times as it reads out many single parameters from the database. Should I open and close the connection within this method, or should I leave the connection open while doing multiple calls to the database.

	
Function GetDbParam(ByRef variable As Object) As String
		Dim r As Object

        conn.Open()
        r = conn.Execute("select * from params where variable = '" & CStr(variable) & "'")
        If Not r.EOF Then
            If IsDBNull(r("value")) Then

                conn.Close()
                Exit Function
            End If
           
            GetDbParam = r("value").ToString()

        End If
        conn.Close()
 End Function

I hope to get some help on this.

Kind regards,
Andy

[jmcilhinney]

Before anything else, why are you using ADO? That's a very bad way to start out learning VB.NET. ADO is old hat and should be left to the VB6 developers. If you're using .NET then use .NET, which means ADO.NET for data access.

[AndyLD]

Thanks, jmcilhinney.
I use it, because the application I am working on now is coded in VB6(at least 10 years old application) and I am trying to get it running under Visual Basic 2008 Express.
OK, then I will try to switch the connection from ADO to ADO.NET.

[jmcilhinney]

Thanks, jmcilhinney.
I use it, because the application I am working on now is coded in VB6(at least 10 years old application) and I am trying to get it running under Visual Basic 2008 Express.
OK, then I will try to switch the connection from ADO to ADO.NET.

If you already have a VB6 app that uses ADO then you probably shouldn't start switching to ADO.NET unless you intend to convert all your data access code. It would be nice to use ADO.NET for new data access code but having some of each might be undesirable. This is an example of why having a separate data access layer is a good thing. You could change the underlying implementation from ADO to ADO.NET and the app would be none the wiser. Probably not a great many separate DALs created in VB6 proportionally though. So, while I would like to see you using ADO.NET, think well before switching for this particular app.

Now that we have addressed why you're using ADO, let me address your questions.

1. ADO is COM-based, so you have to add a reference to a COM library. When adding a reference, select the COM tab and then reference Microsoft ActiveX Data Objects (ADO stands for ActiveX Data Objects). You'll presumably want to select the most recent version. 'adStateOpen' is then a member of the ObjectStateEnum enumeration.

2. If you want to perform multiple data access operations then you should absolutely open the connection only once and close it at the end. A good model to follow here is the data adapter classes used in ADO.NET. When you call Fill or Update, they will open the connection automatically if it is closed and then close it again if they opened it. Basically, the connection is left in the same state that it was found. Your methods would then look something like this:

Function GetDbParam(ByRef variable As Object) As String
		Dim r As Object
        Dim connectionOpened As Boolean = False

        If conn.State = ObjectStateEnum.adStateClosed Then
            conn.Open()
            connectionOpened = True
        End If

        r = conn.Execute("select * from params where variable = '" & CStr(variable) & "'")
        If Not r.EOF Then
            If IsDBNull(r("value")) Then

                conn.Close()
                Exit Function
            End If
           
            GetDbParam = r("value").ToString()

        End If

        If connectionOpened Then
            conn.Close()
        End If
 End Function

You can now simply call the method if that's all the data access you have to do and it will open and close the connection. If you have multiple operations to perform, open the connection explicitly and then call each method, which will simply leave the connection open, and then close it explicitly when you're done.

[AndyLD]

Dear jmcilhinney,

thank you very much for your very detailed explanation.
While I was looking for a way to transform the database connection from ADO to ADO.Net, I realised (as you imagined) that this would mess up all the code. The database connection is used all over the code, there is no datalayer abstraction in this application. So this means I would need to change the whole code. Probably this is what I will need to do in the long run anyway, but currently I need to make urgent changes so the application continues to work.

Thank you I found the tab in Project properties with the Reference list, I will try to add a newer version of the library, so I can use the property to check if the connection is already open.

Thank you also for the solution to check if the connection is open with the old library, I will check it out asap.

[AndyLD]

One more question. Currently the project uses Microsoft ADO 2.7 Library and Microsoft ADO Data Control 6.0. When I select to add a new COM Library, then there is only one newer MS ADO Library 2.8. I searched the internet, but I cannot figure out which is the latest library version. With those libraries in my project, I don't have the 'adStateOpen' property for the connection returned from ADODB.Connection(). BTW the machine I am developing on is a Win XP Pro SP3.