?
Solved

Datatype adNumeric

Posted on 2005-02-28
7
Medium Priority
?
5,205 Views
Last Modified: 2012-05-05
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
Comment
Question by:Paul Kahl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 13

Expert Comment

by:nurbek
ID: 13422556
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
 
LVL 3

Author Comment

by:Paul Kahl
ID: 13422992
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13434731
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Author Comment

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

Thank you!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13435879
>>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
 
LVL 3

Author Comment

by:Paul Kahl
ID: 13440895
Thanks for the tip!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question