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 connection's 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 you're 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