views:

69

answers:

3

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?

+5  A: 

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.

Andy West
Perfect. That's exactly what I did. I won't delete this question because I think it could be valuable for others when troubleshooting, but if someone wants to mark it for deletion I could see the merit in that, as well.
Geo Ego
A: 

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);
jbonethegreat
To format code, select it in the editor and press Control-K.
John Saunders
+1  A: 

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
...
Chris Taylor
When I added the ToString methods, I did a simple Find and Replace to change ".Text;" to ".Text.ToString();" so, of course, it didn't find (nor replace, obviously) the offending lines. Thanks for the excellent idea, however! I'm definitely going to keep this in mind for future error-checking.
Geo Ego