CODEWITHSUNDEEP
c#sqlparsing
Jake Sankey
Jake Sankey

Reputation: 5137

SQL decimal conversion error

Hey, my app parses numerous txt files in a directory that are almost all formatted identically, then inserts the values into columns of my sql db. However, there are a few files that the app has come across where a value is 'blank' instead of '0.0', so the application fails stating that it cannot convert that value to numeric.

Is there a way I could change the following code to say what says AND also add 'IF value is blank, then import 0.0 instead?

EDIT: The following code seems to get my pass my error, however now I have all columns showing as numbers. if it is text, it inputs "0" in its place...

string[] values = line.Split(new[] { ',' });

                                for (int i = 0; i < values.Length - 1; i++)
                                {

                                    SqlParameter param = insertCommand.Parameters[i];

                                    decimal value;
                                    if (values[i] == null || values[i] == "")
                                    {
                                        value = 0.000m;
                                    }
                                    else
                                    {
                                        if (!decimal.TryParse(values[i], out value))
                                        {

                                           // param.Value = decimal.TryParse(values[i], out value) ? value : 0;
                                            param.Value = values[i];
                                        }

                                    }
                                    param.Value = value;
                                }

Upvotes: 0

Views: 2337

Answers (6)

Steven Sudit
Steven Sudit

Reputation: 19620

Doesn't decimal.TryParse return false when executed on an empty string?

edit

Here's the offending line:

param.Value = values[i];

The right hand side should be value, not values[i].

edit

The problem is that it executes param.Value = value; regardless. In other words, when it's not a number, you correctly assign values[i] to parm.Value, and then incorrect overwrite it with value, which is always 0 at that point.

Upvotes: 0

Steven Evers
Steven Evers

Reputation: 17186

This looks like what you're looking for:

foreach (int i = 0; i < values.Length; i++)
{
    SqlParameter param = insertCmd.Parameters[i];
    if (string.IsNullOrEmpty(values[i]))
    {
        param.Value = new Decimal(0m);
    }
    else
    {
        decimal d = new Decimal(0m);
        if (decimal.TryParse(values[i], out d))
            param.Value = d;
        else
            param.Value = new Decimal(0m);
    }   
}

note that the problem with yours looks to be here:

if (!decimal.TryParse(values[i], out value))          
{            
    param.Value = values[i];          
}

basically, you're saying "if the parse failed, then use the text the values array at i".

Upvotes: 0

code4life
code4life

Reputation: 15794

Your problem is that the default value of decimal is 0m.

So:

decimal value;
if (values[i] == null || values[i] == "")
{
    value = 0.000m;
}
else
{
    if (!decimal.TryParse(values[i], out value))
    {
       // param.Value = decimal.TryParse(values[i], out value) ? value : 0;
       param.Value = values[i];
    }

    // value == 0m still!!! 
    // put debugger on this line and you can verify this:
    var testDecimalValue = value;
}

param.Value = value;

Pretty much guarantees that only numeric values or 0 (if the value is not numeric) will be set to the param.Value property.

Try changing your code to:

if (values[i] == null || values[i] == "")
{
    param.Value = 0.000m;
}
else
{
    decimal value;
    if (!decimal.TryParse(values[i], out value))
    {
       // param.Value = decimal.TryParse(values[i], out value) ? value : 0;
       param.Value = values[i];
    }
    else
    {
       param.Value = value;
    }
}

Upvotes: 1

John
John

Reputation: 16007

I'm not a C# programmer, but I'd inspect values when you hit one of the blanks in the debugger, see what the type of the blank is, and test for that condition explicitly as you go through your for loop. For example, if it's null then just set that value to 0.0 in your loop.

Upvotes: 0

iivel
iivel

Reputation: 2576

If you were in SQL it would be IsNull() or Coalesce(), but in C# you'll just want to toss a ternary operater in place "?:" since decimal.TryParse should return false if it cannot parse, your second value would be 0.0

http://msdn.microsoft.com/en-us/library/ty67wk28(VS.80).aspx

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 837926

Try this:

decimal value;
if (values[i] == string.Empty || values[i] == "blank")
{
    value = 0.0m;
}
else
{
    if (!decimal.TryParse(values[i], out value))
    {
        // Error handling
    }
}

param.Value = value;

Upvotes: 0

Related Questions