|
 |
asp_web_howto thread: Sending Parameters to a stored procedure
Message #1 by "Grant, Maughtlyn V" <GrantMV@s...> on Fri, 21 Feb 2003 07:00:09 -0500
|
|
Hi Everyone
I am new to ASP and I am having problems sending parameters from an asp form
to a stored procedure on SQL 7. I would like to enter two parameters on a
form, and resulting records display in table format. I am not sure what I
am doing wrong, as I have used the examples in the ASP and Databases Wrox
book and still it does not work. I would appreciate any help in making this
work or any tips on where I can find help. I have included the code below
for reference. Any help would be greatly appreciated.
My form code is as follows:
<BODY bgcolor="#FFFFFF" text="#000000">
<FORM name="form1" method="post" action="SummaryReportMGT.asp">
<P><font face="Verdana, Arial, Helvetica, sans-serif"><b>Please enter your
Section
name and Password</b></font></P>
<P><font face="Verdana, Arial, Helvetica, sans-serif">Section Name</font>:
<INPUT type="text" name="txtSection" size="50">
</P>
<P><font face="Verdana, Arial, Helvetica, sans-serif">Password: </font>
<INPUT type="text" name="txtPassword" size="10">
</P>
<P>
<INPUT type="submit" name="Submit" value="Submit">
</P>
<P> </P>
The display page code is as follows
SummaryReportMGT.asp
<%@Language=VBScript%>
<%Option Explicit%>
<!-- #include virtual="/scripts/adovbs.inc" -->
<%
Dim objcmd
Dim oConn
Dim oRS
Dim objParam
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=Representation7"
Set objcmd = Server.CreateObject("ADODB.Command")
Set objcmd.ActiveConnection = oConn
Set objcmd.CommandText = "{CALL SummaryReport}"
Set objcmd.CommandType = adCmdStoredProc
objcmd.Parameters.Append objCmd.CreateParameter("@Section",
adVarChar, adParamInput, 50)
objcmd.Parameters.Append objCmd.CreateParameter("@Password",
adVarChar, adParamInput, 10)
objcmd.Parameters("@Section")=Request.Form ("txtSection")
objcmd.Parameters("@Password")=Request.Form ("txtPassword")
Set oRS = objcmd.Execute
Set objcmd = Nothing
oRS.MoveFirst
%>
<body bgcolor="#FFFFFF" text="#000000">
<table width="99%" border="0">
<tr>
<td width="16%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Name</font></b></td>
<td width="19%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Function
Description</font></b></td>
<td width="18%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Function
Date</font></b></td>
<td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Allocation</font></b></td>
<td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Claims</font></b></td>
<td width="21%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Paid</font></b></td>
</tr>
</table>
<hr>
<table width="99%" border="1">
<%
DO WHILE NOT oRS.EOF %>
<tr>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("EmployeeName").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Description").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("FunctionDate").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Expr1").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Claims").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Paid").Value %></font></td>
</tr>
<% oRS.MoveNext
Loop
%>
</table>
<%
oRS.Close
Set oRS = Nothing
'Set.oRS=Nothing
%>
<p> </p>
<p> </p>
</body>
</html>
Maughtlyn Grant
Message #2 by "Ken Schaefer" <ken@a...> on Mon, 24 Feb 2003 12:34:53 +1100
|
|
Hi,
A couple of things:
a) please tell us what the problem is. Are you getting an error? (if so,
what is it?). Is the code doing nothing? Is it doing something, but not what
you expect?
b) Consider using Cascading Style Sheets instead of font tags. If you want
to keep the font tags, don't bother posting them here though, as it makes
for that much more code we have to try to wade through.
c) One thing I noticed was this:
: Set objcmd.CommandText = "{CALL SummaryReport}"
which should be (assuming the sproc is called "SummaryReport")
objCmd.CommandText = "SummaryReport"
You use "Set" only when you need to set a variable to reference an object.
Here you are assigning a text string to the property, hence you use "Let"
(but you don't need to use Let in VBScript).
Similarly:
: Set objcmd.CommandType = adCmdStoredProc
should be:
objCmd.CommandType = adCmdStoredProc
Next, you do not call objRS.MoveFirst before you access the recordset. If
there are records, the cursor is *always* at the first record. Calling
.Movefirst can result in the whole recordset being recreated (in order to
move to the first record).
Secondly, do not dispose of your Command object until you have finished with
your recordset object.
Lastly, you need to add:
oConn.Close
Set oConn = Nothing
at the bottom
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Grant, Maughtlyn V" <GrantMV@s...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Friday, February 21, 2003 11:00 PM
Subject: [asp_web_howto] Sending Parameters to a stored procedure
:
: Hi Everyone
:
: I am new to ASP and I am having problems sending parameters from an asp
form
: to a stored procedure on SQL 7. I would like to enter two parameters on a
: form, and resulting records display in table format. I am not sure what I
: am doing wrong, as I have used the examples in the ASP and Databases Wrox
: book and still it does not work. I would appreciate any help in making
this
: work or any tips on where I can find help. I have included the code below
: for reference. Any help would be greatly appreciated.
:
: My form code is as follows:
:
: <BODY bgcolor="#FFFFFF" text="#000000">
: <FORM name="form1" method="post" action="SummaryReportMGT.asp">
:
: <P><font face="Verdana, Arial, Helvetica, sans-serif"><b>Please enter
your
: Section
: name and Password</b></font></P>
:
: <P><font face="Verdana, Arial, Helvetica, sans-serif">Section
Name</font>:
:
: <INPUT type="text" name="txtSection" size="50">
: </P>
: <P><font face="Verdana, Arial, Helvetica, sans-serif">Password: </font>
: <INPUT type="text" name="txtPassword" size="10">
: </P>
: <P>
: <INPUT type="submit" name="Submit" value="Submit">
: </P>
: <P> </P>
:
: The display page code is as follows
:
: SummaryReportMGT.asp
: <%@Language=VBScript%>
: <%Option Explicit%>
: <!-- #include virtual="/scripts/adovbs.inc" -->
:
: <%
: Dim objcmd
: Dim oConn
: Dim oRS
: Dim objParam
:
: Set oConn=Server.CreateObject("ADODB.Connection")
: oConn.Open "DSN=Representation7"
:
: Set objcmd = Server.CreateObject("ADODB.Command")
: Set objcmd.ActiveConnection = oConn
: Set objcmd.CommandText = "{CALL SummaryReport}"
: Set objcmd.CommandType = adCmdStoredProc
:
: objcmd.Parameters.Append objCmd.CreateParameter("@Section",
: adVarChar, adParamInput, 50)
: objcmd.Parameters.Append objCmd.CreateParameter("@Password",
: adVarChar, adParamInput, 10)
:
: objcmd.Parameters("@Section")=Request.Form ("txtSection")
: objcmd.Parameters("@Password")=Request.Form ("txtPassword")
:
: Set oRS = objcmd.Execute
:
: Set objcmd = Nothing
:
: oRS.MoveFirst
:
: %>
:
: <body bgcolor="#FFFFFF" text="#000000">
: <table width="99%" border="0">
: <tr>
: <td width="16%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Name</font></b></td>
: <td width="19%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Function
: Description</font></b></td>
: <td width="18%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Function
: Date</font></b></td>
: <td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Allocation</font></b></td>
: <td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Claims</font></b></td>
: <td width="21%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Paid</font></b></td>
: </tr>
: </table>
: <hr>
:
:
: <table width="99%" border="1">
: <%
: DO WHILE NOT oRS.EOF %>
:
: <tr>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("EmployeeName").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Description").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("FunctionDate").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Expr1").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Claims").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Paid").Value %></font></td>
: </tr>
: <% oRS.MoveNext
: Loop
: %>
: </table>
: <%
: oRS.Close
: Set oRS = Nothing
: 'Set.oRS=Nothing
: %>
:
: <p> </p>
: <p> </p>
: </body>
: </html>
:
:
: Maughtlyn Grant
:
:
Message #3 by "Grant, Maughtlyn V" <GrantMV@s...> on Tue, 25 Feb 2003 03:29:25 -0500
|
|
Hi Ken
Thank you for your comments on my code. I will look at it again and make
some corrections and hopefully get it to work. I will get back to you if I
run into problems. Your time is greatly appreciated.
Maughtlyn Grant
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Monday, February 24, 2003 6:14 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
Hi,
A couple of things:
a) please tell us what the problem is. Are you getting an error? (if so,
what is it?). Is the code doing nothing? Is it doing something, but not what
you expect?
b) Consider using Cascading Style Sheets instead of font tags. If you want
to keep the font tags, don't bother posting them here though, as it makes
for that much more code we have to try to wade through.
c) One thing I noticed was this:
: Set objcmd.CommandText = "{CALL SummaryReport}"
which should be (assuming the sproc is called "SummaryReport")
objCmd.CommandText = "SummaryReport"
You use "Set" only when you need to set a variable to reference an object.
Here you are assigning a text string to the property, hence you use "Let"
(but you don't need to use Let in VBScript).
Similarly:
: Set objcmd.CommandType = adCmdStoredProc
should be:
objCmd.CommandType = adCmdStoredProc
Next, you do not call objRS.MoveFirst before you access the recordset. If
there are records, the cursor is *always* at the first record. Calling
.Movefirst can result in the whole recordset being recreated (in order to
move to the first record).
Secondly, do not dispose of your Command object until you have finished with
your recordset object.
Lastly, you need to add:
oConn.Close
Set oConn = Nothing
at the bottom
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Grant, Maughtlyn V" <GrantMV@s...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Friday, February 21, 2003 11:00 PM
Subject: [asp_web_howto] Sending Parameters to a stored procedure
:
: Hi Everyone
:
: I am new to ASP and I am having problems sending parameters from an asp
form
: to a stored procedure on SQL 7. I would like to enter two parameters on a
: form, and resulting records display in table format. I am not sure what I
: am doing wrong, as I have used the examples in the ASP and Databases Wrox
: book and still it does not work. I would appreciate any help in making
this
: work or any tips on where I can find help. I have included the code below
: for reference. Any help would be greatly appreciated.
:
: My form code is as follows:
:
: <BODY bgcolor="#FFFFFF" text="#000000">
: <FORM name="form1" method="post" action="SummaryReportMGT.asp">
:
: <P><font face="Verdana, Arial, Helvetica, sans-serif"><b>Please enter
your
: Section
: name and Password</b></font></P>
:
: <P><font face="Verdana, Arial, Helvetica, sans-serif">Section
Name</font>:
:
: <INPUT type="text" name="txtSection" size="50">
: </P>
: <P><font face="Verdana, Arial, Helvetica, sans-serif">Password: </font>
: <INPUT type="text" name="txtPassword" size="10">
: </P>
: <P>
: <INPUT type="submit" name="Submit" value="Submit">
: </P>
: <P> </P>
:
: The display page code is as follows
:
: SummaryReportMGT.asp
: <%@Language=VBScript%>
: <%Option Explicit%>
: <!-- #include virtual="/scripts/adovbs.inc" -->
:
: <%
: Dim objcmd
: Dim oConn
: Dim oRS
: Dim objParam
:
: Set oConn=Server.CreateObject("ADODB.Connection")
: oConn.Open "DSN=Representation7"
:
: Set objcmd = Server.CreateObject("ADODB.Command")
: Set objcmd.ActiveConnection = oConn
: Set objcmd.CommandText = "{CALL SummaryReport}"
: Set objcmd.CommandType = adCmdStoredProc
:
: objcmd.Parameters.Append objCmd.CreateParameter("@Section",
: adVarChar, adParamInput, 50)
: objcmd.Parameters.Append objCmd.CreateParameter("@Password",
: adVarChar, adParamInput, 10)
:
: objcmd.Parameters("@Section")=Request.Form ("txtSection")
: objcmd.Parameters("@Password")=Request.Form ("txtPassword")
:
: Set oRS = objcmd.Execute
:
: Set objcmd = Nothing
:
: oRS.MoveFirst
:
: %>
:
: <body bgcolor="#FFFFFF" text="#000000">
: <table width="99%" border="0">
: <tr>
: <td width="16%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Name</font></b></td>
: <td width="19%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Function
: Description</font></b></td>
: <td width="18%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Function
: Date</font></b></td>
: <td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Allocation</font></b></td>
: <td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Claims</font></b></td>
: <td width="21%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Paid</font></b></td>
: </tr>
: </table>
: <hr>
:
:
: <table width="99%" border="1">
: <%
: DO WHILE NOT oRS.EOF %>
:
: <tr>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("EmployeeName").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Description").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("FunctionDate").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Expr1").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Claims").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Paid").Value %></font></td>
: </tr>
: <% oRS.MoveNext
: Loop
: %>
: </table>
: <%
: oRS.Close
: Set oRS = Nothing
: 'Set.oRS=Nothing
: %>
:
: <p> </p>
: <p> </p>
: </body>
: </html>
:
:
: Maughtlyn Grant
:
:
Message #4 by "Grant, Maughtlyn V" <GrantMV@s...> on Wed, 26 Feb 2003 05:14:38 -0500
|
|
Hi Ken
I have re-written my code with regard to my first problem. I have included
some of the code below, however I am now getting the following error
message. I have looked at my code, but I can't find any errors. Hope you
can help.
Error message:
Microsoft OLE DB Provider for ODBC Drivers(0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
incorrect syntax near ','. (error line is indicated)
Code:
Dim oConn
Dim oRS
Dim strSql
Dim strConn
Dim strSection
Dim strPassword
strSection=Request.Form("txtSection")
strPassword=Request.Form("txtPassword")
strConn="Driver={SQL
Server};SERVER=Londonsntsf;DATABASE=Representation"
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open strConn
strSql="Execute SummaryReport" & strSection & "," & strPassword
Set oRS=Server.CreateObject("ADODB.Recordset")
error oRS.Open strSql, oConn
oRS.MoveFirst
Maughtlyn Grant
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Monday, February 24, 2003 6:14 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
Hi,
A couple of things:
a) please tell us what the problem is. Are you getting an error? (if so,
what is it?). Is the code doing nothing? Is it doing something, but not what
you expect?
b) Consider using Cascading Style Sheets instead of font tags. If you want
to keep the font tags, don't bother posting them here though, as it makes
for that much more code we have to try to wade through.
c) One thing I noticed was this:
: Set objcmd.CommandText = "{CALL SummaryReport}"
which should be (assuming the sproc is called "SummaryReport")
objCmd.CommandText = "SummaryReport"
You use "Set" only when you need to set a variable to reference an object.
Here you are assigning a text string to the property, hence you use "Let"
(but you don't need to use Let in VBScript).
Similarly:
: Set objcmd.CommandType = adCmdStoredProc
should be:
objCmd.CommandType = adCmdStoredProc
Next, you do not call objRS.MoveFirst before you access the recordset. If
there are records, the cursor is *always* at the first record. Calling
.Movefirst can result in the whole recordset being recreated (in order to
move to the first record).
Secondly, do not dispose of your Command object until you have finished with
your recordset object.
Lastly, you need to add:
oConn.Close
Set oConn = Nothing
at the bottom
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Grant, Maughtlyn V" <GrantMV@s...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Friday, February 21, 2003 11:00 PM
Subject: [asp_web_howto] Sending Parameters to a stored procedure
:
: Hi Everyone
:
: I am new to ASP and I am having problems sending parameters from an asp
form
: to a stored procedure on SQL 7. I would like to enter two parameters on a
: form, and resulting records display in table format. I am not sure what I
: am doing wrong, as I have used the examples in the ASP and Databases Wrox
: book and still it does not work. I would appreciate any help in making
this
: work or any tips on where I can find help. I have included the code below
: for reference. Any help would be greatly appreciated.
:
: My form code is as follows:
:
: <BODY bgcolor="#FFFFFF" text="#000000">
: <FORM name="form1" method="post" action="SummaryReportMGT.asp">
:
: <P><font face="Verdana, Arial, Helvetica, sans-serif"><b>Please enter
your
: Section
: name and Password</b></font></P>
:
: <P><font face="Verdana, Arial, Helvetica, sans-serif">Section
Name</font>:
:
: <INPUT type="text" name="txtSection" size="50">
: </P>
: <P><font face="Verdana, Arial, Helvetica, sans-serif">Password: </font>
: <INPUT type="text" name="txtPassword" size="10">
: </P>
: <P>
: <INPUT type="submit" name="Submit" value="Submit">
: </P>
: <P> </P>
:
: The display page code is as follows
:
: SummaryReportMGT.asp
: <%@Language=VBScript%>
: <%Option Explicit%>
: <!-- #include virtual="/scripts/adovbs.inc" -->
:
: <%
: Dim objcmd
: Dim oConn
: Dim oRS
: Dim objParam
:
: Set oConn=Server.CreateObject("ADODB.Connection")
: oConn.Open "DSN=Representation7"
:
: Set objcmd = Server.CreateObject("ADODB.Command")
: Set objcmd.ActiveConnection = oConn
: Set objcmd.CommandText = "{CALL SummaryReport}"
: Set objcmd.CommandType = adCmdStoredProc
:
: objcmd.Parameters.Append objCmd.CreateParameter("@Section",
: adVarChar, adParamInput, 50)
: objcmd.Parameters.Append objCmd.CreateParameter("@Password",
: adVarChar, adParamInput, 10)
:
: objcmd.Parameters("@Section")=Request.Form ("txtSection")
: objcmd.Parameters("@Password")=Request.Form ("txtPassword")
:
: Set oRS = objcmd.Execute
:
: Set objcmd = Nothing
:
: oRS.MoveFirst
:
: %>
:
: <body bgcolor="#FFFFFF" text="#000000">
: <table width="99%" border="0">
: <tr>
: <td width="16%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Name</font></b></td>
: <td width="19%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Function
: Description</font></b></td>
: <td width="18%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Function
: Date</font></b></td>
: <td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Allocation</font></b></td>
: <td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Claims</font></b></td>
: <td width="21%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
: size="2">Paid</font></b></td>
: </tr>
: </table>
: <hr>
:
:
: <table width="99%" border="1">
: <%
: DO WHILE NOT oRS.EOF %>
:
: <tr>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("EmployeeName").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Description").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("FunctionDate").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Expr1").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Claims").Value %></font></td>
: <td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
: size="2"><% =oRs.Fields("Paid").Value %></font></td>
: </tr>
: <% oRS.MoveNext
: Loop
: %>
: </table>
: <%
: oRS.Close
: Set oRS = Nothing
: 'Set.oRS=Nothing
: %>
:
: <p> </p>
: <p> </p>
: </body>
: </html>
:
:
: Maughtlyn Grant
:
:
Message #5 by "Ken Schaefer" <ken@a...> on Thu, 27 Feb 2003 11:40:31 +1100
|
|
Hi
Can you amend your code to read:
' Existing line
strSql="Execute SummaryReport" & strSection & "," & strPassword
' Add the next two lines:
Response.Write(strSQL)
Response.End
so that we can see exactly what you're sending to the database? The error
message indicates that SQL Server think the command is malformed (eg it is
not a valid call to a sproc. We need to see exactly what you're sending).
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Grant, Maughtlyn V" <GrantMV@s...>
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
: Hi Ken
:
: I have re-written my code with regard to my first problem. I have
included
: some of the code below, however I am now getting the following error
: message. I have looked at my code, but I can't find any errors. Hope you
: can help.
:
: Error message:
:
: Microsoft OLE DB Provider for ODBC Drivers(0x80040E14)
: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
: incorrect syntax near ','. (error line is indicated)
:
: Code:
:
: Dim oConn
: Dim oRS
: Dim strSql
: Dim strConn
: Dim strSection
: Dim strPassword
:
: strSection=Request.Form("txtSection")
: strPassword=Request.Form("txtPassword")
:
: strConn="Driver={SQL
: Server};SERVER=Londonsntsf;DATABASE=Representation"
: Set oConn=Server.CreateObject("ADODB.Connection")
: oConn.Open strConn
:
: strSql="Execute SummaryReport" & strSection & "," & strPassword
: Set oRS=Server.CreateObject("ADODB.Recordset")
: error oRS.Open strSql, oConn
: oRS.MoveFirst
:
:
: Maughtlyn Grant
Message #6 by "Grant, Maughtlyn V" <GrantMV@s...> on Thu, 20 Feb 2003 09:44:36 -0500
|
|
Hi Everyone
I am new to ASP and I am having problems sending parameters from an asp form
to a stored procedure on SQL 7. I would like to enter two parameters on a
form, and resulting records display in table format. I am not sure what I
am doing wrong, as I have used the examples in the ASP and Databases Wrox
book and still it does not work. I would appreciate any help in making this
work or any tips on where I can find help. I have included the code below
for reference.
My form code is as follows:
<BODY bgcolor="#FFFFFF" text="#000000">
<FORM name="form1" method="post" action="SummaryReportMGT.asp">
<P><font face="Verdana, Arial, Helvetica, sans-serif"><b>Please enter your
Section
name and Password</b></font></P>
<P><font face="Verdana, Arial, Helvetica, sans-serif">Section Name</font>:
<INPUT type="text" name="txtSection" size="50">
</P>
<P><font face="Verdana, Arial, Helvetica, sans-serif">Password: </font>
<INPUT type="text" name="txtPassword" size="10">
</P>
<P>
<INPUT type="submit" name="Submit" value="Submit">
</P>
<P> </P>
The display page code is as follows
SummaryReportMGT.asp
<%@Language=VBScript%>
<%Option Explicit%>
<!-- #include virtual="/scripts/adovbs.inc" -->
<%
Dim objcmd
Dim oConn
Dim oRS
Dim objParam
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=Representation7"
Set objcmd = Server.CreateObject("ADODB.Command")
Set objcmd.ActiveConnection = oConn
Set objcmd.CommandText = "{CALL SummaryReport}"
Set objcmd.CommandType = adCmdStoredProc
objcmd.Parameters.Append objCmd.CreateParameter("@Section",
adVarChar, adParamInput, 50)
objcmd.Parameters.Append objCmd.CreateParameter("@Password",
adVarChar, adParamInput, 10)
objcmd.Parameters("@Section")=Request.Form ("txtSection")
objcmd.Parameters("@Password")=Request.Form ("txtPassword")
Set oRS = objcmd.Execute
Set objcmd = Nothing
oRS.MoveFirst
%>
<body bgcolor="#FFFFFF" text="#000000">
<table width="99%" border="0">
<tr>
<td width="16%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Name</font></b></td>
<td width="19%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Function
Description</font></b></td>
<td width="18%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Function
Date</font></b></td>
<td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Allocation</font></b></td>
<td width="13%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Claims</font></b></td>
<td width="21%"><b><font face="Verdana, Arial, Helvetica, sans-serif"
size="2">Paid</font></b></td>
</tr>
</table>
<hr>
<table width="99%" border="1">
<%
DO WHILE NOT oRS.EOF %>
<tr>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("EmployeeName").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Description").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("FunctionDate").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Expr1").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Claims").Value %></font></td>
<td width="16%"> <font face="Verdana, Arial, Helvetica, sans-serif"
size="2"><% =oRs.Fields("Paid").Value %></font></td>
</tr>
<% oRS.MoveNext
Loop
%>
</table>
<%
oRS.Close
Set oRS = Nothing
'Set.oRS=Nothing
%>
<p> </p>
<p> </p>
</body>
</html>
Maughtlyn Grant
Message #7 by "Grant, Maughtlyn V" <GrantMV@s...> on Thu, 27 Feb 2003 04:35:57 -0500
|
|
Hi Ken
I amended my code as requested and the result was
Execute SummaryReportadmin,a
strSection should contain the section name "admin"
strPassword should contain a password of "a"
these are parameters in the sp @Section and @password
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, February 27, 2003 5:43 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
Hi
Can you amend your code to read:
' Existing line
strSql="Execute SummaryReport" & strSection & "," & strPassword
' Add the next two lines:
Response.Write(strSQL)
Response.End
so that we can see exactly what you're sending to the database? The error
message indicates that SQL Server think the command is malformed (eg it is
not a valid call to a sproc. We need to see exactly what you're sending).
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Grant, Maughtlyn V" <GrantMV@s...>
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
: Hi Ken
:
: I have re-written my code with regard to my first problem. I have
included
: some of the code below, however I am now getting the following error
: message. I have looked at my code, but I can't find any errors. Hope you
: can help.
:
: Error message:
:
: Microsoft OLE DB Provider for ODBC Drivers(0x80040E14)
: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
: incorrect syntax near ','. (error line is indicated)
:
: Code:
:
: Dim oConn
: Dim oRS
: Dim strSql
: Dim strConn
: Dim strSection
: Dim strPassword
:
: strSection=Request.Form("txtSection")
: strPassword=Request.Form("txtPassword")
:
: strConn="Driver={SQL
: Server};SERVER=Londonsntsf;DATABASE=Representation"
: Set oConn=Server.CreateObject("ADODB.Connection")
: oConn.Open strConn
:
: strSql="Execute SummaryReport" & strSection & "," & strPassword
: Set oRS=Server.CreateObject("ADODB.Recordset")
: error oRS.Open strSql, oConn
: oRS.MoveFirst
:
:
: Maughtlyn Grant
Message #8 by "Ken Schaefer" <ken@a...> on Fri, 28 Feb 2003 13:02:30 +1100
|
|
Do you notice that:
a) there is no space between the end of your sproc name, and the value of
the first parameter? The database will end up looking for a sproc called
SummaryReportadmin rather than SummaryReport
b) You need to indicate what are strings using '
EXECUTE SummaryReport 'admin', 'a'
better would be:
strSQL = "EXECUTE SummaryReport @Section='admin', @Password='a'"
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Grant, Maughtlyn V" <GrantMV@s...>
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
: Hi Ken
:
: I amended my code as requested and the result was
:
: Execute SummaryReportadmin,a
:
: strSection should contain the section name "admin"
: strPassword should contain a password of "a"
:
: these are parameters in the sp @Section and @password
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: Thursday, February 27, 2003 5:43 AM
: To: ASP Web HowTo
: Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
:
:
: Hi
:
: Can you amend your code to read:
:
: ' Existing line
: strSql="Execute SummaryReport" & strSection & "," & strPassword
: ' Add the next two lines:
: Response.Write(strSQL)
: Response.End
:
: so that we can see exactly what you're sending to the database? The error
: message indicates that SQL Server think the command is malformed (eg it is
: not a valid call to a sproc. We need to see exactly what you're sending).
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Grant, Maughtlyn V" <GrantMV@s...>
: Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
:
:
: : Hi Ken
: :
: : I have re-written my code with regard to my first problem. I have
: included
: : some of the code below, however I am now getting the following error
: : message. I have looked at my code, but I can't find any errors. Hope
you
: : can help.
: :
: : Error message:
: :
: : Microsoft OLE DB Provider for ODBC Drivers(0x80040E14)
: : [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
: : incorrect syntax near ','. (error line is indicated)
: :
: : Code:
: :
: : Dim oConn
: : Dim oRS
: : Dim strSql
: : Dim strConn
: : Dim strSection
: : Dim strPassword
: :
: : strSection=Request.Form("txtSection")
: : strPassword=Request.Form("txtPassword")
: :
: : strConn="Driver={SQL
: : Server};SERVER=Londonsntsf;DATABASE=Representation"
: : Set oConn=Server.CreateObject("ADODB.Connection")
: : oConn.Open strConn
: :
: : strSql="Execute SummaryReport" & strSection & "," & strPassword
: : Set oRS=Server.CreateObject("ADODB.Recordset")
: : error oRS.Open strSql, oConn
: : oRS.MoveFirst
: :
: :
: : Maughtlyn Grant
:
:
:
:
Message #9 by "Grant, Maughtlyn V" <GrantMV@s...> on Fri, 28 Feb 2003 09:27:18 -0500
|
|
Thanks for your advice Ken, but I really wanted the users to enter the
parameters via a form. I have modified the code to read
strSQL= "EXECUTE SummaryReport &request("txtSection") & ","
&request("txtPassword")
Which results in SummaryReportadmin,a just like before. I have been unable
to insert a space between the sproc name and the value of the first
parameter. This may be a simple answer, but I am new at this and can't see
where I am going wrong!!
Maughtlyn Grant
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Friday, February 28, 2003 5:38 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
Do you notice that:
a) there is no space between the end of your sproc name, and the value of
the first parameter? The database will end up looking for a sproc called
SummaryReportadmin rather than SummaryReport
b) You need to indicate what are strings using '
EXECUTE SummaryReport 'admin', 'a'
better would be:
strSQL = "EXECUTE SummaryReport @Section='admin', @Password='a'"
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Grant, Maughtlyn V" <GrantMV@s...>
Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
: Hi Ken
:
: I amended my code as requested and the result was
:
: Execute SummaryReportadmin,a
:
: strSection should contain the section name "admin"
: strPassword should contain a password of "a"
:
: these are parameters in the sp @Section and @password
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: Thursday, February 27, 2003 5:43 AM
: To: ASP Web HowTo
: Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
:
:
: Hi
:
: Can you amend your code to read:
:
: ' Existing line
: strSql="Execute SummaryReport" & strSection & "," & strPassword
: ' Add the next two lines:
: Response.Write(strSQL)
: Response.End
:
: so that we can see exactly what you're sending to the database? The error
: message indicates that SQL Server think the command is malformed (eg it is
: not a valid call to a sproc. We need to see exactly what you're sending).
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Grant, Maughtlyn V" <GrantMV@s...>
: Subject: [asp_web_howto] Re: Sending Parameters to a stored procedure
:
:
: : Hi Ken
: :
: : I have re-written my code with regard to my first problem. I have
: included
: : some of the code below, however I am now getting the following error
: : message. I have looked at my code, but I can't find any errors. Hope
you
: : can help.
: :
: : Error message:
: :
: : Microsoft OLE DB Provider for ODBC Drivers(0x80040E14)
: : [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
: : incorrect syntax near ','. (error line is indicated)
: :
: : Code:
: :
: : Dim oConn
: : Dim oRS
: : Dim strSql
: : Dim strConn
: : Dim strSection
: : Dim strPassword
: :
: : strSection=Request.Form("txtSection")
: : strPassword=Request.Form("txtPassword")
: :
: : strConn="Driver={SQL
: : Server};SERVER=Londonsntsf;DATABASE=Representation"
: : Set oConn=Server.CreateObject("ADODB.Connection")
: : oConn.Open strConn
: :
: : strSql="Execute SummaryReport" & strSection & "," & strPassword
: : Set oRS=Server.CreateObject("ADODB.Recordset")
: : error oRS.Open strSql, oConn
: : oRS.MoveFirst
: :
: :
: : Maughtlyn Grant
:
:
:
:
|
|
|
View Cart
