Solved

How to execute stored procedure from Classic ASP page

Posted on 2014-10-14
6
493 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Javascript to allow login/password authorization 4 59
Use Mid in Html 6 21
Two decimal 5 25
Read text on Table 7 25
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

735 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