Stored Procedure with Money Value

Question

Post reply

Stored Procedure with Money Value

TheProof

SSC Enthusiast

Points: 180
More actions
August 16, 2020 at 3:08 am

#3780756

hi everyone,
I've a "problem" with a Stored Procedure and .net application because two values (money) are managed as double and this generate a mismatch with currency (should be 187,50 but is 18750). In SQL, I created a User Table and SP:
CREATE TYPE tbl_FIC_InvoiceGuest AS TABLE
(
Invoice_Token nvarchar(50) NULL,
Invoice_Total money NULL,
)
--SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spInsertInvoiceGuest
@tblFIC tbl_FIC_InvoiceGuest READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Financial_FIC_Invoice (Invoice_Token, Invoice_Total)
SELECT Invoice_Token, CAST(Invoice_Total AS money) as Invoice_Total FROM @tblFIC
END
in .Net I'm using this code
dtv2.Columns.AddRange(New DataColumn(2) {New DataColumn("Invoice_Token", GetType(String)), New DataColumn("Invoice_Total", GetType(Decimal))})
For Each row As GridViewRow In panel_fattureic_ord_link_GridView.Rows
Dim Invoice_TokenAs String = row.Cells(0).Text
Dim Invoice_Total As Decimal = Decimal.Parse(row.Cells(1).Text, NumberStyles.Currency)
The Financial_FIC_Invoice table is set in right mode (money) so I don't understand why and how to fix it.
Any clue?
thanks
July 14, 2022 at 3:59 pm

This was removed by the editor as SPAM
July 22, 2022 at 10:53 am

This was removed by the editor as SPAM
February 15, 2024 at 4:29 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110206 More actions · Answer 1

Write Invoice_Total to console right before calling the procedure.

What's the value you can see?

_____________
Code for TallyGenerator

TheProof SSC Enthusiast Points: 180 More actions · Answer 2

From the API source I see a value like this 220.20 but I tried to "clean" into the gridview with my currency (220,20) without result. I also tried to replace the dot with comma via .net but is not possible.

Sergiy SSC Guru Points: 110206 More actions · Answer 3

OK, I was not asking how the value looks in the grid view.

You're passing to the procedure the value Invoice_Total as a decimal value - is it right?

Formatting is irrelevant here.

What is that decimal value? Can you check?

_____________
Code for TallyGenerator

TheProof SSC Enthusiast Points: 180 More actions · Answer 4

I'm passing the decimal because I cannot set money value from .net. into SP there's CAST to convert value to money but probably the point is that the source is not converted.

I cannot find a solution :/

Sergiy SSC Guru Points: 110206 More actions · Answer 5

Decimal should be fine. As long if it’s really decimal.

can you output that decimal value to console right before calling the SP?

_____________
Code for TallyGenerator

TheProof SSC Enthusiast Points: 180 More actions · Answer 6

in the gridview is 225.00 but into the variable is 22500. Is like the string: Dim Invoice As Decimal = Decimal.Parse(row.Cells(4).Text, NumberStyles.Currency) doesn't works to keep the original value....

Sergiy SSC Guru Points: 110206 More actions · Answer 7

So, the issue is actually in .Net code.

Nothing to do with an SP or any other part of SQL Server.

You mentioned something about using commas instead of dots. Comma in Currency format is a thousands separator, not a decimal point.

Could it be the source of the problem?

_____________
Code for TallyGenerator

TheProof SSC Enthusiast Points: 180 More actions · Answer 8

Correct...is what I done yesterday to fix the problem and it works.

Thanks