Solved

How to execute stored procedure from Classic ASP page

Posted on 2014-10-14
6
523 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with query 3 41
Code Manager | Snippits 2 93
Pass SQL to JSON. Page is in classic ASP and using Jquery 4 56
JS to redirect to prev page 8 44
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/…
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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