Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO.NET decimal parameter

Posted on 2004-12-01
9
Medium Priority
?
750 Views
Last Modified: 2008-01-09
When i update my table i get some annoying rounding.
The SQL table has been set with a scale of 2 (ie 2 to the right of decimal point) If i set it manually in the DB it stays as expected.

Eg. 81.5 stays as 81.5

but when i try and update from my app it gets rounded... 81.5 becomes 82.

...in the dataset its held as i set it via the bound controls. I confirmed this by msgboxing it out..
                                       MsgBox(dsprod.Tables("EDUspecific").Rows(0)("PaymentForefit").ToString)

So the problem lies with the parameters. At first i used the line below.
daProd.UpdateCommand.Parameters.Add(New SqlParameter("@PaymentForefit", SqlDbType.Decimal)).SourceColumn = "PaymentForefit"

Then i tried this other idea which incorporates the scale (set to 2). Still does work.
daProd.UpdateCommand.Parameters.Add(New SqlParameter("@PaymentForefit", SqlDbType.Decimal, _
                                                            9, ParameterDirection.Input, False, 18, 4, "PaymentForefit", _
                                                            DataRowVersion.Current, Nothing))

I printed out @PaymentForefit from the storedprocedure... by then it has been rounded so its 82.

Ideas?!
0
Comment
Question by:DSE
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12714677
hi DSE,
could you try this

        Dim x As New SqlClient.SqlParameter("@PaymentForefit", SqlDbType.Decimal)
       
        command.Parameters("@out_arg").Direction = ParameterDirection.input
        command.Parameters("@out_arg").Precision = 10
        command.Parameters("@out_arg").Scale = 2
     daProd.UpdateCommand.Parameters.Add(x)
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12714688
sorry

        Dim x As New SqlClient.SqlParameter("@PaymentForefit", SqlDbType.Decimal)
       
        x.Direction = ParameterDirection.input
        x.Precision = 10
        x.Scale = 2
       daProd.UpdateCommand.Parameters.Add(x)
0
 

Author Comment

by:DSE
ID: 12714740
Hi Ronald,

I will try that...

BBIAB
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DSE
ID: 12714974
alas that hasnt worked.

Is there any specialties i need consider when declaring the parameter in the stored procedure?

currently...

@PaymentForefit as decimal

Regards,
DSE
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12715052
Strange, I had the same problem, although for an output parameter, and that solved it.
I will try to recreate your problem and I'll get back to you.

0
 

Author Comment

by:DSE
ID: 12715146
Thanks for your persistance...
...........................................

CREATE PROCEDURE [dbo].[UpdateEDUspecific]

@PaymentForefit as decimal =null

 AS

print @paymentforefit

.... Its this print that shows me that its been rounded
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 1000 total points
ID: 12715171
Hi DSE,

I tried this and this worked,

in my stored procedure i defined my decimal like this

@testje decimal(18,2),

and my parameter looked like this

daProd.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@testje", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(2, Byte), "testje", System.Data.DataRowVersion.Current, Nothing))
0
 

Author Comment

by:DSE
ID: 12715212
Hi Ronald!...

i was just about to post my findings of the same!

Thanks for your help.

The points are yours!

Regards

DSE
0
 

Author Comment

by:DSE
ID: 12715244
For future readers id like to add that ive found that you dont need to specify precision and scale in the client side parameter declaration.
Aslong as the receiving procedure's parameter declaration has precision and scale defined it works fine!

DSE
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

564 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