Getting error Input string was not in a correct format when updating via MySQL

Question

I am getting error "Input string was not in a correct format" when trying to update a table via the MySQL .NET connector.

The update statement works fine when run via MySQL workbench, but not via code and I am hoping someone can tell me why.

Code is:

MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId like '" + amazonOrderId + "%';";
command.ExecuteNonQuery();

I have tried both executing as a non query, and as ExecuteReader(); with no luck.

I am sure this is a simple mistake I am making, but I can't seem to find it for the life of me so any help would be greatly appreciated.

-- Edit -- I have tried the following with no luck:

MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId like '@amazonOrderId';";
command.Parameters.AddWithValue("@amazonOrderId", amazonOrderId);

also changed CommandText to:

 command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where convert(varchar(50), amazonOrderId) like '" + amazonOrderId + "';";

and

 command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId = '@amazonOrderId';";

and

command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId = @amazonOrderId;";

-- Resolution --

My resolution was actually found in another piece of code. After running through the debugger several times it became apparent that a MySqlConnection object was trying to be instantiated twice - with the same name etc. I removed the second instantiation and it has resolved the issue. It's too bad the error was misleading.

I appreciate everyone's responses as I feel they have made my code better and as such I have given +1's to Jon, Steve and Chris. Thanks for the help!

Any chance you can debug and look at the actual query text? I'd bet that you have a comma or something in your order ID - also doesn't MySql use backticks for identifying database objects? What method does the error get thrown on (can you post the stack?) — Charleh
– Charleh, Commented Jun 29, 2012 at 16:01
Is it possible that your amazonOrderId variable already has some quotes around it? The MySQL update statement seems to work properly: sqlfiddle.com/#!2/bf794/1 — biziclop
– biziclop, Commented Jun 29, 2012 at 16:04
@RobertH: I would parameterize it regardless of where it's come from. Aside from anything else, it's simply cleaner to separate code from data. — Jon Skeet
– Jon Skeet, Commented Jun 29, 2012 at 16:06

Chris Gessler · Accepted Answer · 2012-06-29 16:34:04Z

1

Is the variable amazonOrderId numeric? If so, you can't + a string to it without calling ToString() on the variable.

amazonOrderId.ToString() + "%"

Is amazonOrderId numeric in the database? If so, have you tried convert(varchar)?

MySqlCommand command = new MySqlCommand(); 
command.Connection = conn; 
command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where convert(varchar(50), amazonOrderId) like @OrderID;"; 
command.Parameters.AddWithValue("@orderID", amazonOrderId + "%");  
command.ExecuteNonQuery();

But I'm not sure why you need LIKE with %. Would this also update:

1001 - amazon order id
10010
10011
10012
10013 
etc.

Is that what you really want?

If not, then use equal instead, without single quotes.

MySqlCommand command = new MySqlCommand(); 
command.Connection = conn; 
command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId = @OrderId;"; 
command.Parameters.AddWithValue("@orderID", amazonOrderId);  
command.ExecuteNonQuery();

If it IS what you want, then why not use between or greater than?

MySqlCommand command = new MySqlCommand(); 
command.Connection = conn; 
command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId > @OrderId;"; 
command.Parameters.AddWithValue("@orderID", int.Parse(amazonOrderId.ToString() + "0"));  
command.ExecuteNonQuery();

edited Jun 29, 2012 at 16:34

answered Jun 29, 2012 at 15:59

Chris Gessler

23.2k8 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Robert H Over a year ago

nm previous comment, I will try to convert the amazonOrderID to varchar.

Sergey Kalinichenko Over a year ago

I cannot upvote this because it does not use parameterized SQL, even though I think you're 100% right on the cause of the problem :(

Chris Gessler Over a year ago

@dasblinkenlight - I was merely pointing out the issue with the user's code to resolve the error. I don't normally like to go off scope and start correcting other issues at the same time because the OP may not know which correction actually corrected the error, the use of params or the use of the convert function. I went ahead and added the params, although I think a simple note would have been sufficient.

Sergey Kalinichenko Over a year ago

@ChrisGessler +1 The problem with SO answers is that you think that you're answering a single user, but the next thing you know is that you have answered the entire internet! SO answers very often enjoy ridiculously high visibility in search engines. In a few days this question may get to the top of Google queries like "MySql Input string was not in a correct format", and then unsuspecting users would be guided to your answer that embeds values into query strings. They'll think it's "normal", and continue coding like that. That's why I think fixing it was a good thing :)

Steve · Accepted Answer · 2012-06-29 17:33:52Z

Probably you need to use parameters

MySqlCommand command = new MySqlCommand(); 
command.Connection = conn; 
command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId like @orderID"; 
command.Parameters.AddWithValue("@orderID", amazonOrderId + "%");
command.ExecuteNonQuery();

This will avoid errors when the parameter value is a string and contains single quotes and prevent SqlInjection attacks. The code above assumes that amazonOrderID field on the database is a text datatype and amazonOrderID variable is of string type.

However, the error message says that it doesn't recognize the input string.
This leads to the real problem. What kind of column is amazonOrderID in the database table? It's varchar (or other text type)? or is a numeric datatype?.

If it is a text type then the syntax with like and parameters should work provided that the amazonOrderID var in code is also a string.
If the column is of a numeric datatype then the LIKE has no sense and you should change the query using = operator and also be sure that the amazonOrderID var is of numeric type.

MySqlCommand command = new MySqlCommand(); 
command.Connection = conn; 
command.CommandText = "update fulfilled_shipments_data set addedCustomer=1 where amazonOrderId = @orderID"; 
command.Parameters.AddWithValue("@orderID", amazonOrderId);
command.ExecuteNonQuery();

Robert H · Accepted Answer · 2012-07-24 15:06:32Z

0

My resolution was actually found in another piece of code. After running through the debugger several times it became apparent that a MySqlConnection object was trying to be instantiated twice - with the same name etc. I removed the second instantiation and it has resolved the issue. It's too bad the error was misleading.

answered Jul 24, 2012 at 15:06

Robert H

11.7k19 gold badges72 silver badges112 bronze badges

Collectives™ on Stack Overflow

Getting error Input string was not in a correct format when updating via MySQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related