Post reply

Displaying a recordset from a stored procedure

  • 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

    '.............

    %>

  • February 15, 2006 at 10:51 am

    #621122

    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

  • February 15, 2006 at 10:53 am

    #621123

    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

  • February 15, 2006 at 11:30 am

    #621146

    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 "<p>It returned a recordset which I used " _

     & "to print out this name: <strong>"

    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 "</strong>.</p>" & vbCrLf

    ' Kill our objects

    Set paramId = Nothing

    Set rstStoredProc = Nothing

    Set adocmd = Nothing

    ' Close and kill our connection

    Set dataConn = Nothing

  • February 15, 2006 at 2:01 pm

    #621190

    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

  • February 16, 2006 at 9:32 am

    #621408

    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 "<p>It returned a recordset which I used " _

     & "to print out this name: <strong>"

    ' 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 "</strong>.</p>" & vbCrLf

    rstStoredProc.movenext

    wend

  • February 16, 2006 at 10:22 am

    #621422

    *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;"

  •  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

  • February 16, 2006 at 11:16 am

    #621437

    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 "<p>It returned a recordset which I used " _

     & "to print out this name: <strong>"

    ' 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 "</strong>.</p>" & vbCrLf

    rstStoredProc.movenext

    wend

    ' Kill our objects

    Set paramId = Nothing

    Set rstStoredProc = Nothing

    Set adocmd = Nothing

    ' Close and kill our connection

    Set dataConn = Nothing

  • February 16, 2006 at 11:47 am

    #621443

    can you remove the line

    Set rstStoredProc = adocmd.Execute

     

     

  • February 17, 2006 at 5:59 am

    #621608

    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.

  • February 17, 2006 at 10:55 am

    #621749

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

    You want to capture the exit code?

     

  • February 17, 2006 at 11:23 am

    #621760

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

  • February 17, 2006 at 12:38 pm

    #621785

    Got puzzled again.

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

    or the number of failed attempts?

  • February 22, 2006 at 7:11 am

    #622609

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

  • February 22, 2006 at 10:35 am

    #622685

    Welcome back.

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

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

    • Viewing 15 posts - 1 through 15 (of 15 total)

    You must be logged in to reply to this topic. Login to reply

    Cookies on SQLServerCentral

    This website stores cookies on your computer.

    These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

    To find out more about the cookies we use, see our Privacy Policy