Varchar to Money Conversion

[stevenfagan]

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

[stevenfagan]

ignore the @location parameter in the above post. that was a typo. That is not of type MONEY. that is defined to be varchar(150) in the asp call and in the stored procedure.

[ArtistScope]

I almost never limit the data type by setting it to anything but text... because it saves lots of time trying to debug little issues like the one you have here. Sure, defining data types my be a norm, but after writing some applications in pure ASP that required 6,000 lines of code for some functions, I found it a great time saver avoiding the possibility of errors rather than trying to find them later.

For calculations I do them in the scripts on the page as data is retrieved, and that way when I export from Access to SQL I don't have to worry about losing stored procedures and formulas. And to ensure that I am working with a number, simply...

strData*1

[stevenfagan]

On that note how do you submit something in just plain text? The way I have submitted parameters as text so far is to just put the integer equivalent for adVarchar in for the data type and use len(variableValue) for the length of the parameter. Is this the conventional way to submit a text parameter?

[ArtistScope]

First you may need to change the properties of the fields in the database to nvarchar(n) where n is the length. Then when sending to the database from your page script, no special properties are needed.