Is the connection closed??

[Guest]

Question??

How do I check to see if 1 or the other connection to a database are closed or not
Iam working in VB 2003

Dim cnnDVD As New ADODB.Connectio
Dim rstDVD As New ADODB.Recordse
Dim rstDVDCheck As New ADODB.Recordset Dim strSQLDVD As String Dim cnnVHS As New ADODB.Connectio
Dim rstVHS As New ADODB.Recordse
Dim rstVHSCheck As New ADODB.Recordse
Dim strSQLVHS As Strin

These are my connections and recordsets, recordset checks, and SQL strings, if that helps any

Thanks to anyone who answers

 

[Guest]

You can query the "State" property on the Connection object

 

[Cor]

Hi Doug,

Normaly if you do not close it yourself it stays open.

And if you have not opened it, it is closed.

Cor

 

[Guest]

That's what I thought. But, for some reason my connection command is not happening. Here is a piece of my puzzle

Private Sub btnView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnView.Clic

If Me.chkDVD.Checked = True Then
cnnDVD.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & "C:\DataBase\Media.mdb
cnnDVD.Open(
strSQLDVD = "select * from DVD
rstDVD.Open(strSQLDVD, cnnDVD, ADODB.CursorTypeEnum.adOpenStatic
If cnnDVD.State = ConnectionState.Closed The
Exit Su
Els
rstDVD.Close(
End I
End I

If Me.chkVHS.Checked = True The
strSQLVHS = "select * from VHS
rstVHS.Open(strSQLVHS, cnnVHS, ADODB.CursorTypeEnum.adOpenStatic
If cnnVHS.State = ConnectionState.Closed The
Exit Su
Els
rstVHS.Close(
End I
End I

end su

Thanks again Co

 

[Cor]

Hi Doug,

If this is your code and it is constistent, than the cnnVHS does not exist
while you refer to it?

(I do not see it).

Cor

Private Sub btnView_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles btnView.Click

If Me.chkDVD.Checked = True Then
cnnDVD.ConnectionString =

"Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & "C:\DataBase\Media.mdb"

 

[Guest]

Sorry, how about this. It just doesn't want to open either of these connections. So, something else is wrong.

Private Sub btnView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnView.Click

If Me.chkDVD.Checked = True Then
cnnDVD.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & "C:\DataBase\Media.mdb"
cnnDVD.Open()
'End If

'If Me.chkDVD.Checked = True Then
strSQLDVD = "select * from DVD"
rstDVD.Open(strSQLDVD, cnnDVD, ADODB.CursorTypeEnum.adOpenStatic)
If cnnDVD.State = ConnectionState.Closed Then
Exit Sub
Else
rstDVD.Close()
End If
End If

If Me.chkVHS.Checked = True Then
cnnVHS.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & "C:\DataBase\Media.mdb"
cnnVHS.Open()
strSQLVHS = "select * from VHS"
rstVHS.Open(strSQLVHS, cnnVHS, ADODB.CursorTypeEnum.adOpenStatic)
If cnnVHS.State = ConnectionState.Closed Then
Exit Sub
Else
rstVHS.Close()
End If
End If

End Sub


Thanks again.

 

[Cor]

Hi Doug,

Is it maybe the second open that gives an error. Try to add those 2
additions that I did.

I hope this helps?

Cor

Private Sub btnView_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles btnView.Click

If Me.chkDVD.Checked = True Then
cnnDVD.ConnectionString =

"Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & "C:\DataBase\Media.mdb"

cnnDVD.Open()
'End If

'If Me.chkDVD.Checked = True Then
strSQLDVD = "select * from DVD"
rstDVD.Open(strSQLDVD, cnnDVD, ADODB.CursorTypeEnum.adOpenStatic)
If cnnDVD.State = ConnectionState.Closed Then
Exit Sub
Else
rstDVD.Close()
End If
cnnDVD.Close()

End If

If Me.chkVHS.Checked = True Then
cnnVHS.ConnectionString =

"Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & "C:\DataBase\Media.mdb"

 

[AnonymousHoward]

I ran the following code below and all went fine. Does your file exist and
do you have rights? I suggest throwing in some debug asserts and wrap the
connection logic in try/catch.


Sub Main()
Dim cnn1 As New ADODB.Connection
Dim cnn2 As New ADODB.ConnectionClass
Try
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
source=" & "C:\My Projects\AddressBook\Address.mdb"
Debug.Assert(cnn1.State = 0, "Not closed???")
cnn1.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Debug.Assert(cnn1.State = 1, "Not open???")

Try
cnn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
source=" & "C:\My Projects\AddressBook\Address.mdb"
Debug.Assert(cnn2.State = 0, "Not closed???")
cnn2.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Debug.Assert(cnn2.State = 1, "Not open???")
End Sub

 

[Cor]

Hi Anonymous Howard,

It goes wrong if it goes the second time.

Cor

 

[AnonymousHoward]

Hey Cor,

He said in his last message "It just doesn't want to open either one of the
connections. So, something else is wrong.". I take this to mean that both of
the connections are not opening.

My example has two open connections to the access DB and that works. I just
ran code below and closing and reopening doesn't show any problem either.
Maybe the connections are not his problem. Could be the database is not
there? Could be he doesn't have rights? Maybe he is running under ASP.NET
and the default ASP.NET account doesn't have rights to the file/directory?

Sub Main()
Dim cnn1 As New ADODB.Connection
Try
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
source=" & "C:\My Projects\AddressBook\OIS Migration Tools.mdb"
Debug.Assert(cnn1.State = 0, "Not closed???")
cnn1.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Debug.Assert(cnn1.State = 1, "Not open???")
cnn1.Close()
Try
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
source=" & "C:\My Projects\AddressBook\OIS Migration Tools.mdb"
Debug.Assert(cnn1.State = 0, "Not closed???")
cnn1.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Debug.Assert(cnn1.State = 1, "Not open???")
End Sub

 

[Cor]

Hi Howard,

He uses this in a view click and therefore it will give an already open
connection when he clicks the second time. I also did not see that direct
and tested it also and with that click what I normaly do not. (I never use
ado so it was going a little bit back in history)

)

Cor

 

[AnonymousHoward]

Aaah..I see.

Well then the logic he is showing may not be the problem code, it may only
be indicating that a connection is still left open from before.

If the code he showed is the only connection open logic in his application
then its possible that his code is throwing an exception before the closing
connection logic, thus never properly closing the connection. His (or
anybody elses for that matter) connection code should be wrapped in a
try/finally block with the closing code logic in the finally block, thus
assuring its always closed.

 

[Guest]

Cor and AnonymousHoward

Thanks for the info on my problem. I'll try your ideas and let you gents know what I come up with

Dou