Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 1st, 2007, 10:17 AM
Authorized User
 
Join Date: Jan 2007
Location: , , USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jonsey Send a message via Yahoo to jonsey
Default Classic ASP calling a SQL 2000 SP

Looking for the method of calling a SQL store procedures into my ASP form.


Reply With Quote
  #2 (permalink)  
Old March 1st, 2007, 10:21 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Just setup your database connections as you normally would and pass the command:

exec usp_myProcedure

which will execute the stored procedure.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #3 (permalink)  
Old March 2nd, 2007, 10:28 AM
Authorized User
 
Join Date: Jan 2007
Location: , , USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jonsey Send a message via Yahoo to jonsey
Default

Thanks for the reply, Co-worker handed me a Book Professiaonl ASP 3.0 which had what I was looking for in it. But running into an issue with my SP. Getting the following error:

Microsoft OLE DB Provider for SQL Server error '80004005'
The precision is invalid.

/paafv5.asp, line 88

Line 88 is the
cmd.Execute lngRecsAffected, adCmd Or adExecuteNoRecords

SP file:
Code:
ALTER PROCEDURE smc_paal_user.sp_Insert
(@SurveyCreateDate datetime, @Value numeric(18, 0))

As

Begin
    Set NoCount on
    DECLARE @SurveyID numeric(18, 0)
    Insert into Servey(SurveyCreateDate)
    Values(@SurveyCreateDate)

    Select @SurveyID=@@Identity

    Insert into Answer
    (SurveyID, AnswerValue)
    Values
    (@SurveyID, @Value)

End
My ASP:

Code:
Dim cmd
   
   Set oConn = Server.CreateObject("ADODB.Connection")
   
   oConn.Open "Provider=SQLOLEDB;server=test;uid=sauser;pwd=sauser;database=test;"

   
   Set cmd = Server.CreateObject("ADODB.Command")
   
   Set cmd.ActiveConnection = oConn
   
   cmd.CommandText = "sp_Insert"
   
   cmd.CommandType = adCmdStoredProc
   
   cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)
   cmd.Parameters.Append cmd.CreateParameter("@SurveyCreateDate", adDate, adParamInput)
   cmd.Parameters.Append cmd.CreateParameter("@Value", adNumeric, adParamInput)
   
   ' Set value of Param
   
  cmd.Parameters("@SurveyCreateDate") = FormatDateTime(Now(),0)
  cmd.Parameters("@Value") = 23

  cmd.Execute lngRecsAffected,  adCmd Or adExecuteNoRecords
%>
   

<h2>Thanks for submitting your information to us!</h2>

        <p>
        <strong>The resulting SQL statement was:</strong>
        ReturnValue = <% Response.Write cmd("RetVal") %>
        </p>

        <p>
        <strong>Number of records affected:</strong> <%= lngRecsAffected %>
        </p>
     
             
<%
Set cmd = nothing
oConn.Close
Set oConn = Nothing
%>
Cheers,
Jonsey
Reply With Quote
  #4 (permalink)  
Old March 2nd, 2007, 10:45 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

You need to set the percision in code since your using a parameterized query.
Soemthing like:
oParm.NumericScale = 2
oParm.Precision = 6

Search the msdn.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #5 (permalink)  
Old March 2nd, 2007, 03:40 PM
Authorized User
 
Join Date: Jan 2007
Location: , , USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jonsey Send a message via Yahoo to jonsey
Default

OK I have finally go this to write all my data to the database all but one thing.

When it trys to write the AnswerValue which is numeric get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to numeric.

guess I need to convert the str to a numeric format can this be done in SP or does it have to be done on the ASP page?

Code:
If Request.Form("action") = "Save Form Data" Then
    ' Do our DB insert!
    'Response.Write(lGroup & "_" & oGroup.SelectSingleNode("@weight").Text & "_" & lQuestion & "_" & oQuestion.SelectSingleNode("@weight").Text & "_" & Request.Form("question_" & lGroup & "_" & lQuestion) & "_" & Request.Form("comment_" & lGroup & "_" & lQuestion) )

    For lGroup = 0 to oXml.SelectNodes("//root/group").Length - 1
        Set oGroup = oXml.SelectNodes("//root/group").Item(lGroup)

    For lQuestion = 0 to oGroup.SelectNodes("question").Length - 1
        Set oQuestion = oGroup.SelectNodes("question").Item(lQuestion)


Dim objConn, sqlInsPAAF, AnswerComment

AnswerComment = Request.Form("comment_" & lGroup & "_" & lQuestion)
AnswerValue = Request.Form("question_" & lGroup & "_" & lQuestion)


set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server}; Server=[Server]; Database=[Database]; Uid=[Username]; Pwd=[Password];"

sqlInsPAAF = "sp_Insert2 '" & AnswerComment & "', '" & AnswerValue & "'"

objConn.Execute(sqlInsPAAF)

Reply With Quote
  #6 (permalink)  
Old March 5th, 2007, 04:57 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

You can use a Convert() function in the procedure itself, however, it is more likely that you are passing in some sort of Alpha character with the numeric value.

The reason being, if i pass in the value

1234 enlclosed in ' ' is the same as passing in the value of 1234; the ' ' just denote it as a string instead of an int value.

In your case, its a little different since you are using a paramertized query and you are not having to deal with ' ' so check the value of your variable.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========
Reply With Quote
  #7 (permalink)  
Old July 30th, 2007, 04:03 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Minneapolis, MN, USA.
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

set rs = cn.execute("YourProcedure " & YourParameter)

(cn being your connection object)

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
parametising sql statements in ASP classic trufla Classic ASP Basics 5 June 18th, 2008 02:38 PM
SQl Injection through ASP and MS SQl 2000 cancer10 Classic ASP Databases 1 October 27th, 2007 03:21 AM
Calling an SP from a DTS Package? Gibi SQL Server DTS 1 April 18th, 2007 05:47 AM
MS Access .mdb calling SQL server 2000 stored proc fazzou Access 6 September 8th, 2006 11:32 AM
accessing MS SQL DB from Classic ASP script crmpicco SQL Server ASP 2 June 1st, 2006 03:43 PM



All times are GMT -4. The time now is 10:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.