Calling a stored procedure from ASP

Posted on 2005-05-17
Last Modified: 2008-02-26
I have a stored procedure for inserting the data.  I have created an html form where the user will enter the fields which will populate the data in a table.  The asp page I created is as follows:

    Dim aconnect
    Dim MyConn
    Dim Rs

ActiveConnection= "Provider=MSDASQL;Data Source=My connection string
      Commandtext = "Reports.dbo.Incident"
    CommandType = adCmdStoredProc
    SET Cmd= Server.CreateObject("ADODB.Command")

      strDate_of_Incident= (CDATE(Request.Form("IncidentDate")))
      strStation = Trim(Cstr(Request.Form("Station")))
      strManager = Trim(Cstr(Request.Form("Manager")))
      strEmployee_ID = Trim(Cstr(Request.Form("ENo")))
Cmd.Parameters.Append Cmd.CreateParameter("@Employee_ID",advarchar, strEmployee_ID)
    Cmd.Parameters.Append Cmd.CreateParameter("@Date_of_Incident",addate, strDate_of_Incident)
    Cmd.Parameters.Append Cmd.CreateParameter("@Station",advarWchar, strStation)
    Cmd.Parameters.Append Cmd.CreateParameter("@Manager",advarWchar, strManager)
    Cmd.Parameters.Append Cmd.CreateParameter("@SSN", advarWchar,strSSN)
    Cmd.Parameters.Append Cmd.CreateParameter("@First_Name", advarWchar,strFirst_Name)
    Cmd.Parameters.Append Cmd.CreateParameter("@Last_Name",advarWchar, strLast_Name)
    Cmd.Parameters.Append Cmd.CreateParameter("@Street", advarWchar,strStreet)
    Cmd.Parameters.Append Cmd.CreateParameter("@City", advarWchar,strCity)
    Cmd.Parameters.Append Cmd.CreateParameter("@State", advarWchar,strState)
    Cmd.Parameters.Append Cmd.CreateParameter("@Zip", advarWchar,strZip)
    Cmd.Parameters.Append Cmd.CreateParameter("@Date_Of_Birth", addatetime,strDate_Of_Birth)
    Cmd.Parameters.Append Cmd.CreateParameter("@Sex", advarWchar,strSex)

Set Rs = Cmd.Execute


Can somebody help me in this ASAP and tell me what I am doing wrong.  I keep getting errors.  Thank you very much.

Question by:Lalitha_Ramalingam
    LVL 52

    Expert Comment

    by:Carl Tawn
    It would be useful to know what error you are getting.

    Author Comment

    Thank you for your prompt response.  I get the following error:

    ADODB.Command error '800a0bb9'

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

    LVL 46

    Expert Comment

    A few things:

    SET Cmd= Server.CreateObject("ADODB.Command")
    Cmd.Commandtext = "Reports.dbo.Incident"
    Cmd.CommandType = adCmdStoredProc

    Also, do you have the file included on your page? If not, you will have to declare some constants.


    LVL 46

    Expert Comment

    Also, I think that you need to specify the length of the text parameterized--for example

    objCommand.Parameters.Append objCommand.CreateParameter("@strShipRegion",adVarChar,adParamInput ,12,"SP")

    LVL 46

    Accepted Solution

    Here is a complete example that I wrote--you just need to change the sql string to the name of your SP.

    <%@ Language = VBScript %>
    <%Option Explicit%>
    <%Response.Buffer = True%>
    <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
    Const adCmdStoredProc = &H0004
    Const adParamInput = &H0001
    Const adVarChar = 200
    Const adCurrency = 6
    Const adInteger = 3

    Dim strDataPath, strConnectString, objConnection, objCommand, objRS,i

    'set connection strings for entire application
    strDataPath = server.MapPath("NorthWind.mdb")
    strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                      & " Data Source= " & strDataPath & ";"_
                      & " Mode=Share Deny None;User Id=admin;PASSWORD=;"
    if not IsObject("ojbConnection") then
          set objConnection=Server.CreateObject("ADODB.Connection")
          objConnection.ConnectionTimeout = 15
          objConnection.CommandTimeout =  10
          objConnection.Mode = 3 'adModeReadWrite
          if objConnection.state = 0 then
                objConnection.Open strConnectString
          end if
    end if

    Set objCommand = Server.CreateObject ("ADODB.Command")
    objCommand.ActiveConnection = objConnection
    objCommand.CommandText = "SELECT * FROM Orders WHERE  ShipRegion =@strShipRegion AND Freight>@curFreight AND Year(ShippedDate) =@intYear"
    objCommand.Parameters.Append objCommand.CreateParameter("@strShipRegion",adVarChar,adParamInput ,12,"SP")
    objCommand.Parameters.Append objCommand.CreateParameter("@curFreight",adCurrency,adParamInput , ,50)
    objCommand.Parameters.Append objCommand.CreateParameter("@intYear",adInteger,adParamInput , ,1997)
    Set objRS = Server.CreateObject ("ADODB.Recordset")
    objRS.Open objCommand,,3,3
    Response.Write("<Table Border=1 cellpadding=2 cellspacing=2>")
    Do while not  objRS.EOF
          for i=0 to objRS.Fields.count-1
                Response.Write("<TD>" & objRS(i).Value & "</TD>")
    Response.write("Number of records returned: " & objRS.RecordCount)
    set objCommand = Nothing
    Set objRS = Nothing
    set objConnection = Nothing


    Author Comment

    Thank you so much ftb.  I will try out the code you sent me and let you know. Thank you again
    LVL 46

    Expert Comment

    Okay and good luck.


    Author Comment

    Thank you FtB.  I have able to use the example and get it to work.
    LVL 46

    Expert Comment

    You are very welcome.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video discusses moving either the default database or any database to a new volume.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now