VBA calling SQL Stored Procedure problem

[KCcasey]

Hello all,

I'm a VB newbie and I've been trying to piece together snippets of code in order to call a SQL Stored Procedure and return the result-set to an excel sheet.

I'm (mis)using VBA and ADO, and I've been able to run a sinple select statement, but I've gotten no-where trying to run a SP.

With the code I have supplied, my trying to pass 2 parameters.

The error message being returned is:
Runtime Error '3265';
Item cannot be found in the collection corresponding to the requested name or ordinal



The code I have at the moment is:

Public Sub test()
'Create a connection object
Dim conPayG As ADODB.Connection
Set conPayG = New ADODB.Connection


'Provide a connection string
Dim strConPayG As String

'Use the SQL Server OLE DB Provider
strConPayG = "PROVIDER=SQLOLEDB;"

'Connect to the PayGlobal DB on the MOF-CH-SQL server
strConPayG = strConPayG & "DATA SOURCE=mof-ch-sql;INITIAL CATALOG=payglobal;"

'Use an integrated login
strConPayG = strConPayG & " INTEGRATED SECURITY=sspi;"

'Now open the connection
conPayG.Open strConPayG

'*******************************************

'Create a command object
Dim cmdSP As ADODB.Command
Set cmdSP = New ADODB.Command

'Set the text of the command sent to SQL Server
cmdSP.CommandText = "dbo.test"

'Identify the type of command
cmdSP.CommandType = adCmdStoredProc

'Automatically fill-in parameter info from the stored procedure
cmdSP.Parameters.Refresh

'Set parameter 1
cmdSP(1) = "31/10/2004"

'Set parameter 2
cmdSP(2) = "1/12/2004"

'*******************************************

'Create a recordset object
Dim rsSP As ADODB.Recordset
Set rsSP = New ADODB.Recordset

Set rsSP = cmdSP.Execute()

With rsSP
    'Resultset > Sheet 1 cell A1
    Sheet1.Range("A1").CopyFromRecordset rsSP
    'Tidy up
    .Close
End With

End Sub

Any tips/pointers/help will be gratefully recieved! Thankyou in advance folks!

Kind Regards,
Casey.

 

[Dashley]

Casey,

Looks like your setting rsSP twice here. I think this may be your problem.

Dan

'Create a recordset object
Dim rsSP As ADODB.Recordset
Set rsSP = New ADODB.Recordset ' *** HERE

Set rsSP = cmdSP.Execute() '** HERE

With rsSP
'Resultset > Sheet 1 cell A1
Sheet1.Range("A1").CopyFromRecordset rsSP
'Tidy up
.Close
End With

End Sub

 

[KCcasey]

Hi,

Thanks for suggestion Dan, I've tried commenting out one then the other statement -- to no avail.

Still stumped.

Casey

 

[Dashley]

Casey,

Have you put a breakpoint in your code and steped through it to see where its failing?

 

[KCcasey]

Not knowing exactly how the debugger works, the breakpoint highlighted the line below and quit to the error message on proceeding.

'Set parameter 1
cmdSP(1) = "31/10/2004"

Have I used the correct syntax for providing a parameter? The parameters in the SQL SP are @startdate and @enddate...

Casey.

 

[Dashley]

Casey,

They look a little different then what Im used to and I dont use param passing alot but here's what mine look like.

Dan


'Dim P1 As Parameter
'Dim P2 As Parameter
'Dim P3 As Parameter
'Dim P4 As Parameter
'Dim P5 As Parameter

'Dim CMD As Command
'Set CMD = New Command


'CMD.ActiveConnection = CONN

'SqlInsert = "INSERT INTO CORRESPOND_APPRAISALS (ORDER_NO, CUST_ID, ORDER_DATE, ENTERED,PRINTED) "
'SqlInsert = SqlInsert & " VALUES ( ?,?,?,sysdate,'N' ) "

'CMD.CommandType = adCmdText
'CMD.CommandText = SqlInsert


' Set P1 = CMD.CreateParameter(, adNumeric, adParamInput)
' CMD.Parameters.Append P1
'
' Set P2 = CMD.CreateParameter(, adBSTR, adParamInput)
' CMD.Parameters.Append P2
'
' Set P3 = CMD.CreateParameter(, adDate, adParamInput)
' CMD.Parameters.Append P3
'
' 'Set P4 = CMD.CreateParameter(, adBSTR, adParamInput)
'CMD.Parameters.Append P4

'Set P5 = CMD.CreateParameter(, adBSTR, adParamInput)
'CMD.Parameters.Append P5



While Not Rs1.EOF

' P1.Value = Rs1(0)
' P2.Value = Rs1(1)
' P3.Value = Rs1(2)
'P4.Value = Now
'P4.Value = Rs1(4)

'CMD.Execute

 

[KCcasey]

Much Appreciated Dan,

Your code got me looking at the .CreateParameter method. I've had a play with my code, which now looks like...

Public Sub test()

Dim conPayG As ADODB.Connection
Dim strConPayG As String
Dim cmdSP As ADODB.Command

'Create 2x parameter objects
Dim paramStartDate As ADODB.Parameter
Dim paramEndDate As ADODB.Parameter

'Create 2x record vars
Dim strStartDate As String
Dim strEndDate As String

Dim rsSP As ADODB.Recordset

'*******************************************

'Create a connection object
Set conPayG = New ADODB.Connection

'Use the SQL Server OLE DB Provider
strConPayG = "PROVIDER=SQLOLEDB;"

'Connect to the PayGlobal DB on the MOF-CH-SQL server
strConPayG = strConPayG & "DATA SOURCE=mof-ch-sql;INITIAL CATALOG=payglobal;"

'Use an integrated login
strConPayG = strConPayG & " INTEGRATED SECURITY=sspi;"

'Now open the connection
conPayG.Open strConPayG

'*******************************************

'Create a command object
Set cmdSP = New ADODB.Command

'Set the text of the command sent to SQL Server
cmdSP.CommandText = "[dbo].[test]"

'Identify the type of command
cmdSP.CommandType = adCmdStoredProc

strStartDate = Trim(InputBox("Enter startDate:"))
strEndDate = Trim(InputBox("Enter endDate:"))

Set paramStartDate = cmdSP.CreateParameter '("startDate", adVarChar, adParamInput)
cmdSP.Parameters.Append paramStartDate
paramStartDate.Value = strStartDate

Set paramEndDate = cmdSP.CreateParameter '("endDate", adVarChar, adParamInput)
cmdSP.Parameters.Append paramEndDate
paramEndDate.Value = strEndDate


'*******************************************

Set cmdSP.ActiveConnection = conPayG
Set rsSP = cmdSP.Execute

With rsSP
    'Resultset > Sheet 1 cell A1
    Sheet1.Range("A1").CopyFromRecordset rsSP
    'Tidy up
    .Close
End With

End Sub

... but now I'm getting an error message:
[red]3708
Parameter object is improperly defined. Inconsistent or incomplete information was provided[/red]

The debugger quits at this line...
[blue]cmdSP.Parameters.Append paramStartDate[/blue]

This is probably something obvious, but for the life of me I can't spot my error. Any help anyone can offer will be as before MUCH appreciated!!!

Kind Regards (and holiday greetings)

Casey.

 

[tsuji]

Hello KCcasey,

Just a quick note first. Your code is in vb which is different from vbscript, though readily understandable. Maybe you should follow through later in forum vb5&6:

http://tek-tips.com/threadminder.cfm?pid=222

Also we have a vb-dbase dedicated forum as well.

As to your code above:
>>[tt]Set paramStartDate = cmdSP.CreateParameter [COLOR=red yellow]'[/color]("startDate", adVarChar, adParamInput)[/tt]
>>[tt]Set paramEndDate = cmdSP.CreateParameter [COLOR=red yellow]'[/color]("endDate", adVarChar, adParamInput)[/tt]
Take the comment sign out.

regards - tsuji

 

[bonealwf]

VBA and VB use # for date/time datatypes - if you are passing a datetime parameter from a vb/vba app you need to put the #s around it and not the @s. I think it has to do with Jet TSQL being so different from TSQL. Hope this helps.

 

[PHV]

And something like this ?
strStartDate = Trim(InputBox("Enter startDate:"))
strEndDate = Trim(InputBox("Enter endDate:"))
Set cmdSP.ActiveConnection = conPayG
Set rsSP = cmdSP.Execute( , Array(Format(strStartDate, "yyyy-mm-dd"), Format(strEndDate, "yyyy-mm-dd")))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244