• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5436
  • Last Modified:

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.
0
Paul Kahl
Asked:
Paul Kahl
  • 3
  • 2
1 Solution
 
nurbekCommented:
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)



0
 
Paul KahlAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
For the record and since this question is to be PAQ'd:
Numeric (aka decimal) data types always need the Precision and NumericScale properties set in the parameter object.  Here is the correct way to do it:

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, 4, 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, 4, rec_id)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@PAYMENTAMT", adNumeric, adParamInput, 13, amtDue)
prm.Precision = 28
prm.NumericScale = 12
cmd.Parameters.Append prm

cmd.Execute ,, adExecuteNoRecords
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Thank you!
0
 
Anthony PerkinsCommented:
>>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.
0
 
Paul KahlAuthor Commented:
Thanks for the tip!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now