question

Fizz avatar image
Fizz asked

vbs difficulty in obtaining a recordset from a stored proc

Hi I am trying to obtain a single record (multi field) recordset via vbscripting from SQL server database. My scripting is rusty ! The SP receives a single parameter and it runs fine. however I cannot get past a parameter error in the execution of the script. Code as below Set sqlconnection = CreateObject("ADODB.Connection") Set sqlcommand = CreateObject("ADODB.Command") Set oRS = CreateObject("ADODB.Recordset") sconnect= "driver={sql server};server=?????;Database=S??;Trusted_Connection=yes" sqlconnection.ConnectionString = sconnect sqlconnection.Open sqlcommand.ActiveConnection = sqlconnection sqlcommand.CommandType = adCmdStoredProc sqlcommand.CommandText = "spSignature" sqlcommand.Parameters.Append sqlcommand.CreateParameter("@strUser", adVarChar, adParamInput, 50,szEnvUserName) set oRS = sqlcommand.execute wscript.Echo Err.number wscript.Echo Err.description The error number = negative and very long -2147217900 error source = MS OLE DB Provider for ODBC driver error description = Function or procedure "spSignature" expects parameter "@strUser" which was not supplied. The variable being passed as the param value is populated with a valid user name. What have I got wrong? Thanks
stored-proceduresvbscript
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
Is it the case that spSignature actually has more than one parameter, and that `@strUser` is not the first parameter? The reason I ask is because ADO passes parameters in order by default, rather than by name. Check out [the NamedParameters property][1]. [1]: http://msdn.microsoft.com/en-us/library/ms675840(v=VS.85).aspx
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Fizz There is one trick related to moving between the records in the recordset I remember from the good ol' days of classic ADODB. Suppose you want to move to the record # 127 quckly (without .MoveNext 126 times). You can
oRS.PageSize = 126
oRS.AbsolutePage = 2
and there you have the cursor pointing to the record # 127 :)
1 Like 1 ·
Fizz avatar image Fizz commented ·
No. spSignature only has the one parameter and it is being populated with a value. I have checked the variable holding it and it has what I want in it. I am guessing I have something wrong that while not being a syntax error, just manages to throw it enough to fail. It may be that the parameter error is somewhat dubious. I shall continue fiddling with it. Thanks for the pointer to named paameters though :-)
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Ok, have you tried with and without the '@' at the start of the parameter name in your code? Could you post the top of the stored proc code? (just up to the 'AS')
0 Likes 0 ·
Fizz avatar image Fizz commented ·
I have tried it without the @ - same issue. However, I have just got it returning data, by dropping the Set oRS = CreateObject("ADODB.Recordset") line and relying on the set oRS = sqlcommand.execute line only. At the same time it has expanded from a single record data set to a multi record set so I'll see how it handles that. Thanks to all for your assistance.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Fizz This should be pretty straightforward. Once you are done with looping through first set of records, you can
Set oRS = oRS.NextRecordset

' Loop again
Do While Not oRS.EOF
    ' 
    oRS.MoveNext
Loop
In a mean time, in your original code, should not it be something like
oRS.ActiveCommand = sqlcommand
oRS.Open sqlcommand.Execute
I cannot remember for sure though, it has been a while since I had to work with classic ADODB
0 Likes 0 ·
Show more comments
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Long time since I used ADO and even longer since I programmed PROPER ADO (using parameters rather than executing dynamic SQL that is). But I think I recall that you need to explicitly set the NamedParameters property that Matt mentioned even if you only have one parameter. Otherwise ADO won't be able to match **sqlcommand.CreateParameter("@strUser", adVarChar, adParamInput, 50,szEnvUserName)** with the parameter in the procedure. Did you try setting NamedCommand property?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fizz avatar image Fizz commented ·
Thanks to all. It is running through multi record sets without drama, doing all I need it to do and so far no issues. Appreciate all assistance provided. Fizz
0 Likes 0 ·
Fizz avatar image
Fizz answered
Drop the Set oRS = CreateObject("ADODB.Recordset") line and relying on the set oRS = sqlcommand.execute line only
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.