Classic ASP calling a SQL 2000 SP

jonsey · March 1st, 2007, 10:17 AM

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

dparsons · March 1st, 2007, 10:21 AM

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
================================================== =========

jonsey · March 2nd, 2007, 10:28 AM

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

dparsons · March 2nd, 2007, 10:45 AM

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
================================================== =========

jonsey · March 2nd, 2007, 03:40 PM

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)

dparsons · March 5th, 2007, 04:57 PM

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
================================================== =========

DaveGerard · July 30th, 2007, 04:03 PM

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

(cn being your connection object)