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
and then in an asp page I try to retieve the info
I do not get an error just the record count is alway -1 and the loop in never entered
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
Code:
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
Code:
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