Copy link to clipboard
Copied
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.
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.
Copy link to clipboard
Copied
Best to show us the entire code from both pages. Also explain what you mean by 'doesn't work'.
Copy link to clipboard
Copied
Here is default.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/Expense.asp" -->
<%
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
%>
Copy link to clipboard
Copied
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"%>
<!--#include file="Connections/Expense.asp" -->
<%
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>
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
It is a numeric field. What do I use for the datatypes?
Copy link to clipboard
Copied
Can you show us the stored procedure, at least the top where the parameters are defined?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Dreamweaver made the parameter. I will take it out.
Copy link to clipboard
Copied
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!
Copy link to clipboard
Copied
Excellent!
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Get ready! An upgraded Adobe Community experience is coming in January.
Learn more