How to execute stored procedure from Classic ASP page

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.
romsomIT DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
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
javaftperCommented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

plusone3055Commented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
romsomIT DeveloperAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.