ASP help required please

[Stephen]

Hey All,
I have a stored procedure in SQL server which when exectuated returns a table of results. I would like to be able to pass in the parameters from a form into the stored procedures and then view the table of results in a html table when the submit button is clicked.

The parameters for my SQL statement are: -
@weekenddate1, @weekenddate2, @txtpub, @txtprice and @txtDescription

Can anyone help me write the asp code to pass these values into a form and return a table of results on a new html page.

My html form looks like the following: -
<form name="form1" method="post" action=""><table width="52%" border="0"><tr><td width="43%">FROM DATE</td><td width="57%"><input name="fromdate" type="text" id="fromdate"></td></tr><tr><td>TO DATE</td><td><input name="todate" type="text" id="todate"></td></tr><tr><td>PUBLICATION</td><td><input name="publication" type="text" id="publication"></td></tr><tr><td>PRICE</td><td><input name="price" type="text" id="price"></td></tr><tr><td>DESCRIPTION</td><td><input name="description" type="text" id="description"></td></tr><tr><td>&nbsp;</td><td><input name="VIEW_REPORT" type="submit" id="VIEW_REPORT" value="Submit"><input name="CLEAR" type="reset" id="CLEAR" value="Reset"></td></tr></table></form>

I will be so so so so pleased if anyone is able to help me. My asp knowledge is minimal

[Ray at <%=sLocation%> [MVP]]

http://www.aspfaq.com/5003

--

Ray at home
Microsoft ASP MVP

"Stephen" <stephense...@hotmail.com> wrote in message
news:67F9EA5E-BD89-4AA2...@microsoft.com...

[Bob Barrows]

Stephen wrote:
> Hey All,
> I have a stored procedure in SQL server which when exectuated returns
> a table of results. I would like to be able to pass in the
> parameters from a form into the stored procedures and then view the
> table of results in a html table when the submit button is clicked.
>
> The parameters for my SQL statement are: -
> @weekenddate1, @weekenddate2, @txtpub, @txtprice and @txtDescription
>
> Can anyone help me write the asp code to pass these values into a
> form and return a table of results on a new html page.
>
> My html form looks like the following: -
> <form name="form1" method="post" action="">

<snip>

You did not mention the name of the stored procedure ... I will call it
GetRecords. Where you see "GetRecords" below, substitute the name of your
procedure.

It would also have been helpful to see the datatypes of those parameters. In
the future, show the beginning of the stored procedure's creation script,
the part that says:

CREATE PROCEDURE <procedurename> (
<parameters> )
AS

Tip: The first line of your procedure following "AS" should be:

SET NOCOUNT ON

This will prevent informational messages from being sent to the client as
resultsets, and improve performance as well.

I will assume the two dates are datetime and the three txt ones are
character (although why anyone would use a character datatype for a price is
beyond me)

Create a new page called "displayrecords.asp". Modify the above form tag in
your initial page to:
<form name="form1" method="post" action="displayrecords.asp">

The following code in displayrecords.asp will display a very basic table,
very quickly. If you need more control over the table properties, use
GetRows as described in this article: http://www.aspfaq.com/show.asp?id=2467

<%
Option Explicit
Dim cn, rs, dDate1, dDate2, sPub, sPrice, sDesc
Dim bValid, sHTML
dDate1=CDate(Request.Form("fromdate"))
dDate2=CDate(Request.Form("todate"))
sPub=Request.Form("publication")
sPrice=Request.Form("price")
sDesc=Request.Form("description")

'Validate the above data - make sure they contain what
'they are supposed to contain. If not, display an error
'message to the user and set bValid to false
'set the bValid variable to true if the data is valid, then:

If bValid = true then
set cn=server.createobject("adodb.connection")

'substitute your information below - do not use the sa
'account! Create a sql login with the minimum rights
'needed - basically, it needs to be able to run this stored
'procedure

'You may want to do this in an include file

cn.open "Provider=sqloledb;" & _
"Data Source=yourserver;" & _
"Initial Catalog=yourdatabase;" & _
"User ID = sql_login_name;" & _
"Password = sql_password"
set rs=server.createobject("adodb.recordset")
cn.GetRecords dDate1, dDate2, sPub, sPrice, _
sDesc, rs
if rs.eof then
response.write "No records were returned"
else
Response.Write "<table border=1><tr><td>"
sHTML=rs.GetString(2,,"</td><td>", _
"</td></tr><tr><td>")
sHTML = Left(sHTML, len(sHTML) - 8)
response.Write sHTML
response.write "</table>"
end if
rs.close: set rs=nothing
cn.close: set cn=nothing
end if
%>

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"