Solved

How to execute stored procedure from Classic ASP page

Posted on 2014-10-14
6
422 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 32

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 32

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now