Conncetion Object Scope

[shaunk]

I am running VBscript within a DTS Package.(ActiveX Script Task)
It railroads you into using a function called Main() as the backbone of the script.
I create and open connection and recordset objects etc to read an SQl Server d/b.(successfully)

I have another function called Search_Orders() which also executes SQL commands. I have created it for modularity's sake.
When I reference this function from the Main()function, it seems to loose the connection to the d/b that I had already established in the main function. It doesn't complain, it just exits the referenced function at the point of call to the d/b.
I have to create and open another connection object within the nested function, which seems unecessary.

Any thoughts then on the scope/persistence of open connections.
Thanks

 

[dollinger]

This is just a thougt, but did you dim your object?
There is no error message, did you use "on error resume next" ?

can you post some code?

Thanks

 

[shaunk]

Thanks for your time on this!

Code as follows with connection setup first, then the call to the function, then the function. I am operating under the assumption that once the connection has been made, that it stays open, and I just have to change recordset objects on each call.

Set myConn = CreateObject("ADODB.Connection")
Set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties, open connection and perform error checking 

myConn.Open = "Provider=SQLOLEDB.1;Data Source = MyServer ; Initial Catalog = IMRU_Test ;Trusted_Connection=yes"
If myConn.Errors.Count > 0 Then
	' Perform call to email error
End If

' Call to the function
Order_Search = Find_Order(txtDb_Key)

' The function
Function Find_Order(txtDb_Key)
sqlSearch = "Select count(*) as Order_Count From Results_ORC  Where ORC_Order_Number = " & "'"& txtDb_Key & "'" 
	
Set mySearchRecordset = CreateObject("ADODB.Recordset")
mySearchRecordset.Open sqlSearch, myConn
If myConn.Errors.Count > 0 Then
        ' Email error status code
	Exit Function
End If

intOrder_Count  = mySearchRecordset("Order_Count")
	
If intOrder_Count > 0 then 
    Find_Order = True
Else
    Find_Order = False
End If

Set mySearchRecordset = Nothing
myConn.Close

End Function

It doesn't make it past the mySearchRecordset.Open statement but continues as if the call was successful and proceeeds to the statement after the function call.

 

[dilettante]

Something like:

Option Explicit
Dim myConn

Function Find_Order(txtDb_Key)
  Dim sqlSearch, mySearchRecordset, intOrder_Count
     :
     :
End Function

Sub Main()
  Dim OrderSearch

  Set myConn = CreateObject("ADODB.Connection")
  myConn.Open _
    "Provider=SQLOLEDB.1;Data Source=MyServer;Initial Catalog=IMRU_Test; Trusted_Connection=yes"
     :
     :
  Order_Search = Find_Order(txtDb_Key)
     :
     :
  myConn.Close
  Set myConn = Nothing
End Sub

The colons ) above are meant to signify a "vertical ellipsis."

The general idea is to have [tt]myConn[/tt] be global to the script. Your Recordset doesn't need this, because for whatever reason you chose to create/dispose of it regularly... otherwise you might declare it globally as well and create/dispose it only once in [tt]Main()[/tt]. A Recordset can be used multiple times, you just need to close it before reopening it.

You're closing/disposing of the connection in your function too!

Always use [tt]Option Explicit[/tt]. It will uncover many bugs for you, and it will force you to do things properly - like make decisions about the scope of data.

Another very good rule in VBScript: declare things before using them! In this instance it means declaring that function before you declare [tt]Main()[/tt]. It isn't always important with procedures, but it can be critical with data - more so if you omit [tt]Option Explicit[/tt].

 

[shaunk]

OK...Have taken note of your suggestions and implemented them ..thankyou.

Just a few questions.
1. I dimensioned Myrecordset as a global var, and yet had to Set myRecordset = Nothing and Set myRecordset = CreateObject("ADODB.Recordset) in between each call ? Yet, I dimensioned myConn as a global var, opened it once and closed it once with no propblem ?
2. I am using Err.number to track errors. It's very useful as it picks up ADO errors as well. However, the script crashes when I try to set it as a global var. If I call a error sub to log and email the error, the Err.number is reset to zero on entering the sub.