Re: Error when Reusing ADO Connection & Command

Thanks for the tune-up Bob. Much appreciated. =)
the prize is a free copy of a software product my company is producing for
market trading. send me your e-mail address to jtaylor@xxxxxxxxxxxxxxx and I
will send you a copy when it is ready for market. thanks again.

"Bob Barrows [MVP]" wrote:

MarketWizard wrote:

Remove the New keyword from these statements:

*********************************************
Dim cn As New ADODB.Connection
Dim cmd2 As New ADODB.Command
Dim Rs2 As New ADODB.Recordset
*******************************************

Change them to:
Dim cn As ADODB.Connection
Dim cmd2 As ADODB.Command
Dim Rs2 As ADODB.Recordset

Set cn = New ADODB.Connection
Set cmd2 = New ADODB.Command
Set Rs2 = New ADODB.Recordset

The reason for this is that you want to be in control of when these
objects are instantiated. Using Dim As New causes them to be
instantiated whenever they are mentioned in your code if they have yet
to be instantiated.


What is the purpose of these statements? 1. You don't seem to have
declared these variables, and 2. you don't seem to use them later on in
the code. I would eliminate them:
****************************************************

Set customer_param = New ADODB.Parameter
Set rundate_param = New ADODB.Parameter
Set groupcode_param = New ADODB.Parameter
Set orderby_param = New ADODB.Parameter
********************************************************

'***********************************
' Connect to the Datasource
'***********************************


Oh wait. With this line, you can get rid of the "Set cn = New
ADODB.Connection" I suggested above.
Set cn = GetNewConnection

cmd2.ActiveConnection = cn

Again, this should be:
Set cmd2.ActiveConnection = cn


' Get the input parameters from the excel work***
GroupCode = Worksheets("Main").Range("GroupCode")
RunDate = Worksheets("Main").Range("RunDate")
OrderBy = Worksheets("Main").Range("OrderBy")

'Set the target work***
Set Ws = Sheets("Main")

cmd2.CommandText = "iobu_issue_bet_matrix.get_data"
cmd2.CommandType = adCmdStoredProc

' Set the parameters for the stored procedure call
cmd2.Parameters.Append cmd2.CreateParameter("customer_param",
adInteger, adParamInput, , 1) ' just default parm val = 1, set
actual val in loop below cmd2.Parameters.Append
cmd2.CreateParameter("groupcode_param", adVarChar, adParamInput, 15,
GroupCode)
cmd2.Parameters.Append cmd2.CreateParameter("rundate_param",
adVarChar, adParamInput, 10, RunDate)
cmd2.Parameters.Append cmd2.CreateParameter("orderby_param",
adVarChar, adParamInput, 25, OrderBy)


' Loop thru the customerS range
' Calling a stored procedure that will return a grid (ref cursor
recordset) to the spread*** for each
' time the stored procedure is called. These grids are concatenated
together to form a large continuous grid.

Dim r As Range
Dim row As Range
Set r = Ws.Range("customerS")

' Clear the work*** where we will put our results
Worksheets("Results").Cells.ClearContents
Rs2.CursorLocation = adUseClient

rownum = 1
Col = 1
'Set Ws
Set Ws = Sheets("Results")

For Each row In r.Rows

customer = row.Cells(1)
strMsg = "customer=" & customer
response = MsgBox(strMsg, vbOKOnly, "title", "DEMO.HLP", 1000)

If customer = 0 Then
Exit For
End If

' Set the parameter values
cmd2("customer_param") = customer
cmd2("groupcode_param") = GroupCode
cmd2("rundate_param") = RunDate
cmd2("orderby_param") = OrderBy

Set Rs2 = cmd2.execute

By using this syntax, you are invalidating the line above where you set
the cursorlocation to adUseClient. The Execute method constructs a NEW
recordset which is then assigned to the variable on the left side of the
equals sign, replacing whatever that variable already contains. If you
want a client-side cursor (why?) then use the Open method:

Rs2.Open cmd2

But for this application, I see no need for an expensive client-side
cursor. You should get rid of the previous lines in which Rs2 was
instantiated (Set rs2=New ADODB.Recordset and Rs2.CursorLocation =
adUseClient) and use the Execute method as you originally had it to
retrieve the data.

Ws.Range("A2").CopyFromRecordset Rs2

Next row

' Clean-Up
Rs2.Close
cn.Close
Set Rs2 = Nothing
Set cmd = Nothing

Exit Sub

ErrHandler:
'Clean-Up
If Rs2.State = adStateOpen Then
Rs2.Close
End If

If cn.State = adStateOpen Then
cn.Close
End If

Set Rs2 = Nothing
Set cn = Nothing
Set cmd2 = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If

End Sub

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



.