none
SqlDataAdapter UpdateCommand fails because of decimal comma for float column parameter RRS feed

  • Question

  • Hello,

     

    does anyone know how to avoid the following scenario?:

     

    I have a simple Stored Procedure to update Quote-lines:

     

    Code Snippet

    PROCEDURE UpdateQuoteLine

    @QuoteId int

    , @LineNo int

    , @ItemId int

    , @Alternative bit

    , @Qty int

    , @Price float

    , @Margin float

    , @Modified datetime OUTPUT

    , @ModifiedBy int

    AS

    DECLARE @NewModified DateTime

    SET @NewModified = getdate()

    UPDATE QuoteLine

    SET ItemId = @ItemId

    , Alternative = @Alternative

    , Qty = @Qty

    , Price = @Price

    , Margin = @Margin

    , Modified = @NewModified

    , ModifiedBy = @ModifiedBy

    WHERE QuoteId = @QuoteId AND LineNo = @LineNo

    AND Modified = @Modified

    SET @Modified = @NewModified

    GO

     

     

    Now, my SqlDataAdapter (in my C# program) maps the Columns of the DataSet-table to the parameters and generates the following statements (intercepted via SQL-Server Profiler):

     

    Code Snippet

    declare @p8 datetime

    set @p8='2008-03-13 15:01:33:343'

    exec dbo.UpdateQuoteLine

    @QuoteId=211,

    @LineNo=30,

    @ItemId=10029,

    @Alternative=0,

    @Qty=1,

    @Price=1172,25,

    @Margin=67,98,

    @Modified=@p8 output,

    @ModifiedBy=123456

    select @p8

     

     

    But this makes SQL-Server stumble over de comma's in the values for @Price . . :

    Msg 119: Must pass parameter number 7 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

    apparently, it doesn't interpret 1172,25 as the value for @Price, but thinks that '25' is meant as a new parameter!!

     

    So - Why does Visual C# not communicate with SQL Server for float columns!?

    And - is there any way to get them on speaking terms again - also for fractions?

    Thursday, March 13, 2008 3:46 PM

Answers

  • How do you create parameters? Do you concatenate their values to the string and built that SQL string dynamically in your code? If yes, then that is the source of the problem. All the values for the parameters should be passed to SQL Server using culture-independant format. If you create parameters in your code using SqlParameter class, then ADO.NET will take care of this issue, but if you just build dynamic connection string then you will have multiple issues with dates, numbers and some special characters.

     

     

    Monday, March 17, 2008 10:02 AM
    Moderator

All replies

  • How is SQL server to know you mean "1172,25" or that you mean "1172" and messed up the next parameter? Easy solution is to use '.' to specify decimal values. SQL server doesn't understand culture specific decimal separators. Now it should understand culture specific string to decimal conversions, so so writing it as:

     

    @Price = "1172,25"

     

    should work if you have your culture set correctly in your program.

     

     

    Thursday, March 13, 2008 4:10 PM
  • OK - so setting Culture would make the .NET program communicate differently with SQL-server?

    Where do you think I can set that culture? In the connection-string? Or is it a property of the adapter?

     

    I think this is a bug in the ADO.NET 'driver', because it indeed makes no sense to use a comma as the parameter delimiter and the decimal symbol. Shouldn't this be 'language-independant'?

    But - it seems to be even worse: sometimes, the float-values are accepted and sometimes they are not (throwing a concurrency exception . . .)

     

    Thursday, March 13, 2008 8:15 PM
  • How do you create parameters? Do you concatenate their values to the string and built that SQL string dynamically in your code? If yes, then that is the source of the problem. All the values for the parameters should be passed to SQL Server using culture-independant format. If you create parameters in your code using SqlParameter class, then ADO.NET will take care of this issue, but if you just build dynamic connection string then you will have multiple issues with dates, numbers and some special characters.

     

     

    Monday, March 17, 2008 10:02 AM
    Moderator