VB.NET - Cannot open an ADODB connection

Set pbnut_cn = CreateObject("ADODB.Connection")
 pbnut_cn.Open strConn   // code gets stuck at this point

The above connection is established in a sub routine which is called from its parent sub routine. And there is a connection already opened in the parent sub.
By trying TO CHECK THE ERROR I could only find out that the connection object is closed and still I am trying to open it. But, am clueless on how the connection object can already remain open 'coz it has nowhere else been declared.

How u have opened the connection in parent sub routine? check the way. second thing close the connection instance once the process is done, now in the mention code snippet check the connection state before opening it .and if its state is closed then open it. also check the ConnectionTimeout property.

perform this check in parent sub routine and see if there is an instance of the connection is still active.like this

MsgBox "Cnxn1 state: " & GetState(Cnxn1.State)

Hope this helps
Regards
D

In any of the sub-routines, where you create the connection this is the pattern that you need to follow.

Function SomeFunction(parameter 1)
'Create a connection
Dim mConnection
Set pbnut_cn = CreateObject("ADODB.Connection")
'Open the connection
pbnut_cn.Open strConn   
'Execute the statements using the connection object.
pbnut_cn.Execute "statement"
'Dispose the connection object
Set pbnut_cn= Nothing
End Function

1) check Provider in your connection string
2) check connection state object State method, it will return connection state (1=open,0=closed)

check below example

Option Explicit
Dim strConnection, conn, rs, strSQL

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath(".\db\db.mdb") & ";User Id=admin;Password=;"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myTable"
rs.open strSQL, conn, 3,3

rs.MoveFirst
WHILE NOT rs.EOF
   Response.Write(rs("myField") & "<br/>")
rs.MoveNext
WEND

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing 

hi,

Try below example:

Hey D,

I had already tried putting a check for the connection state. And the point where the connection object is made, I had checked for the state and it was closed. I tried to open the connection after that but no luck.
below is the code flow :
SUB A

'declare a connection 
'open a connection

'record set operation to execute a SQL stored proc using the connection established above
'record set eof check operations and all

'Call to SUB B

'closing record set 
'closing connection and setting it to nothing

END SUB

SUB B

'declare a connection  // connection is closed here
'open a connection // trying to open it but is not working(this is working in SUB A). code freezes here

'record set operation to execute a SQL stored proc using the connection established above
'record set eof check operations and all

'closing record set 
'closing connection and setting it to nothing 

END SUB

I have no clue as to what is happening 'coz the same code is working fine when I just extract the above code snippet and run it as a separate script.

Need your help.. thanks

In the sub A section use dispose statement like this

Set pbnut_cn= Nothing

Regards
D

You didn't assign a connection object or connection string to your recordset rstrolley.

Code:

rsTrolley.ActiveConnection = cn

Or

Code:

rsTrolley.Open strSQL, cn

Here is some sample code:

Code:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset

Set cn = new ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=TestPass;Data Source=C:\Test.mdb;Persist Security Info=False;Exclusive=0"
cn.open

set rs = new ADODB.recordset
rs.ActiveConnection = cn
rs.Open "Select Distinct family From Products"

rs.Close
cn.Close
Set rs=Nothing
Set cn = Nothing

Hi,

In many earlier applications, the tendency was to open a connection when you start the application and not close the connection until the application terminates. It is an expensive and time-consuming operation to open and close a database connection. Most databases have a limit on the number of concurrent connections that they allow.

For example: each connection consumes a certain amount of resources on the database server and these resources are not infinite. Most modern OLE DB providers (including SQL Server provider) implement connection pooling. If you create database connections, they are held in a pool. When you want a connection for an application, the OLE DB provider extracts the next available connection from the pool. When your application closes the connection, it returns to the pool and makes itself available for the next application that wants a connection.

This means that opening and closing a database connection is no longer an expensive operation. If you close a connection, it does not mean you disconnect from the database. It just returns the connection to the pool. If you open a connection, it means it's simply a matter of obtaining an already open connection from the pool. It's recommended in many ADO.NET books not to keep the connections longer than you need to. Therefore, you should:

•     Open a connection when you need it, and
•     Close it as soon as you have finished with it.
  

Hope this will help you

Hey Somesh,

I have already assigned the connection and the SQL string while opening the recordset. It goes like as below:

*************************************************************
Set pbimport_cn = CreateObject("ADODB.Connection")

If pbimport_cn.State = 1 then
  WriteToLog("**** Conn is open ****")
Else If pbimport_cn.State = 0 then
  WriteToLog("**** Conn is closed ****")
  pbimport_cn.Open strConn   // opening the connection here but it fails to open, strConn = "Provider=SQLOLEDB;Data Source=" datasource here ";" & "Initial Catalog=" catalog here ";Integrated Security=SSPI;"   
    WriteToLog("**** Conn is open now ****")
   End If 
End If

Set pb_rs = CreateObject("ADODB.Recordset")

 strSQL = "Executing proc here" 
 pb_rs.open strSQL, pbimport_cn, 3, 3        // adOpenStatic = 3, adLockOptimistic = 3

' record set operations are performed here (calling SUB B from here in SUB A) -- this is the only difference

pb_rs.close   
Set pb_rs = nothing

pbimport_cn.close
Set pbimport_cn = nothing
*************************************************************

The above code is in SUB B.
Similar code is in SUB A as well but the difference is that the connection is still open there before calling SUB B from there.
Rest all(like closing the connection or recordset and disposing them) are same in both the sub routines.

Please advice. Thanks

Hey Everyone,

I have noticed a very peculier behavior while running the vb script in two different ways.
One way is through the command prompt and another way is by running the script as a scheduled task.

When it runs via command prompt, the connection gets opened perfectly.But when run as a scheduled task, the code gets stuck while opening the connection.


Could anyone please explain this behavior ?

Thanks