Calling Stored Procedure Fail

Forum|Forum|14 years ago
August 26, 2011
1 reply
1843 views

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.

Server side applications

This topic has been closed for replies.

Correct answer bregent

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

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.

B

bregent

Participating Frequently

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

E

er1ca2000Author

Participating Frequently

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded