returning a recordset from a stored proc

[MarkGreen]

Hi,
I am having trouble returning an ADO recordset to access from a server stored proc.
I read somewhere earlier that when select statements are perform in a stored proc they can be "automatically" returned?

This is my called procedure in access:

Function get_recordset(sqlstring As String) As ADODB.Recordset

Dim cmd As ADODB.Command
Dim criteria As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = getADOConnectstring()
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dl_data_access_get_recordset"

Set criteria = cmd.CreateParameter("criteria", adVarChar, adParamInput, 150, sqlstring)

cmd.Parameters.Append criteria


Set get_recordset = cmd.Execute
get_recordset.MoveLast
Debug.Print get_recordset.recordCount
End Function


and my stored proc is simply:
CREATE PROCEDURE dl_data_access_get_recordset
@sqlstring varchar(150) = NULL
AS
--exec(@sqlstring)
GO

I seem to be getting a recordset back from server with a recordcount of -1!

Thanks for any help

 

[Mr3Putt]

I don't see what is IN your "sqlString" variable... is it valid?

Start at the source, check your sqlString value.

 

[vidru]

Also, by the looks of that procedure, you've got the 'meat' of the procedure commented out?

-dave

 

[MarkGreen]

I pass the @sqlstring a simple select staement so @sqlstring becomes "SELECT * FROM table"

Also, the part of the proc that is commented isn't normally commented, i did it for testing purposes and then forgot to change it back.

Any ideas?

 

[vidru]

Mark,

Your posted code looks fine. Check your

getADOConnectstring()

function, and make sure you're setting the CursorLocation of your connection object to adUseClient.

-dave