Passing a Numeric type from VB to a store procedure

[Marrigon]

Hi,
I have a SQL 7 server and I created a store procedure which insert a new record into a table.
This table has a field declared as numeric and the SP has a corresponding numeric variable.
From my VB application I have to call this SP; I tried with the ADODB.command way and CreateParameter method.
CreateParameter("myVar", adNumeric, adParamInput, 9, myValue)


When I call Execute I receive this error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Why? Where is the mistake?
The ADO version is 2.6 and my OS is Windows 2000.

Thanks
Marco

[Cimperiali]

which is the content of "myValue"? you sure it is a number?

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[Cakkie]

Well, this is the typcal 'find-it-out-yourself' error (Ok guys, I know, I said it before, I just like the expression). And this is exactly what we need to do. The error can be caused by a number of factors, but mostly, it is due to typemismatches, or incorrect field lengths. The latter only applies to strings or binary data, where the data is to large to fit the field. Another thing could be that the parameter you specify isn't defined in the SPROC.
To find the exact error, M$ supplie sus with a tool name SQL Profiler, which comes with SQL Server. Star the tool, and run a trace with the default settings. Then run your code. You will exactly see what is happening on your server. When the code fails, check your profiler and go the the last statement executed (should be your stored procedure). Copy/Paste the statement to Query Analyzer and run it. Now you should see the exact error that occured, with a decent description, which will probably lead you right to the solution of your problem.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Marrigon]

I tried to do what you suggested, but no help come from Profiler and Query Analyzer.
I tried to change the parameter type in adInteger and size to 9 (the size of the field on the SQL table) and it perfectly works if the the field is defined as DECIMAL (10,0).
Then I defined it DECIMAL (13, 2) even if I use adDouble as number type, in the SQL table there is an integer (i.e. 234,75 -> 235). If I use the adDecimal or adNumeric I get an error.
For sure the SPROC is correct, because I tried to use from connection.Execute command from VB and it works (I can't use this way for other reasons).
So, have you any idea about how to solve the problem of float values?

Marco

[Marrigon]

OK, now it works (more or less)
If you use adDecimal or adNumeric after the CreateParameter you must set the precision and numericScale properties of the ADODB.Parameter and then Append it.
In any case I'm still receiving and Integer!
Looking at the parameter proprerties the value is correct (123.75) but when I open my SQL table there is 124! I tried to insert it by hand, just to see if there were declaration mistakes, but it accept it without converting it into an integer.
If you have any idea ...

[Cakkie]

What type is the parameter in the SPROC, if it is an integer, the decimal is converted autmatically, resulting in 124

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Marrigon]

Yes, the mistake was the declaration in the SPROC.
It has been declared as Nuemric but I didn't specified precision and scale. Now it correcly works.

Thanks for your help.
Marco