Calling Stored Procedure Fail

Report · Aug 26, 2011

I am sure that this is probably an easy answer but please bear with me.

I have a page

defaulredirect.asp

it is receiving form variables from default.asp'

I need to use those form variables in my stored procedure. My procedure works fine in SQL but when I try to add it to my page as a command it doesn't work.

I am not sure what I am doing wrong. I also don't know for sure what details you need. Please be patient with me.

The procedure inserts the form variables into the table ans returns the newly created ID (identity) for further use in my application.

Report · Aug 26, 2011

Best to show us the entire code from both pages. Also explain what you mean by 'doesn't work'.

Report · Aug 29, 2011

Here is default.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
Dim rsEmp
Dim rsEmp_cmd
Dim rsEmp_numRows

Set rsEmp_cmd = Server.CreateObject ("ADODB.Command")
rsEmp_cmd.ActiveConnection = MM_Expense_STRING
rsEmp_cmd.CommandText = "SELECT * FROM dbo.tblEmployee ORDER BY EmpName ASC"
rsEmp_cmd.Prepared = true

Set rsEmp = rsEmp_cmd.Execute
rsEmp_numRows = 0
%>
<%
Dim rsSupervisor__MMColParam
rsSupervisor__MMColParam = "1"
If (Request("MM_EmptyValue") <> "") Then
rsSupervisor__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim rsSupervisor
Dim rsSupervisor_cmd
Dim rsSupervisor_numRows

Set rsSupervisor_cmd = Server.CreateObject ("ADODB.Command")
rsSupervisor_cmd.ActiveConnection = MM_Expense_STRING
rsSupervisor_cmd.CommandText = "SELECT * FROM dbo.tblEmployee WHERE empRole = ? ORDER BY EmpName ASC"
rsSupervisor_cmd.Prepared = true
rsSupervisor_cmd.Parameters.Append rsSupervisor_cmd.CreateParameter("param1", 5, 1, -1, rsSupervisor__MMColParam) ' adDouble

Set rsSupervisor = rsSupervisor_cmd.Execute
rsSupervisor_numRows = 0
%>
<%
Dim rsExpenseType
Dim rsExpenseType_cmd
Dim rsExpenseType_numRows

Set rsExpenseType_cmd = Server.CreateObject ("ADODB.Command")
rsExpenseType_cmd.ActiveConnection = MM_Expense_STRING
rsExpenseType_cmd.CommandText = "SELECT * FROM dbo.tblExpenseType ORDER BY expenseType ASC"
rsExpenseType_cmd.Prepared = true

Set rsExpenseType = rsExpenseType_cmd.Execute
rsExpenseType_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Expense Form</title>

</head>
<body>
<p> </p>

<form id="frmExp" name="frmExp" method="post" action="defaultredirect.asp">
<p>

</p>
<table width="100%" border="0" align="center" id="tblExpFrm">
    <tr align="center">
      <th colspan="2" scope="col">Expense Form</th>
    </tr>
    <tr>
      <td align="left"><label for="txtExpEmpName">Name:</label></td>
      <td><input name="txtExpEmpName" type="text" id="txtExpEmpName" size="32" /></td>
    </tr>
    <tr>
      <td align="left"><label for="txtExpEmpSuper">Supervisor:</label></td>
      <td><input name="txtExpEmpSuper" type="text" id="txtExpEmpSuper" size="32" /></td>
    </tr>
    <tr>
      <td align="left"><label for="txtExpDate"><br />
    Date of Expense:</label></td>
      <td><input name="txtExpDate" type="text" id="txtExpDate" size="10" /></td>
    </tr>
    <tr>
      <td align="left"><label for="txtExpAmount">
    Amount:</label></td>
      <td><input name="txtExpAmount" type="text" id="txtExpAmount" size="10" /></td>
    </tr>
    <tr>
      <td align="left"><label for="txtExpPurpose">
    Purpose:</label></td>
      <td><textarea name="txtExpPurpose" cols="50" rows="5" id="txtExpPurpose"></textarea></td>
    </tr>
    <tr>
      <td align="left">Expense Type:</td>
      <td> <table width="200" id="tblExpType">
    <tr>
      <td><label>
        <input type="radio" name="RdGrpExpenseType" value="2" id="RdGrpExpenseType_0" />
        Cash</label></td>
    </tr>
    <tr>
      <td><label>
        <input type="radio" name="RdGrpExpenseType" value="4" id="RdGrpExpenseType_1" />
        Credit Card</label></td>
    </tr>
    <tr>
      <td><label>
        <input type="radio" name="RdGrpExpenseType" value="1" id="RdGrpExpenseType_2" />
        Meal</label></td>
    </tr>
    <tr>
      <td><label>
        <input type="radio" name="RdGrpExpenseType" value="3" id="RdGrpExpenseType_3" />
        Mileage</label></td>
    </tr>
</table></td>
    </tr>
    <tr>
      <td align="left"> </td>
      <td><input type="submit" name="btnExpfrm" id="btnExpfrm" value="Submit" /></td>
    </tr>
</table>
</form>
</body>
</html>
<%
rsEmp.Close()
Set rsEmp = Nothing
%>
<%
rsSupervisor.Close()
Set rsSupervisor = Nothing
%>
<%
rsExpenseType.Close()
Set rsExpenseType = Nothing
%>

Report · Aug 29, 2011

The page (defaultredirect.asp) will show the aproppriate form responses. BUT when I add in the stored procedure it get an error. I will recreated the error....

ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

/Expense/defaultredirect.asp, line 44

Here is the entire code for that page:

-----------------------------------------------------------------------------------

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%

Dim cmdInsertRecord__txtExpEmpName
cmdInsertRecord__txtExpEmpName = ""
if(Request("txtExpEmpName") <> "") then cmdInsertRecord__txtExpEmpName = Request("txtExpEmpName")

Dim cmdInsertRecord__txtExpEmpSuper
cmdInsertRecord__txtExpEmpSuper = ""
if(Request("txtExpEmpSuper") <> "") then cmdInsertRecord__txtExpEmpSuper = Request("txtExpEmpSuper")

Dim cmdInsertRecord__txtExpDate
cmdInsertRecord__txtExpDate = ""
if(Request("txtExpDate") <> "") then cmdInsertRecord__txtExpDate = Request("txtExpDate")

Dim cmdInsertRecord__RdGrpExpenseType
cmdInsertRecord__RdGrpExpenseType = ""
if(Request("RdGrpExpenseType") <> "") then cmdInsertRecord__RdGrpExpenseType = Request("RdGrpExpenseType")

Dim cmdInsertRecord__txtExpPurpose
cmdInsertRecord__txtExpPurpose = ""
if(Request("txtExpPurpose") <> "") then cmdInsertRecord__txtExpPurpose = Request("txtExpPurpose")

Dim cmdInsertRecord__txtExpAmount
cmdInsertRecord__txtExpAmount = ""
if(Request("txtExpAmount") <> "") then cmdInsertRecord__txtExpAmount = Request("txtExpAmount")

%>
<%

set cmdInsertRecord = Server.CreateObject("ADODB.Command")
cmdInsertRecord.ActiveConnection = MM_Expense_STRING
cmdInsertRecord.CommandText = "dbo.InsertExpense"
cmdInsertRecord.CommandType = 4
cmdInsertRecord.CommandTimeout = 0
cmdInsertRecord.Prepared = true
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@RETURN_VALUE", 3, 4)
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@txtExpEmpName", 3, 1,-1,cmdInsertRecord__txtExpEmpName)
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@txtExpEmpSuper", 3, 1,-1,cmdInsertRecord__txtExpEmpSuper)
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@txtExpDate", 135, 1,-1,cmdInsertRecord__txtExpDate)
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@RdGrpExpenseType", 3, 1,-1,cmdInsertRecord__RdGrpExpenseType)
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@txtExpPurpose", 200, 1,50,cmdInsertRecord__txtExpPurpose)
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@txtExpAmount", 6, 1,-1,cmdInsertRecord__txtExpAmount)
cmdInsertRecord.Parameters.Append cmdInsertRecord.CreateParameter("@Identity", 3, 2)
cmdInsertRecord.Execute()

%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
yeah
<pre> </pre>
<p><%= Request.Form("txtExpEmpName") %> -emp</p>
<p><%= Request.Form("txtExpEmpSuper") %>-super</p>
<p><%= Request.Form("RdGrpExpenseType") %> - Exp type</p>
<p><%= Request.Form("txtExpDate") %></p>
<p><%= Request.Form("txtExpAmount") %> <%= Request.Form("txtExpPurpose") %> </p>
<p> </p>
<p> </p>
</body>
</html>

Report · Aug 29, 2011

ok, now I get this:

ADODB.Command error '800a0bb9'

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

/Expense/defaultredirect.asp, line 32

IDK if this will help but when I try to open the command under the bindings column, it tells me that it can't find my procedure in my database.

Report · Aug 29, 2011

Datatypes do appear to be set incorrectly. For example:

cmdInsertRecord.CreateParameter("@txtExpEmpName", 3, 1,-1,cmdInsertRecord__txtExpEmpName)

Here you are defining an input variable named txtExpEmpName, with a datatype of 4-bit signed integer and size = -1.

Is this a text or numeric field? If this is a text field, you need to give a text datatype and specify the size.

Report · Aug 29, 2011

It is a numeric field. What do I use for the datatypes?

Report · Aug 29, 2011

Can you show us the stored procedure, at least the top where the parameters are defined?

Report · Aug 29, 2011

This is what I used to create it.

USE [Expense]
GO

/****** Object: StoredProcedure [dbo].[InsertExpense] Script Date: 08/29/2011 13:22:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertExpense]
@txtExpEmpName int,
@txtExpEmpSuper int,
@txtExpDate datetime,
@RdGrpExpenseType int,
@txtExpPurpose varchar(50),
@txtExpAmount int,
@Identity int OUT
AS
INSERT INTO [Expense].[dbo].[tblExpense]
           ([ExpEmpName]
           ,[ExpEmpSuper]
           ,[ExpDate]
           ,[ExpTypeID]
           ,[ExpPurpose]
           ,[ExpAmount])
     VALUES
           (@txtExpEmpName
           ,@txtExpEmpSuper
           ,@txtExpDate
           ,@RdGrpExpenseType
           ,@txtExpPurpose
           ,@txtExpAmount)

SET @Identity = SCOPE_IDENTITY()

GO

Report · Aug 29, 2011

Your sproc has 7 parameters, yet you have created 8 in your asp code? What is "cmdInsertRecord.CreateParameter("@RETURN_VALUE", 3, 4)" ? I don't see that in your sproc. The parameters, including the order you create them, must match exactly. I've never used a return value type so I'm not sure how it is used. I'll research that but in the mean time, try commenting that out and see if it runs.

Report · Aug 29, 2011

Dreamweaver made the parameter. I will take it out.

Report · Aug 29, 2011

I also figured out that I am not returning a recordset apparently. I am so very new to all this. You are truly a rockstar!

Report · Aug 29, 2011

Excellent!

Report · Aug 31, 2011

ONe more question, since it relates to this, I need to show what the @idenity is. rather, at least i need to send it to the next page and beyond. Any idea how to do that?

Report · Sep 01, 2011

I think you would need to define a recordset and modify your execute statement to assign it to the RS.

Dim objRS As ADODB.Recordset

......

your existing code

......

SET objRS = cmdInsertRecord.Execute()

You should then be able to reference the return value in the recordset. There are probably other ways. Investigate using the @@identity feature of MS SQL server.

Report · Aug 29, 2011

IDK if this helps, if I store a default value inthe command in dreamweaver, it runs fine.. well except it obviuosly doesn't change the data. It just uses what is stored as a default.

Calling Stored Procedure Fail

1 Correct answer