ADO.NET parameters from TextBox

Question

I'm trying to call a parameterized stored procedure from SQL Server 2005 in my C# Winforms app. I add the parameters from TextBoxeslike so (there are 88 of them):

cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100).Value = CustomerName.Text;

I get the following exception:

"System.InvalidCastException: Failed to convert parameter value
 from a TextBox to a String. ---> System.InvalidCastException:
 Object must implement IConvertible."

The line throwing the error is when I call the query:

cmd.ExecuteNonQuery();

I also tried using the .ToString() method on the TextBoxes, which seemed pointless anyway, and threw the same error. Am I passing the parameters incorrectly?

Answer 1

It's probable that you forgot to specify the Text property when assigning one of your parameter values.

For example, instead of:

CustomerName.Text

You may have done just:

CustomerName

This would be an easy thing to miss if there are 88 of them.

Answer 2

try something like this:

SqlParameter sqlParam= new SqlParameter();
sqlParam.ParameterName = "@customerName";
sqlParam.Direction = ParameterDirection.Input;
sqlParam.SqlDbType = SqlDbType.VarChar;
sqlParam.Size = 100;
sqlParam.Value = customername.Text;
cmd.Parameters.Add(sqlParam);

Answer 3

I suspect you have missed a .Text somewhere, but you say you did a ToString on all the parameter values so that seems strange. Anyway, you can use the following piece of code to dump the parameter names and the type name of the value, this might help spot the problematic parameter.

foreach (SqlParameter param in cmd.Parameters)
{
  System.Diagnostics.Debug.WriteLine(string.Format("{0}:{1}", param.ParameterName, param.Value == null ? "NULL" : param.Value.GetType().Name));
}

You should get something like the following in the debug window, in this case CustomerSurname is the parameter that has a Textbox and not a string as an example.

@CustomerName:String
@CustomerSurname:TextBox
...