Displaying a recordset from a stored procedure

Question

Post reply

Displaying a recordset from a stored procedure

Anthony Phillips-301303

Mr or Mrs. 500

Points: 574
More actions
February 15, 2006 at 10:20 am

#189241

With the help I've gotten from this site I'm finally beginning to understand something about stored procedures. Using script below I've connected to the database and successfully called the stored procedure "sp_IsValidLogon". But I'm stuck as to how I could display a recordset. How can I accomplish this?

<%
' Selected constants from adovbs.inc
Const adCmdStoredProc = &H0004
Const adInteger = 3
Const adCurrency = 6
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adExecuteNoRecords = &H00000080
Const adParamReturnValue = &H0004
Const adVarChar = 200
dim dataConn, adocmd, IsValid
set dataConn = Server.CreateObject("ADODB.Connection")
dataConn.Open "PROVIDER=SQLOLEDB; DATA SOURCE=REMITCOCLTVOL;" _
& "database=REMITCOSQLServer;Trusted_Connection=yes;"
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "sp_IsValidLogon"
adocmd.ActiveConnection = dataConn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter("return", _
adInteger, adParamReturnValue, 3)
adocmd.Parameters.Append adocmd.CreateParameter("username", _
adVarChar, adParamInput, 16, _
Request.Form("UserName"))
adocmd.Parameters.Append adocmd.CreateParameter("password", _
adVarChar, adParamInput, 16, _
Request.Form("Password"))
adocmd.Execute
IsValid = adocmd.Parameters("return").Value
If IsValid > 0 Then
'Redirect user, incorrect login
Response.Redirect "GetDailyStatus.asp"
End If
'process logon code
'.............
%>
Connect via an IP address:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;Integrated Security=SSPI;"

*To check at what state the connection is

If cnn.State = adStateOpen Then

Response.Write " Connection succesfull"

Else

Response.Write " Connection failed"

End If

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 1

Bind the recordset to a Dataset...bind the Dataset to any of these...DataList, Datagrid, DataView etc. Check out http://www.4guysfromRolla.com they have some good examples.

HTH

Mathew J Kulangara
sqladventures.blogspot.com

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 2

have a look at

http://www.davidpenton.com/testsite/scratch/disconnected.command.asp

basically

rs=adocmd.Execute

while not rs.eof '*retrieving all records*

rs.movenext

wend

Anthony Phillips-301303 Mr or Mrs. 500 Points: 574 More actions · Answer 3

Thanks for the response. I've run into a problem though. I'm getting this error and I don't know why. What's wrong.

ADODB.Recordset (0x800A0E78)

Operation is not allowed when the object is closed.

/Password.asp, line 42

Here is the code with the eror line in bold.

<%

dim dataConn, adocmd, IsValid

set dataConn = Server.CreateObject("ADODB.Connection")

dataConn.Open "PROVIDER=SQLOLEDB; DATA SOURCE=REMITCOCLTVOL;" _

& "database=REMITCOSQLServer;Trusted_Connection=yes;"

Set adocmd = Server.CreateObject("ADODB.Command")

adocmd.CommandText = "sp_IsValidLogon"

adocmd.ActiveConnection = dataConn

adocmd.CommandType = adCmdStoredProc

adocmd.Parameters.Append adocmd.CreateParameter("return", _

adInteger, adParamReturnValue, 3)

adocmd.Parameters.Append adocmd.CreateParameter("username", _

adVarChar, adParamInput, 16, _

Request.Form("UserName"))

adocmd.Parameters.Append adocmd.CreateParameter("password", _

adVarChar, adParamInput, 16, _

Request.Form("Password"))

adocmd.Execute

' Run the SP by executing the command and grab

' the returned recordset.

Set rstStoredProc = adocmd.Execute

' Message so people know what the sample's doing:

Response.Write "It returned a recordset which I used " _

& "to print out this name: "

while not rstStoredProc.eof

rstStoredProc.movenext

' Spit out our data which I pull out of the recordset.

'Response.Write Trim(rstStoredProc("Supervisors"))

'Response.Write " "

'Response.Write Trim(rstStoredProc("Password"))

wend

' Spit out our data which I pull out of the recordset.

'Response.Write Trim(rstStoredProc("Supervisors"))

'Response.Write " "

'Response.Write Trim(rstStoredProc("Password"))

' Message so people know what the sample's doing:

Response.Write "." & vbCrLf

' Kill our objects

Set paramId = Nothing

Set rstStoredProc = Nothing

Set adocmd = Nothing

' Close and kill our connection

Set dataConn = Nothing

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 4

where did you declare rstStoredProc ?

you can test if rstStoredProc exist with

if not(rstStoredProc is nothing)

*move

' Spit out our data which I pull out of the recordset.

'Response.Write Trim(rstStoredProc("Supervisors"))

'Response.Write " "

'Response.Write Trim(rstStoredProc("Password"))

before

rstStoredProc.movenext /*basically requests next row*/

*the example also mentioned

rstStoredProc.CursorLocation = adUseClient

rstStoredProc.Open adocmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

Anthony Phillips-301303 Mr or Mrs. 500 Points: 574 More actions · Answer 5

Guys, I really appreciate the help. Bear with me just a bit longer. I think I'm making some progress because I'm now getting a different error. It's happening at the loop. As you can see I'm usng rstStoredProc.Open to open the record set. Why is it telling me that it's closed? Also, I'm not quite sure if I'm declaring rstStoredProc. How would I do that?

ADODB.Recordset (0x800A0E78)

Operation is not allowed when the object is closed.

/Password.asp, line 42

' Run the SP by executing the command and grab

' the returned recordset.

Set rstStoredProc = adocmd.Execute

rstStoredProc.CursorLocation = adUseClient

rstStoredProc.Open adocmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

while not rstStoredProc.eof

' Message so people know what the sample's doing:

Response.Write "It returned a recordset which I used " _

& "to print out this name: "

' Spit out our data which I pull out of the recordset.

'Response.Write Trim(rstStoredProc("Supervisors"))

'Response.Write " "

'Response.Write Trim(rstStoredProc("Password"))

' Message so people know what the sample's doing:

Response.Write "." & vbCrLf

rstStoredProc.movenext

wend

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 6

*Declared: do you have a line

Set rstStoredProc= Server.CreateObject("ADODB.Recordset")

*Is your connection string correct? (see error object not open)

From http://www.connectionstrings.com/

Trusted_Connection=yes is for ODBC whilst you are using OLEDB

*oledb

"Provider=sqloledb;Data Source=REMITCOCLTVOL;Initial Catalog=REMITCOSQLServer;Integrated Security=SSPI;"

Anthony Phillips-301303 Mr or Mrs. 500 Points: 574 More actions · Answer 7

Thanks for the response. I truly appreciate it. I used the if fuction for getting the session state and it returned "Connection successful". And I see that I have declared rstStoredProcedure as my recordset. But It's still telling me that the object rstStoredProcedure is closed when I try to display it. Here is the entire code with all the changes highlighted. What could be wrong?

<%

dim dataConn, adocmd, IsValid

Set rstStoredProc = Server.CreateObject("ADODB.Recordset")

dataConn.Open "Provider=sqloledb;Data Source=REMITCOCLTVOL;Initial Catalog=REMITCOSQLServer;Integrated Security=SSPI;"

Set adocmd = Server.CreateObject("ADODB.Command")

adocmd.CommandText = "sp_IsValidLogon"

adocmd.ActiveConnection = dataConn

adocmd.CommandType = adCmdStoredProc

adocmd.Parameters.Append adocmd.CreateParameter("return", _

adInteger, adParamReturnValue, 3)

adocmd.Parameters.Append adocmd.CreateParameter("username", _

adVarChar, adParamInput, 16, _

Request.Form("UserName"))

adocmd.Parameters.Append adocmd.CreateParameter("password", _

adVarChar, adParamInput, 16, _

Request.Form("Password"))

adocmd.Execute

' Run the SP by executing the command and grab

' the returned recordset.

Set rstStoredProc = adocmd.Execute

rstStoredProc.CursorLocation = adUseClient

rstStoredProc.Open adocmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

Trouble Spot

while not rstStoredProc.eof

' Message so people know what the sample's doing:

Response.Write "It returned a recordset which I used " _

& "to print out this name: "

' Spit out our data which I pull out of the recordset.

'Response.Write Trim(rstStoredProc("Supervisors"))

Response.Write " "

'Response.Write Trim(rstStoredProc("Password"))

If dataConn.State = adStateOpen Then

Response.Write " Connection succesfull"

Else

Response.Write " Connection failed"

End If

' Message so people know what the sample's doing:

Response.Write "." & vbCrLf

rstStoredProc.movenext

wend

' Kill our objects

Set paramId = Nothing

Set rstStoredProc = Nothing

Set adocmd = Nothing

' Close and kill our connection

Set dataConn = Nothing

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 8

can you remove the line

Set rstStoredProc = adocmd.Execute

Anthony Phillips-301303 Mr or Mrs. 500 Points: 574 More actions · Answer 9

Well, I commented out the line Set rstStoredProc = adocmd.Execute and it's still telling me that the rstStoredPorc is closed. I've never seen anything like this. If I comment out "while not rstStoredProc.eof" the page runs just fine. Maybe if I gave the fields to my table it might help. The Stored Procedure is below as well. It's used to check logons against what's in the database. If some trys to hack into the site using another member's name without the password it will deactivate the account after 4 attempts. All this is working fine I just can't display a recordset. Any thoughts?

Table StarAdmin Field Names and Datatypes

RecordID int 4

Supervisors nvarchar 16

Password nvarchar 16

address nvarchar 150

SiteName nvarchar 80

Areas nvarchar 50

PTONames nvarchar 100

TeamLeaders nvarchar 50

Region nvarchar 150

UserType nvarchar 150

City nvarchar 150

Stat nvarchar 150

Zipcode nvarchar 150

Active numeric 9

Stored Procedure sp_IsValidLogon

CREATE PROCEDURE [dbo].[sp_IsValidLogon]

@UserName varchar (16),

@Password varchar (16)

As

if exists(Select * From StarAdmin

Where Supervisors = @UserName

And

Password = @Password

And

Active = 1)

begin

return(1)

end

else

begin

INSERT INTO FailedLogins(Supervisors, Password)

values(@UserName, @Password)

declare @totalFails int

Select @totalFails = Count(*) From FailedLogins

Where Supervisors = @UserName

And dtFailed > GetDate()-1

if (@totalFails > 5)

UPDATE StarAdmin Set Active = 0

Where Supervisors = @UserName

return(0)

end

GO

I must admint, this is wuppin my tail.

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 10

Simply because there is no recordset in sp_IsValidLogon, just a return code.

You want to capture the exit code?

Anthony Phillips-301303 Mr or Mrs. 500 Points: 574 More actions · Answer 11

Will capturing the exit code allow me to response.write out the results? I'd love to be able to do that.

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 12

Got puzzled again.

Do you wish to capture 1/0 (the exit code)

or the number of failed attempts?

Anthony Phillips-301303 Mr or Mrs. 500 Points: 574 More actions · Answer 13

I'm back. The first point is what I want. (the exit code) How do I capture it and print it out?

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 14

Welcome back.

Try the sample at http://support.microsoft.com/kb/q194792/.

Apparently there must be a recordset in order to capture the returncode.