asked on

Datatype adNumeric

I have a complex set of nested loops parsing through some data sets that I then need to publish into a database.

In order to publish each iteration into the db, I use the following:

Set cmd = server.CreateObject("ADODB.command")
Set cmd.ActiveConnection = objConn
cmd.CommandText = "dbo.spx_CustWebPaySelection"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("@PAYSESSIONID", adInteger, adParamInput, , l_nSessCustWebPayID)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@DATAAREAID", adVarChar, adParamInput, 3, da_id)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@RECID", adInteger, adParamInput, , rec_id)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@PAYMENTAMT", adNumeric, adParamInput, , amtDue)
cmd.Parameters.Append prm

cmd.Execute

However, when I run this, I recieve the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
foo.asp, line 101

Line 101 is the cmd.execute. I believe I've narrowed it down to the adNumeric parameter, and some syntax problems therein, because if I move away from the ADO and do it the old way:

objSQL = "Exec spx_CustWebPaySelection " & l_nSessCustWebPayID & ", '" & da_id & "', " & rec_id & ", '" & amtDue & "'"
objConn.Execute(objSQL)

it works great.

So... The table has the particular column for the numeric set with the size as "13(28,12)"... Do I put that in as the size for my ADO, or is there something else missing altogether? (and yes, I have a connection established).

Thanks in advance.

nurbek

this error generally occurs

when you try to add bigger length then defined length of the fields

make sure that fields have enough size like

myField varchar(50)

Paul Kahl

ASKER

Thanks. The issue, after much research, turns out to be related to the data SIZE, but in that adNumeric has strange size parameters in SQL, and this syntax doesn't support inputting that syntax (ie. 13(28,12)). That said, I moved to an older form of the ADO syntax, and it worked perfectly:

Set newPrm = cmd.CreateParameter("@PAYMENTAMT")
newPrm.Type = adNumeric
newPrm.Direction = adParamInput
newPrm.Precision = 28
newPrm.Size = 12
cmd.Parameters.Append newPrm
newPrm.value = amtDue

As you can see, in this instance the "13" from the SQL size is not used, but instead the precision and secondary size are used.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul Kahl

ASKER

Fantastic! Shorter, sweeter, and to the point. I'll retract my previous request for removal and award points accordingly.

Thank you!

Anthony Perkins

>>Shorter, sweeter, and to the point. <<
Oh, you wanted shorter, than try it this way (you can lose the prm variable): <g>

Set cmd = server.CreateObject("ADODB.command")
Set cmd.ActiveConnection = objConn
cmd.CommandText = "dbo.spx_CustWebPaySelection"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@PAYSESSIONID", adInteger, adParamInput, 4, l_nSessCustWebPayID)
cmd.Parameters.Append cmd.CreateParameter("@DATAAREAID", adVarChar, adParamInput, 3, da_id)
cmd.Parameters.Append cmd.CreateParameter("@RECID", adInteger, adParamInput, 4, rec_id)
cmd.Parameters.Append cmd.CreateParameter("@PAYMENTAMT", adNumeric, adParamInput, 13, amtDue)
cmd.Parameters("@PAYMENTAMT").Precision = 28
cmd.Parameters("@PAYMENTAMT").NumericScale = 12
cmd.Execute ,, adExecuteNoRecords
' ...

Or if you are into With statements, try it this way:

Set cmd = server.CreateObject("ADODB.command")
With cmd
      Set .ActiveConnection = objConn
      .CommandText = "dbo.spx_CustWebPaySelection"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@PAYSESSIONID", adInteger, adParamInput, 4, l_nSessCustWebPayID)
      .Parameters.Append .CreateParameter("@DATAAREAID", adVarChar, adParamInput, 3, da_id)
      .Parameters.Append .CreateParameter("@RECID", adInteger, adParamInput, 4, rec_id)
      .Parameters.Append .CreateParameter("@PAYMENTAMT", adNumeric, adParamInput, 13, amtDue)
      With .Parameters("@PAYMENTAMT")
            .Precision = 28
            .NumericScale = 12
      End With
      .Execute ,, adExecuteNoRecords
End With
' ...

But in all seriousness, the most important part with stored procedures that do not return a resultset is setting the Execute option to adExecuteNoRecords

Thanks for the points.

Paul Kahl

ASKER

Thanks for the tip!