Solved

How to execute stored procedure from Classic ASP page

Posted on 2014-10-14
6
446 Views
Last Modified: 2014-10-14
I would like to update my record with the help of a stored procedure.
I am using Classic ASP. This is the form on my web page:
<form action="Update.asp" method="POST">

<input type="text" name="Week_1" value="<%=rs("Week_1")%>" />
<br />
<input type="text" name="Week_2" value="<%=rs("Week_2")%>" />
<br />
<input type="text" name="Week_3" value="<%=rs("Week_3")%>" />

<input type="submit" value="Submit" name="submit" />

What would be the code of the Update.asp if the stored procedure is the following:
CREATE PROCEDURE [dbo].[UpdateVacWeeks]
(
@Week_1 INT,
@Week_2 INT,
@Week_3 INT

)
AS
BEGIN
UPDATE dbo.Vac_Weeks
SET Week_1=@Week_1,Week_2=@Week_2,Week_3=@Week_3
WHERE WorkArea=@WorkArea
END

Open in new window


Thank you for your help.
0
Comment
Question by:romsom
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40379890
You have 4 parameters in your procedure by only three defined in the CREATE statement.. If you add the forth you need to adjust the code below...


Taken from http://support.microsoft.com/kb/164485  and adjusted for your case:

 <%@ LANGUAGE="VBSCRIPT" %>
   <!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
   <HTML>
   <HEAD><TITLE>Place Document Title Here</TITLE></HEAD>
   <BODY>
<BR>
   <%
   Set cn = Server.CreateObject("ADODB.Connection")
   Set cmd = Server.CreateObject("ADODB.Command")
   cn.Open "data source name", "userid", "password"
   Set cmd.ActiveConnection = cn
   cmd.CommandText = "UpdateVacWeeks"
   cmd.CommandType = adCmdStoredProc
   ' Ask the server about the parameters for the stored proc
   cmd.Parameters.Refresh
   ' Assign a value to the 2nd parameter.
   ' Index of 0 represents first parameter.
 cmd.Parameters(0) = Week_1   
cmd.Parameters(1) = Week_2
 cmd.Parameters(2) = Week_3
   cmd.Execute
   %>
  
   ReturnValue = <% Response.Write cmd.Parameters(0) %><P>

   </BODY>
   </HTML>

Open in new window

0
 
LVL 4

Expert Comment

by:javaftper
ID: 40379895
declaresthe stored procedure, and then explicitly declare the parameters.

as following example-

   <%
   Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open "data source name", "userid", "password"
   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = cn
   cmd.CommandText = "sp_test"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
      adParamReturnValue)
   cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
      adParamInput)
   ' Set value of Param1 of the default collection to 22
   cmd("Param1") = 22
   cmd.Execute
   %>

Open in new window

0
 
LVL 33

Accepted Solution

by:
Big Monty earned 500 total points
ID: 40379899
good to see you again romsom! try this to get started:

dim cmd, sql, conn
set conn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
conn.Open connectionString 
 
sql = "exec UpdateVacWeeks ?, ?, ?"
with cmd
    .ActiveConnection = conn
    .CommandText = sql
    .Parameters.Append  .CreateParameter( "@Week_1", adInteger, adParamInput, , Request("Week_1") )
    .Parameters.Append  .CreateParameter( "@Week_2", adInteger, adParamInput, , Request("Week_2") )
    .Parameters.Append  .CreateParameter( "@Week_3", adInteger, adParamInput, , Request("Week_3") )
    .Execute
end with

Open in new window


you do have an extra undefined variable in your SP, @WorkArea, that you'll need to deal with, but this should get you going in the right direction
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 22

Expert Comment

by:plusone3055
ID: 40379906
Here is a microsoft article which will give you exactly what you need  to write it out :)

FROM
http://support.microsoft.com/kb/164485/en-us
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40379915
forgot to mention in the solution I provided that you'll need to include adovbs.inc, which you can download from here

http://www.4guysfromrolla.com/webtech/code/adovbs.txt
0
 

Author Closing Comment

by:romsom
ID: 40380041
Thank you everyone. I also checked out the links you provided but I still couldn't customize the code for my needs.
Big Monty, your code is working fine, now I'll be able to use it for all my update pages.
Thanks again!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question