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.
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
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...
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?
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.