How can I find which sql server parameter is throwing an exception in .net?

Question

I'm trying to execute a SQL Server stored procedure from .net. I'm getting the error

Error converting data type numeric to decimal

but I'm having a hard time determining which SQL parameter is the culprit.

There are 70, so I'm sure there's an easier way than a manual search.

Is it somewhere in the exception box, in "view detail?" Thanks in advance..

Relevant code (hopefully this is helpful... I didn't include all parameters, but this is the pattern):

public void WriteKeyStatToDB(KeyStatisticsDataCollection.KeyStatsDP dp)
{
    SqlParameter symbol = SqlParameterFactory("@symbol", SqlParamInOrOut.Input, SqlDbType.Char);
    SqlParameter dateAdded = SqlParameterFactory("@dateAdded", SqlParamInOrOut.Input, SqlDbType.Date);
    SqlParameter tradeDate = SqlParameterFactory("@tradeDate", SqlParamInOrOut.Input, SqlDbType.Date);
    SqlParameter marketCapIntra = SqlParameterFactory("@marketCapIntra", SqlParamInOrOut.Input, SqlDbType.Money);
    SqlParameter entVal = SqlParameterFactory("@entVal", SqlParamInOrOut.Input, SqlDbType.Money);
    SqlParameter trailingPE = SqlParameterFactory("@trailingPE", SqlParamInOrOut.Input, SqlDbType.Decimal);
    SqlParameter forwardPE = SqlParameterFactory("@forwardPE", SqlParamInOrOut.Input, SqlDbType.Decimal);

    symbol.Value = dp.Symbol;
    dateAdded.Value = dp.TradeDate;
    tradeDate.Value = dp.TradeDate;
    marketCapIntra.Value = dp.MarketCapIntraDay;
    entVal.Value = dp.EntValue;
    trailingPE.Value = dp.TrailingPE;
    forwardPE.Value = dp.ForwardPE;

    List<SqlParameter> sqlParams = new List<SqlParameter> {symbol, dateAdded, tradeDate, marketCapIntra, entVal, trailingPE, forwardPE};

    ExecuteSproc("spAddKeyStatDP", sqlParams, SqlConnection);
}

private static SqlParameter SqlParameterFactory (String ParamName, SqlParamInOrOut Direction, 
    SqlDbType SqlType)
{
    SqlParameter param = new SqlParameter
    {
        ParameterName = ParamName,
        SqlDbType = SqlType
    };

    if (Direction == SqlParamInOrOut.Input)
        param.Direction = ParameterDirection.Input;
    else
        param.Direction = ParameterDirection.Output;

    return param;
}

private static void ExecuteSproc(String CommandName, List<SqlParameter> ParamList, SqlConnection SqlConn, int Timeout = 0)
{
    using (SqlCommand cmd = new SqlCommand(CommandName, SqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (SqlParameter sp in ParamList)
        {
              if (sp.Value == null)
              sp.Value = DBNull.Value;
              cmd.Parameters.Add(sp);
         }

              cmd.CommandTimeout = Timeout;
              cmd.ExecuteNonQuery();
         }
     }
}

Let us see some code. I'm assuming you're reading the results of a stored procedure into some class? — Thelonias, Jul 31 '12 at 14:37
the problem is most likely sql, not .net as numeric and decimal are DB types. are you returning data or altering data with the stored proc? are there any computed values. whether it's an input parameter, or a computed result needs to be determined. — Jason Meckley, Jul 31 '12 at 14:40
@Ryan Thanks, I added code above, hope it helps. As you'll see, it's adding a class instance to the db as a record. — StatsViaCsh, Jul 31 '12 at 15:09
@Jason I'm adding data to the db. I'm sure the issue is a kludge value in one of the parameters, I just want the "easy" way to find the offender. — StatsViaCsh, Jul 31 '12 at 15:10
Do you really have a stored procedure that takes 70 parameters? It may be time to consider some rewriting... ;-) — CesarGon, Jul 31 '12 at 15:39
@CesarGon I do indeed. It's for all columns of a 70 column table of daily stock market data. I'm open to suggestions. ;) — StatsViaCsh, Jul 31 '12 at 15:49
@StatsViaCsh: Hard to say without knowing more about the usage of your table, i.e. do you retrieve all columns regularly, or rather selectively and in groups? Have a look at this, anyway: http://stackoverflow.com/questions/3496618/sql-server-any-value-in-vertical-partitioning-when-im-always-going-to-re-join — CesarGon, Jul 31 '12 at 16:03
@CesarGon Thanks for the link... I'll check that out. I do plan on using all those values, as I ramp up to using data mining with SSAS. — StatsViaCsh, Jul 31 '12 at 16:10

score 1 · Answer 1 · answered Jul 31 '12 at 15:06

1

You could Debug Sql Server stored procedure from Visual Studio. Here is Knowledge Base article of how to do it. Perhaps then it would be easy for you to find out which parameter was at fault

answered Jul 31 '12 at 15:06

Anand

14,545
8
32
44

score 1 · Accepted Answer · answered Jul 31 '12 at 15:49

I do not see 70 parameters in your code but assuming you have 70, here are some pointers to debug.

1) Since it is a decimal conversion error, and assuming the most common case of precision not enough to support your value, you can write a quick script and run it in sql server to see which one is at fault.
Untested script below:

StringBuilder sb = new StringBuilder();
            foreach (SqlParameter param in sqlParams)
            {
                sb.Append("declare " + param.ParameterName + " as " + param.SqlDbType + Environment.NewLine);
                sb.Append("set " + param.ParameterName + " = " + param.Value + Environment.NewLine);
            }
            string sqlTestString = sb.ToString();

2) You can use SQL profiler to see the parameters that have been passed to the SQL server during execution. You can also use other debugging information provided by the SQL profiler.

3) Not all parameter are going to be decimal. Assuming you have a classic case of 20 - 30% decimal parameters, that would amount to 14 - 20 parameters. Sometimes, it is worth to manually inspect them.

score 1 · Answer 3 · answered Jul 31 '12 at 16:17

1

maybe you can set the size and scale of the parameters and check which value causes the exception

answered Jul 31 '12 at 16:17

almi_n

51
3

How can I find which sql server parameter is throwing an exception in .net?

3 Answers3