Getting Values from Stored Procedure

[rrocket]

I currently have this that sends a value to the DB and should return the information I need:

Set RS = createobject("ADODB.Connection")
Set x1 = server.CreateObject("ADODB.Command")
    With x1
    .ActiveConnection = gObjConnect
    .CommandType = adCmdStoredProc
    .CommandText = "s_FRCWP_OpenSPInvoiceList"
    .Parameters.Append  .CreateParameter("@DirectoryId", adInteger,adParamInput,,94)    
    .Execute
    End With

But when I try to get the value with something like this:

formString = formString & "<td><input type=""checkbox"" value="""&RS("Transaction_ID")&"""></td>"

I get a "Wrong number of arguments or invalid property assignment" error. Now I know I am not doing something right, but I cannot figure out what it is. Any help would be appreciated.

[rrocket]

I actually got it to return one row when I changed the "Set RS = createobject("ADODB.Connection")" to "Set RS = createobject("ADODB.Recordset")", but it gives me an error when I check for EOF try to use RS.MoveNext... Any ideas on how to get it to work?

[DrBunchman]

Hi rrocket,

Are you trying to return a record set from the database or just a single return value from the stored procedure?

If you're using the record set then you have created a record set object but not assigned anything to it (which is why using RS("Transaction_ID") won't work).

If you are just returning a single value then this needs to be the first item in your parameter list and it needs to have a parameter type of adParamReturnValue.

Take a look at this. It's got some good examples and explanations of stored procedures which should give you enough info to fix your code.

If you need any more help then let me know.

Dr B

[rrocket]

Thanks for your help... I actually changed the RS value to "Set RS = server.createobject("ADODB.Recordset")", but keep getting an error when I try to use MoveNext, EOF, or BOF.... I am trying to return a list of things and am currently getting the first record, but that is it.

Here is the error:
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'MoveNext'

I have been searching for a solution and have not been able to find anything.

[rrocket]

I finally figured it out... I commented the changes below.

Here is the problem section:

Set x1 = server.CreateObject("ADODB.Command")
'Set RS = server.CreateObject("ADODB.Recordset") 'commented out and made change below
    With x1
    .ActiveConnection = gObjConnect
    .CommandType = adCmdStoredProc
    .CommandText = "StoredProcName"
    .Parameters.Append  .CreateParameter("@Customer_ID", adInteger,adParamInput,,session("UserLocationId"))    
    set RS = .Execute 'Was RS = .Execute
    End With