I am trying to pass a decimal datatype to a stroed proc using DMX's command interface. I set the particular firld to decimal with a size of 9.
<pre id=code><font face=courier size=2 id=code>
cmd.Parameters.Append cmd.CreateParameter("@mydecimal", 14, 1,9,cmd__mydecimal)
</font id=code></pre id=code>

It returns this error when run
<pre id=code><font face=courier size=2 id=code>
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
</font id=code></pre id=code>

I a pretty sute that my problem is that I have not set a scale and precision for the decimal datatype.

How do I set these via DMX? Or can someone help me with the handcoding?

Are the datatype settings the same size in the db and your command?

Digga

Sharing Knowledge Saves Valuable Time!!!

Yes the column is decimal 9(10,2).

DMX's Command interface only lets you set the size - not precision and scale.

The stored proc works fine with decimals when run from query analyzer.

BTW, its MS SQL 2000.

Hi

My understanding of this error is that you are trying to send more data than the datatype specified will allow. Have you verified that the value populating the @mydecimal variable isn't longer than the datatype restrictions in your sql field.

Another thought, you say the qa call to the sp works fine when you supply the data, would you need to change the sp's @mydecimal datatype using cast or convert to the datatype of decimal.

You could do a isNumeric check in the asp page (that's if it's asp) to see if the value supplied to the variable is ok to use in the sp and maybe use a CDbl to convert before being sent to the sp.

Let me know how you get on.

Digga

Sharing Knowledge Saves Valuable Time!!!

MSSQL's default for decimal is (10,0). I changed it to (10,2) because the data being passed is 1, 1.5, 2, 2.5, 3, 3.5, etc up to 9.5. (Not sure if thats the best choice, but...)

The error is recieved when any number is sent. There is validation on the page (isNumeric).

In QA it works fine without CAST. spMyproc 1, spMyproc 3.5, etc.

I think the command obj is expecting the parameter's precision and scale to be passed - but it is not being passed because there is not way to do that with DMX.

As I have read there are Precision and Scale properties to the Command object. I am assuming if I can set these to 10,2 my problem will be resolved.