SQL-Server Recordset 1

[malonep]

Hi,

I am trying to connect to a SQL-Server stored procedure and return a recordset. I can access a stored procedure which returns a number, successfully, but I am unable to return a record set

my stored procedure is very simple

CREATE PROCEDURE dbo.sprGetProjects
(
	@ID INT=0
)
AS

BEGIN	
Select *
	FROM Projects
	WHERE iID = @ID
END
GO

and then in an asp page I try to retieve the info

set conn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3

conn.open "Provider=SQLOLEDB;Data Source=Earth\SLDev;Database=SL;Integrated Security=SSPI"

If conn.errors.count = 0 Then 

	Response.write ("<br><br>Connected OK." )
else
	Response.Write("error")

End If

set cmd.ActiveConnection = conn

cmd.CommandText = "sprGetProjects"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("@ID",adInteger,adParamInput,10,1)
set rs = cmd.Execute

Response.Write("<br> " & rs.recordCount)

Do While Not rs.EOF
		Response.Write("Have something<br>")
        rs.MoveNext
    Loop

I do not get an error just the record count is alway -1 and the loop in never entered

 

[malonep]

I found and read the thread 333-987548 Record count.

I just wanted to clarify.

I don't really care about the record count. I just want to be able to open a record set and scan through the entries to build a List.

I ran the test

 rs.Supports(adBookmark) Or rs.Supports(adApproxPosition)

and it failed.


I don't know what the different type of cursors mean, I just want to select specific items from a table and return a recordset with the results that I can scan through.

Any help on how to correctly set up a recordset that would do this would be appreciated.

Thanks,

 

[Tarwn]

First of all, you don't need the @ symbol, that is not actually part of the attribute name but is a way to tell the db that it is an attribute/variable. The actual name of the parameter is just the rest of that string. So your call should look something like:

cmd.Parameters.Append cmd.CreateParameter("ID",adInteger,adParamInput,10,1)

Second, you do not need the following lines:

set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3

Basically all that does is create a recordset object, set rs as a reference to that object, then change the cursortype. No problem. Unfortunatly later on you Set rs to another Recordset object and the previous Recordset then disappears, never to be seen again. In other words, your creating a Recordset object that you will never use because your Execute command returns a refernce to another _new_ recordset object.

-T