>Hi,
>
>I'm using ADO 2.6 from within VFP6 SP5 and am having a problem getting a record set to be returned from an ADO command object when executing a stored procedure on SQL Server 7. Here is my code:
>
>
loConn = createobject("ADODB.Connection")
>loConn.Open(lcConnStr)
>
>loCmd = CreateObject("ADODB.Command")
>
>with loCmd
> .ActiveConnection = loConn
> .CommandType = 4 && Stored Procedure
> .CommandText = 'sp_getkey'
> .Parameters('@tablename').Value = 'CUSTOMERS'
>
> loRS = .Execute
>endwith
>
>? loRS.State && This displays 0, closed.
>
>This stored procedure is used to generate primary keys. It increments the counter in the keymaster table for a specific table and returns a single row and column with the next available key. When I run the above code, the keymaster table get incremented fine, so I know the stored procedure is running. However, the record set (loRS) does not contain my selected row from the stored procedure. As a matter of fact, it is closed. I can call this same stored procedure using SQL Pass Thru and it returns a cursor fine. I would really like to get this to work using ADO. If someone can let me know what I might be missing, I would greatly appreciate it.
>
>Thanks,
>
>James
It looks good at first glance, but if I can recommend a download of mine here in the UT :
http://www.levelextreme.com/wconnect/wc.dll?FournierTransformation~?2,54,Forum=5,28,1196 , it will give you a list of stored procedures in your target server and database and return the parameter structure for the selected sp. This is a
design-time tool only.
IMO, I think you will be better served by an output parameter so you don't incur the overhead of returning a cursor that requires a recordset object. Just add an output parameter to your stored procedure and assign its value by using a "Select @myOutParam=myField From myTable" kind of a query.
HTH