ADO Command Object w/ Connection Object

Hey there...

I'm having a bit of difficulty with the Command Object in ADO. (MDAC 2.5)
The problem, which I just noticed is that fact that some of my disconnected recordsets are NOT disconnecting from the Data Provider when I think they are.

Basically, I am opening a command object, recordset then disassociating the recordset from the source and closing the source and activeconnection of the command object, however my SQL Process Information viewer shows that the connection is still active until I physically CLOSE the recordset. (?)  I understand this for recordsets that are bound to a source, but disconnected?  

I seem to have narrowed down the problem to only occur when parameters to my stored procedures are present.

Code Includes:

Dim i As Integer

    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command
   
    On Error GoTo ErrorHandler
       
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP & ""
    cmd.CommandType = adCmdStoredProc
   
    cmd.Parameters.Refresh
   
    CollectParams cmd, Params
   
    rst.CursorLocation = adUseClient
    rst.Open cmd, , adOpenKeyset, adLockBatchOptimistic
   
    Set rst.ActiveConnection = Nothing
    Set rst = Nothing
   
    Set cmd.ActiveConnection = Nothing
   
    Set RunSPReturnRS_RWDis = rst
   
    Exit Function
   
ErrorHandler:

    'RaiseError mstrMODNAME, "RunSPReturnRS_RW(" & strSP & ", ...)"
    Err.Raise Err.Number, , Err.Description
    Exit Function

    Set rst = Nothing
    Set cmd = Nothing


Any suggestions….

jayproAsked:
Who is Participating?
 
Brendt HessConnect With a Mentor Senior DBACommented:
You might use a fully disconnected recordset.  This saves the data to a local disk file, closes the rs, sets it to nothing, and then re-opens the local rs.  You can reconnect to the server with updates as needed.

Use code similar to this:

  ars.Save "C:\DATA.DAT"  ' Save data to a file
  ars.ActiveConnection = Nothing
  ars.Close
  ars.Open "C:\DATA.dat"

Note that this uses a recordset, rather than a command object, but the theory should be the same.
0
 
AzraSoundCommented:
try

rst.close
set rst = nothing
0
 
jayproAuthor Commented:
Need to keep it open (Disconnected though), actually function call that assigns to Module level Rst for quick reference
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
AzraSoundCommented:
try setting its locktype property to adLockBatchOptimistic after setting its activeconnection to nothing
0
 
jayproAuthor Commented:
cannot set locktype while rst is open, by the way I noticed that some of the sample code I provided was some of my test code (oops), below is that real code I have been using (close but a little different)

    Dim i As Integer

    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command
   
    On Error GoTo ErrorHandler
       
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP & ""
    cmd.CommandType = adCmdStoredProc
   
    cmd.Parameters.Refresh
   
    'CollectParams cmd, Params
   
    rst.CursorLocation = adUseClient
    rst.Open cmd, , adOpenKeyset, adLockBatchOptimistic
   
    Set rst.ActiveConnection = Nothing
    rst.LockType = adLockBatchOptimistic
   
    Set cmd.ActiveConnection = Nothing
   
    Set RunSPReturnRS_RWDis = rst
   
    Exit Function
   
ErrorHandler:

    'RaiseError mstrMODNAME, "RunSPReturnRS_RW(" & strSP & ", ...)"
    Err.Raise Err.Number, , Err.Description
    Exit Function

    Set rst = Nothing
    Set cmd = Nothing
0
 
Brendt HessSenior DBACommented:
Three possibilities:

(1) Clear the Parameters collection of the recordset.

(2) Explicitly define the Parameters (instead of using Refresh).  This is not only faster, but saves numerous calls to the server.

(3) Set the command object to Nothing (not just the Connection of the Command object).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Actually, i always used a concrete connection object being affected to the command instead of the connect string.

Additionally, the comments of bhess1 are true.
0
 
jayproAuthor Commented:
I will try all comments ...thanks...I will get back to you ASAP...
0
 
jayproAuthor Commented:
Damn ConnectionString...Anyone have a good method for making a dynamic connectionstring

So I don't end up with something like this

"Driver={SQL Server};Server={Server Here};Uid=sa;Pwd=;Database={Database Here}"

Registry Entries/Database/?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Registry(on Client PC): NO
Database: how to read database if you don't have connection (-:
Ini File: Best for simple application, even on local network
Component+Ini File/Registry: Best for distributed applications, optimal with small form to configure AND test values...
0
 
jayproAuthor Commented:
Thanks all for your help...I'm a little confused though...don't know who to give points to?...
0
 
Éric MoreauSenior .Net ConsultantCommented:
«however my SQL Process Information viewer shows that the connection is still active»

Isn't it your connection that keeps your counter to 1?

I suggest that you open a connection, use it in your command and close it.

    Dim i As Integer

    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    dim cn as new adodb.connection
     
    On Error GoTo ErrorHandler

    cn.connectionstring = getconnectionstring()
    cn.CursorLocation = adUseClient
    cn.open
         
    cmd.ActiveConnection = cn
    cmd.CommandText = strSP & ""
    cmd.CommandType = adCmdStoredProc
     
    cmd.Parameters.Refresh
     
    'CollectParams cmd, Params
     
    rst.CursorLocation = adUseClient
    rst.Open cmd, , adOpenKeyset, adLockBatchOptimistic
     
    Set rst.ActiveConnection = Nothing
    Set cmd.ActiveConnection = Nothing
    cn.close
    set cn = nothing
     
    Set RunSPReturnRS_RWDis = rst
     
    Exit Function
     
ErrorHandler:

    'RaiseError mstrMODNAME, "RunSPReturnRS_RW(" & strSP & ", ...)"
    Err.Raise Err.Number, , Err.Description
    Exit Function

    Set rst = Nothing
    Set cmd = Nothing
    set cn = nothing
0
 
callaCommented:
To avoid long connection strings use DSN's. That way you can change server and database without modifying code or writing registry setting code.

On your disconnected recordset...
Weather SQL Server shows the connection or not if you use adLockBatchOptimistic can you unplug your network cable and still access the recordset?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DSN need to be installed on client PC, is basically the same as registry entries...
Personally, i don't like DSN
0
 
Brendt HessSenior DBACommented:
How many server/DB connections are you talking about here?  Personally, I like the UDT files for my connection information... I only need to change the info in one location, and every program uses the new location.

However, I might consider using a DB for one.  I'd put in fields for Server, DB Name, and an ID field.  I could make my query return the appropriate info from some master DB with a query like (SQL Server example):

"SELECT 'Driver={SQL Server};Server={' + Server + '};Uid=sa;Pwd=;Database={' DB_Name + '}' as ConnStr from ConnectionDB Where ID = " & ID
0
 
Brendt HessSenior DBACommented:
Sorry - read UDT as UDL in above comments.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
On what server is your master DB????
(i run into similar options like this, don't mind :-)
0
 
jayproAuthor Commented:
Running on SQL Server 7.0,

Basically as calla stated, it is correct, I could unplug my network connection and the app would continue to work fine, however, the app chews up 20+ user access licenses on SQL Server at one time...:(

What the F...

What am I missing...

Thought I had the solution, I was wrong?

Really narrowed it down to parameterized command object queries based on stored procedures (disconnected) I cannot get the connection to disappear until rst.close...........ahahah..

:)

Really would hate to have to redesign all of my class modules (although a redesign usually does end up in tighter code...)

Thanks for any help...
0
 
callaCommented:
You'vw tried closing and setting cmd = nothing after setting rst = cmd right?

adam
0
 
jayproAuthor Commented:
yep..:)

gone to this..

For Each cmdPara In cmd.Parameters
  cmd.Parameters.Delete cmdPara.Name
Next
   
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
   
Set rst.ActiveConnection = Nothing

Set RunSPReturnRS_RWDis = rst 'Passes back
   
cnn.Close
Set cnn = Nothing
0
 
jayproAuthor Commented:
think I will go with your suggestion bhess1...do you know of any problems/speed or functional that I should be aware of?

By the way, it is the Only solution that finally released the connections on the server....:)

Thanks again...
0
 
jayproAuthor Commented:
See above
0
 
Brendt HessSenior DBACommented:
Speed - Actually, it can be faster than a non-fully disconnected rs.  I use it when doing a batch update set to a static DB, and update speed overall increased about 30-50%.  HOWEVER, since it does transfer all of the requested data, it can be slow starting if your rs has numerous records.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.