I can't insert values into a table using a stored procedure because the fields are of data type MONEY.
First, is the data type CURRENCY the same as data type MONEY?
Right now, I am currently using the integer value of adCurrency for the data type parameter of the createparameter function. This integer value is 6.
I don't think that converting the values to strict currency values before sending them will matter.
1 - because when this code is written with inline SQL, there isn't a problem putting the values into the MONEY fields.
2 - because I tried explicitly converting the values before I sent them to the db using the cCur() function and it made no difference in the error that I was getting.
The actual error I am getting is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to money.
Also, I have tried sending the money parameters as varchar(10) to the stored procedure and the using the SQL CONVERT function to attempt to convert these values to a money data type. I get a similar error when I do that. The error for that is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot convert a char value to money. The char value has incorrect syntax.
Any input or ideas would be much appreciated.
the call to the stored procedure is here:
set sp = new storedProcedure
call sp.setup(cnBusinessApps, "sp_tblTravelApproval_AddRow")
call sp.addParam("@RequesterID", 3, 1, , request("RequesterID"))
call sp.addParam("@RequestedBy", 3, 1, , Session("EmployeeID"))
call sp.addParam("@SupervisorID", 3, 1, , request("SupervisorID"))
call sp.addParam("@BeginDate", 135, 1, , BDate)
call sp.addParam("@EndDate", 135, 1, , EDate)
call sp.addParam("@Location", 200, 1, 150, request("Location"))
call sp.addParam("@EstRegFee", 6, 1, , request("RegFees"))
call sp.addParam("@EstTransportation", 6, 1, , request("Airfare"))
call sp.addParam("@EstMileage", 6, 1, , request("Mileage"))
call sp.addParam("@EstLodging", 6, 1, , request("Lodging"))
call sp.addParam("@EstMeals", 6, 1, , request("Meals"))
call sp.addParam("@TotalECost", 6, 1, , request("TotalECost"))
call sp.addParam("@AdditionalComments", 200, 1, 500, request("AddComments"))
set rs = sp.callStoredProcedure
set sp = nothing
The Class that I use in the above statement is here:
Class storedProcedure
private cmd
'Constructor
Private Sub Class_Initialize
set cmd = server.CreateObject("ADODB.Command")
End sub
'Destructor
Private Sub Class_Terminate
End sub
'Methods
'
'This Class only works when the Methods are called In-Order
'Therefore, setup() must be called before addParam and callStoredProcedure
'
'------------------
Public sub setup(databaseConnection, storedProcedureName)
'Note: The Connection must already be made and open to the desired database
cmd.ActiveConnection = databaseConnection
cmd.CommandText = storedProcedureName
cmd.CommandType = 4
End sub
Public sub addParam(variableNameStr, variableType, inputOrOutput, sz, variableValue)
cmd.Parameters.Append = cmd.CreateParameter(variableNameStr, variableType, inputOrOutput, sz, variableValue)
End sub
Public property Get callStoredProcedure
set callStoredProcedure = cmd.Execute
End property
End Class
The stored Procedure is here:
ALTER PROCEDURE sp_tblTravelApproval_AddRow
@RequesterID int = null,
@RequestedBy int = null,
@SupervisorID int = null,
@BeginDate datetime = null,
@EndDate datetime = null,
@Location money = null,
@EstRegFee money = null,
@EstTransportation money = null,
@EstMileage money = null,
@EstLodging money = null,
@EstMeals money = null,
@EstOther money = null,
@TotalECost money = null,
@AdditionalComments varchar(500) = null
AS
BEGIN
INSERT INTO tblTravelApproval
(
RequesterID,
RequestedBy,
SupervisorID,
BeginDate,
EndDate,
Location,
EstRegFee,
EstTransportation,
EstMileage,
EstLodging,
EstMeals,
EstOther,
TotalECost,
AdditionalComments
)
VALUES
(
@RequesterID,
@RequestedBy,
@SupervisorID,
@BeginDate,
@EndDate,
@Location,
@EstRegFee,
@EstTransportation,
@EstMileage,
@EstLodging,
@EstMeals,
@EstOther,
@TotalECost,
@AdditionalComments
)
END
GO