Close and open a connection

[TheWizardofInt]

I have multiple tables in a database, related by an AccountNo. I want to open the first table, get the accountno, close it, go to the second table, search by that accountno, find the match (there is always a match) and then read it.

I am having trouble getting the connections to close. Here is the code:

            strSQLQuery = "Select * from contact1 WHERE RecID =" & Request("ID") & ""
            myConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\apps\gm57\common;Extended Properties=dBase IV")
            myCommand = New OleDb.OleDbCommand(strSQLQuery, myConnection)
            Try
                myConnection.Open()
            Catch ex1 As Exception
                strSQLQuery = "Select * from contact1 WHERE RecID =9IXWISO$L6>O@_:"
                myConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=C:\apps\gm57\common;Extended Properties=dBase IV")
                myConnection.Open()
            End Try

             Get a new datareader from our command
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If myDataReader.Read Then
                For i = 0 To 30
.... do some stuff

                Next i
            End if
                myCommand.Connection.Close()
                now get the contact2
                strSQLQuery = "Select * from contact2 WHERE AccountNO like " & sAccNo & "%"
                myConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=C:\apps\gm57\common;Extended Properties=dBase IV")
                myConnection.Open()
                 Get a new datareader from our command
                myDataReader.Close()
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
                If myDataReader.Read Then

Obviously, I am wrong here. I get the error:

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connections current state is Closed.

On the second myDataReader =

How am I messing up?

 

[Robby]

You are Opening a connection but Closing a Datareader and a Command.

And use a For loop inside If myDataReader.Read Then, you should just do While myDataReader.Read (no IF), it will exit the loop when it hits EOF.

Also if youre going to put another New Connection inside the Catch, you should nest another Try/Catch in there.

 

[TheWizardofInt]

The reader stuff is more involved than I posted, but it follows what you said.

I tried closing myConnection and reopenning it. It gives a valid myConnection.open() but then when I try to use the datareader, it gives me a connection closed error. If I close the datareader and reopen it, that is a similar error for the datareader.

Is there a reset that I should be using? I guess I am just used to ADO where you can close and reopen your connection within a procedure