Solved

ADO Command Object w/ Connection Object

Posted on 2000-05-01
23
571 Views
Last Modified: 2013-11-25
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….

0
Comment
Question by:jaypro
  • 9
  • 5
  • 4
  • +3
23 Comments
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
try

rst.close
set rst = nothing
0
 

Author Comment

by:jaypro
Comment Utility
Need to keep it open (Disconnected though), actually function call that assigns to Module level Rst for quick reference
0
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
try setting its locktype property to adLockBatchOptimistic after setting its activeconnection to nothing
0
 

Author Comment

by:jaypro
Comment Utility
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
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:jaypro
Comment Utility
I will try all comments ...thanks...I will get back to you ASAP...
0
 

Author Comment

by:jaypro
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:jaypro
Comment Utility
Thanks all for your help...I'm a little confused though...don't know who to give points to?...
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
«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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Expert Comment

by:calla
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
DSN need to be installed on client PC, is basically the same as registry entries...
Personally, i don't like DSN
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
Sorry - read UDT as UDL in above comments.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
On what server is your master DB????
(i run into similar options like this, don't mind :-)
0
 

Author Comment

by:jaypro
Comment Utility
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
 

Expert Comment

by:calla
Comment Utility
You'vw tried closing and setting cmd = nothing after setting rst = cmd right?

adam
0
 

Author Comment

by:jaypro
Comment Utility
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 200 total points
Comment Utility
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
 

Author Comment

by:jaypro
Comment Utility
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
 

Author Comment

by:jaypro
Comment Utility
See above
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now