October 16, 2008 at 9:50 am
I am using ADO in VB6 to call a stored procedure in SQL2005. The stored procedure is looking for data as "decimal(6,5)". My data is a double in VB6. In VB6, I tried setting up the call as follows (This is right out of an ADO book):
SQLCommand.Parameters.Append .CreateParameter("@Offset", adDecimal, adParamInput, , dMyDoubleValue)
SQLCommand.Parameters("@Offset").Precision = 6
SQLCommand.Parameters("@Offset").NumericScale = 5
After doing a trace, the number I passed -0.03125 is converted in the trace to -312.
In another forum, they told me that this is because NumericScale and Precision are enumerated types correspond to adDouble and adCurrency.
I don't know which is correct. The way ADO converted the data it sent to SQL, I'd assume that the enumerated explanation is correct. If that's true, how can I get it to be sent to SQL correctly?
October 16, 2008 at 11:20 am
Welcome to the forums - and to the 21st century. What's up with using VB6? Perhaps a programming language from this decade would be a better choice.
First, you set the precision of a parameter after putting data into it. That's not going to work because your data would already have been messed with.
So, you could add an empty parameter or zero, change the precision and scale, and then set the parameter value. You could also call (I think the command is correct) Parameters.Refresh to get the parameter definitions rather than creating parameters - remember that you have to call Parameters.Create in the correct order and need to create the return value parameter first (and it can only be an integer).
October 17, 2008 at 12:17 am
SSCrazy,
The VB6 I had no choice about. It is legacy software that the company wanted to update from Access to SQL. Anyway, I tried the Refresh command as you suggested. It did provide me with some interesting results. I was doing the Precision and NumericScale correctly, but the interesting part was that instead of the adDecimal type I expected, it returned adNumeric. It appears that the Refresh command created and populated all the parameters, so all I did is to assign the .Value. Unfortunately, I ended up with the same result.
Set SQLCommand = Nothing
With SQLCommand
.CommandType = adCmdStoredProc
.CommandText = "ins_upd_SerialNumLkUp"
.NamedParameters = True
Set .ActiveConnection = SQLConnection
.Parameters.Refresh
.Parameters("@Offset").Value = dMyDoubleValue
.execute
End With
After doing a trace, the number I passed -0.03125 is converted in the trace to -312.
So no dice...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply