Command Object and RecordSet ASP

[Sean]

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>

[Bob Barrows]

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"

[Sean]

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

[Bob Barrows]

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"

[Sean]

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