• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1223
  • Last Modified:

Calling a stored procedure from ASP

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")))
      strSSN=Trim(Cstr(Request.Form("SSN")))
      strFirst_Name=Request.Form(Trim(Cstr("FName")))
      strLast_Name=Request.Form(Trim(Cstr("LName")))
      strStreet=Request.Form(Trim(Cstr("Street")))
      strState=Request.Form(Trim(Cstr("State")))
      strCity=Request.Form(Trim(Cstr("City")))
      strState=Request.Form(Trim(Cstr("State")))
      strZip=Request.Form(Trim(Cstr("Zip")))
      strDate_Of_Birth=(CDATE(Request.Form("DOB")))
      strSex=Request.Form(Trim(Cstr("Sex")))
      
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.

Lalitha
0
Lalitha_Ramalingam
Asked:
Lalitha_Ramalingam
  • 5
  • 3
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
It would be useful to know what error you are getting.
0
 
Lalitha_RamalingamAuthor Commented:
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.

Lalitha
0
 
fritz_the_blankCommented:
A few things:

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


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

FtB



0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
fritz_the_blankCommented:
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")


0
 
fritz_the_blankCommented:
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%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
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
%>
</HEAD>
<BODY>
<%
Response.Write("<Table Border=1 cellpadding=2 cellspacing=2>")
Do while not  objRS.EOF
      Response.Write("<TR>")
      for i=0 to objRS.Fields.count-1
            Response.Write("<TD>" & objRS(i).Value & "</TD>")
      next
      Response.Write("</TR>")
      objRS.MoveNext
Loop
Response.Write("</Table>")
Response.write("Number of records returned: " & objRS.RecordCount)
%>
</BODY>
<%
set objCommand = Nothing
objRS.Close
Set objRS = Nothing
objConnection.Close
set objConnection = Nothing
%>

</HTML>
0
 
Lalitha_RamalingamAuthor Commented:
Thank you so much ftb.  I will try out the code you sent me and let you know. Thank you again
0
 
fritz_the_blankCommented:
Okay and good luck.

FtB
0
 
Lalitha_RamalingamAuthor Commented:
Thank you FtB.  I have able to use the example and get it to work.
Lalitha
0
 
fritz_the_blankCommented:
You are very welcome.

FtB
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now