Link to home
Start Free TrialLog in
Avatar of mainrotor
mainrotor

asked on

I need help with a connection error

Hi experts,
I have a VB6 application that connects to a SQL Server 2012 database.  The application appears to be working fine, but sometimes when our users start up the application they get a pop-up with the following error.

"The connection cannot be used to perform this operation.  It is either closed or invalid in the context. 3709"

The error doesn't stop them from working, but I need help with what may be causing this issue.  

Thanks in advance,
mrotor
Avatar of Ryan
Ryan
Flag of United States of America image

I suspect the connection being used for the query was closed prior to this query running.

Generally, best practice is to open a connection just prior to using it, then close is as soon as possible.  Certainly not let that connection remain open after the method that opened it has exited.
If its 1 particular line, you can amend this error by checking if its closed, and opening it.

if cnn.state=0 then  cnn.open
VB6 with SQL Server 2012 :-)

Can you show us some code?
Avatar of mainrotor
mainrotor

ASKER

Here's a code sample.  You will notice that I Dim to ADODB.Connection.  One in the public section of my class and another with the SQLSave section of my class.  Could this be causing an issue?

Option Explicit

Dim strClientCode As String
Public cn As New ADODB.Connection



Public Function SQLConnect(Optional strErr As String) As ADODB.Connection
	On Error GoTo SQLError

	Dim provName As String
	cn.Provider = "sqloledb"


	provName = "Server=SERVERNAME;Database=DBNAME;User Id=MyID;Password=MyPwd"

	cn.Open provName

	Set SQLConnect = cn

	SQLExit:
    		Exit Function
	SQLError:
    		strErr = Trim$(strErrText & " " & Err.Description)
    		Resume SQLExit
End Function


'SAVE FUNCTION CODE
Public Sub SQLSave(strRecordID As String)
                
	On Error GoTo Save_Err

	Dim strErr As String
	strErr = ""

	Dim rs As ADODB.Recordset
	Dim cmd As New ADODB.Command
	Set cmd = New ADODB.Command
	Dim cnConnect As ADODB.Connection
	Set cnConnect = SQLConnect(strErr)
	cmd.ActiveConnection = cnConnect 
	cmd.CommandType = adCmdStoredProc

	cmd.CommandText = "sp_SaveRec"
	cmd.Parameters.Append cmd.CreateParameter("RecID", adVarChar, adParamInput, 50, strRecordID)


	Set rs = cmd.Execute

	cmd.ActiveConnection.Close
	Set cmd.ActiveConnection = Nothing

	Exit Sub

Save_Err:
    PopBox Err.Description & " " & Err.Number, vbExclamation, Err.Source
    Exit Sub
End Sub

Open in new window

What happens if you move

Public cn As New ADODB.Connection

to inside SQLConnect?
I don't know.  I haven't tried it.  This issue is hard to debug because not every user experiences it, and when it does occur it does when the app first loads.

mrotor
In my SQLSave procedure, when I close the command's connection, am i doing that right?
SOLUTION
Avatar of Ryan
Ryan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I could be mistaken, but I think your parameter
Optional strErr As String
should be
Optional ByRef strErr As String = ""
if you're trying to return any errors. As you have it (default is ByVal), changes inside the function will not be seen by the caller.
I will try your suggestion mr. bullwinkle.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial