Professional Web Applications Themes

Command Object and RecordSet ASP - ASP Database

I am very knew to asp and Im having problems returning the results from my stored procedure in the browser. The asp code and the html form code is below. Can anyone guide me as to where I've went wrong. Im not sure if I've referred to the asp code in the right place, and I don't know if the recordset is set up correctly for a html table of reselts to appear. I would really appreciate any help you could give me. I think my connection is fine but im not sure about the rest of it. The stored ...

  1. #1

    Default Command Object and RecordSet ASP

    I am very knew to asp and Im having problems returning the results from my stored procedure in the browser. The asp code and the html form code is below. Can anyone guide me as to where I've went wrong. Im not sure if I've referred to the asp code in the right place, and I don't know if the recordset is set up correctly for a html table of reselts to appear. I would really appreciate any help you could give me. I think my connection is fine but im not sure about the rest of it. The stored procedure requires one input parameter which has a data type of smalldatetime in SQL

    <
    dim c
    cn = "
    if len(Request.form("ReportByPubClass2")) > 0 the
    'login processin
    'Connect to Databas
    Set dbcn = server.CreateObject("ADODB.Connection"
    set dbrs = server.CreateObject("ADODB.Recordset"
    Set byclasspubclass = server.CreateObject ("ADODB.Command"
    dbcn.open Application ("connectionString"
    'create the command objec
    With byclassnopric
    .CommandType = adCmdStoredPro
    'set the name of the stored procedur
    .CommandText = "ByClassPUBCLASS
    'create and append the input parameters
    .Parameters.Append byclasspubclass.CreateParameter ("t_week_end_date", adDate
    'set the initial value of the paramete
    .Parameters ("t_week_end_date").Value = t_week_end_date.Tex
    'set the connectio
    .ActiveConnection = dbc
    End Wit
    'call the stored procedure and save the results in a recordse
    dbrs = byclasspubclass.Execute
    end i
    %><form name="accounting" method="post" action="displayreports.asp"><p>Please Enter a Weekending Date <%cn%><input type="text" name="t_week_end_date"></p><p><input type="submit" name="ReportByPubClass2" value="View Report"></p></form>
    Sean Guest

  2. #2

    Default Re: Command Object and RecordSet ASP

    Sean wrote:
    > I am very knew to asp and Im having problems returning the results
    Before I start digging into your code, I need to know what the problem is.
    Error messages? Incorrect results?

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

  3. #3

    Default Re: Command Object and RecordSet ASP

    On the click of the "ReportByPubClass2" button nothing seems to be happening. I've got a page set up called displayresults.asp and would like the stored procedures to appear on this page when submit the form. Im using the form to pass in the t_week_end_date parameter required by the stored procedure to run. I know I've not got it set up correctly for the results to come back in a table either. At the moment Im not even sure if my code is asking the "ReportByPubClass2" button to run the asp code. Im not getting any errors nothing seems to be happening just. I hope this helps
    Thank you very much for taking time to help me
    Sean Guest

  4. #4

    Default Re: Command Object and RecordSet ASP

    Sean wrote:
    > I am very knew to asp and Im having problems returning the results
    > from my stored procedure in the browser. The asp code and the html
    > form code is below. Can anyone guide me as to where I've went wrong.
    > Im not sure if I've referred to the asp code in the right place, and
    > I don't know if the recordset is set up correctly for a html table of
    > reselts to appear. I would really appreciate any help you could give
    > me. I think my connection is fine but im not sure about the rest of
    > it. The stored procedure requires one input parameter which has a
    > data type of smalldatetime in SQL.
    >
    > <%
    You dimmed cn, but you used dbcn later on in your code. To prevent errors
    like this, use the following line as the first line in your script:

    Option Explicit

    This forces you to Dim all your variables and enforces that you do not use
    any undimmed variables
    > dim cn
    > cn = ""
    There is no reason to have this line (the above line, that is)
    > if len(Request.form("ReportByPubClass2")) > 0 then
    > 'login processing
    > 'Connect to Database
    > Set dbcn = server.CreateObject("ADODB.Connection")
    > set dbrs = server.CreateObject("ADODB.Recordset")
    > Set byclasspubclass = server.CreateObject ("ADODB.Command")
    > dbcn.open Application ("connectionString")
    > 'create the command object
    > With byclassnoprice
    > .CommandType = adCmdStoredProc
    > 'set the name of the stored procedure
    > .CommandText = "ByClassPUBCLASS"
    > 'create and append the input parameters
    > .Parameters.Append byclasspubclass.CreateParameter
    > ("t_week_end_date", adDate) 'set the initial value of the parameter
    This is incorrect syntax, in addition to its being the wrong datatype
    constant. You may benefit from my stored procedure code generator which can
    be found here:
    [url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

    However, see below for a simpler method to execute your procedure.
    > .Parameters ("t_week_end_date").Value = t_week_end_date.Text
    The server side code has no direct access to your client-side objects.
    Instead, the values from named controls are passed via the Request object.
    Since you used the POST method, the values will be found in the Form
    collection. Like this:

    Dim sDate
    sDate=Request.Form("t_week_end_date")
    If you had used the GET method, the value would have been found in the
    Querystring collection.

    COMMENT: you need to validate that sDate contains a valid date before going
    any further in your code. This should be done BEFORE creating and opening
    any ADO connections, etc.

    > 'set the connection
    > .ActiveConnection = dbcn
    This should be:
    Set .ActiveConnection = dbcn
    > End With
    > 'call the stored procedure and save the results in a recordset
    > dbrs = byclasspubclass.Execute
    You may not realize that you do not need to use a Command object to run this
    stored procedure. A simpler technique is known as
    "procedure-as-connection-method". When my stored procedure has no output
    paramters, and I'm not interested in reading the Return parameter value, I
    don't bother with the explicit Command object. Instead, I will do something
    like this:

    dbcn.ByClassPUBCLASS sDate, dbrs
    > end if
    OK. You now have a recordset. BUT, nothing further will happen because you
    don't do anything with this recordset. You need to write the recordset's
    data to the Response object - something like this:

    if dbrs.eof then
    response.write "No records were returned"
    else
    Response.Write "<table border=1><tr><td>"
    sHTML=dbrs.GetString(2,,"</td><td>", _
    "</td></tr><tr><td>","NA")
    sHTML = Left(sHTML, len(sHTML) - 8)
    response.Write sHTML
    response.write "</table>"
    end if
    dbrs.close: set dbrs=nothing
    dbcn.close: set dbcn=nothing

    This (GetString) is about the fastest way to get the contents of a recordset
    put on the screen. Other techniques can be found here:
    [url]http://www.aspfaq.com/show.asp?id=2467[/url]


    > %><form name="accounting" method="post"
    > action="displayreports.asp"><p>Please Enter a Weekending Date
    > <%cn%><input type="text" name="t_week_end_date"></p><p><input
    > type="submit" name="ReportByPubClass2" value="View
    > Report"></p></form>
    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

  5. #5

    Default Re: Command Object and RecordSet ASP

    Thanks very much for your help Bob i will try all this. Im very grateful
    Sean Guest

Similar Threads

  1. adodb.recordset object and the IIS session object
    By Mbtch@. in forum ASP Components
    Replies: 0
    Last Post: June 8th, 05:25 PM
  2. Replies: 1
    Last Post: March 3rd, 02:15 PM
  3. Can I re-use a recordset object?
    By Terry in forum ASP Database
    Replies: 4
    Last Post: January 28th, 07:23 AM
  4. Vb6 object returning ADO Recordset - Error in .NET
    By Developer in forum ASP.NET General
    Replies: 0
    Last Post: August 11th, 03:27 PM
  5. Seeing If Recordset Object Exists !
    By Adam Knight in forum ASP Database
    Replies: 3
    Last Post: August 5th, 01:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139