SQL decimal conversion error

Question

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;
                                } 

Answer 1

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.

Answer 2

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;

Answer 3

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

Answer 4

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.

Answer 5

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;
    }
}

Answer 6

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".