Incorrect integer value : 'System.Windows.Forms.Textbox, Text: 1' for column 'customer_id' at row 1

Question

I don't know why I am getting this error. I did insert integer values before. In Form1, I inserted both strings and int values before in previous forms.

        string customer_id = variables.cid;
        customerID_txt.Text = customer_id;

        string constring2 = "datasource=localhost;port=3306;username=root;password=root";
        string Query2 = "insert into artgallery.orders(customer_id,painting_id) values ('" + this.customerID_txt + "','" + this.pID_txt.Text + "');";
        MySqlConnection conDatabase2 = new MySqlConnection(constring2);

And the variables.cid is a string.

your table column customer_id,painting_id are INT or varchar? — HaveNoDisplayName, Jan 23 '15 at 16:20
It looks like you are passing the customer_ID as string instead of as an integer. — macoms01, Jan 23 '15 at 16:20
Please use parametrized queries to avoid this particular issue and generic [bobby tables](http://xkcd.com/327/) answers. — Alexei Levenkov, Jan 23 '15 at 16:21
Both customer_id and painting_id are INT in my orders table.BUT How do i pass it as INT ? How do i convert customerID_txt.Text to int? — Bolshoi Booze, Jan 23 '15 at 16:23
http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp... Hopefully someone familiar with MySQL will provide proper parametrized query as answer instead of fixing up string concatenation issues you have. — Alexei Levenkov, Jan 23 '15 at 16:29

score 0 · Answer 1 · answered Jan 23 '15 at 16:23

Try to remove the quotes from the value:

string customer_id = variables.cid;
customerID_txt.Text = customer_id;

string constring2 = "datasource=localhost;port=3306;username=root;password=root";
string Query2 = "insert into artgallery.orders(customer_id,painting_id) values (" + this.customerID_txt + ",'" + this.pID_txt.Text + "');";
MySqlConnection conDatabase2 = new MySqlConnection(constring2);

And if this.customer_id is a text box you will need to change it to this.customer_id.Text like you did with this.pID_txt.Text

score 0 · Answer 2 · answered Jan 23 '15 at 16:23

You mean this instead: (note the .Text and removing the quotes)

string customer_id = variables.cid;
customerID_txt.Text = customer_id;

string constring2 = "datasource=localhost;port=3306;username=root;password=root";
string Query2 = "insert into artgallery.orders(customer_id,painting_id) values (" + this.customerID_txt.Text + "," + this.pID_txt.Text + ");";
MySqlConnection conDatabase2 = new MySqlConnection(constring2);

Also you probably want to ensure those are integers so that no one can inject code into the database:

string customer_id = variables.cid;
customerID_txt.Text = customer_id;

string constring2 = "datasource=localhost;port=3306;username=root;password=root";
string Query2 = "insert into artgallery.orders(customer_id,painting_id) values (" + int.Parse(this.customerID_txt.Text) + "," + int.Parse(this.pID_txt.Text) + ");";
MySqlConnection conDatabase2 = new MySqlConnection(constring2);

score 0 · Answer 3 · answered Jan 23 '15 at 16:24

Remove single quotes and use customer_id or customerID_txt.Text instead of customerID_txt because it's a component. Try this code:

string customer_id = variables.cid;
customerID_txt.Text = customer_id;

string constring2 = "datasource=localhost;port=3306;username=root;password=root";
string Query2 = "insert into artgallery.orders(customer_id,painting_id) values (" + customer_id + ",'" + this.pID_txt.Text + "');";
MySqlConnection conDatabase2 = new MySqlConnection(constring2);

score 0 · Answer 4 · answered Jan 23 '15 at 16:25

Your code:

string Query2 = "insert into artgallery.orders(customer_id,painting_id) values ('" + this.customerID_txt + "','" + this.pID_txt.Text + "');";

creates the following sql:

insert into artgallery.orders(customer_id,painting_id) values ('xxx','yyy');

That SQL says that you are trying to insert two strings. Remove the quotes and you'll be on your way (assuming that the value is indeed an integer, etc.). As was noted, you want to do parametrized SQL to avoid SQL injection attacks.

score 0 · Answer 5 · answered Jan 23 '15 at 16:46

As others have pointed out, it's strongly recommended to use parameterized query.
The code should be on the lines of (assuming both parameters are int):

string customer_id = variables.cid;
customerID_txt.Text = customer_id;
string constring2 = "datasource=localhost;port=3306;username=root;password=root";

using (MySqlConnection connection = new MySqlConnection(constring2))
{
    string query = "insert into artgallery.orders(customer_id, painting_id) values (@cutsomerId, @paintingId);";

    MySqlCommand command = new MySqlCommand(query, connection);

    command.Parameters.Add(new MySqlParameter("customerId", int.Parse(this.customerID_txt.Text)));
    command.Parameters.Add(new MySqlParameter("paintingId", int.Parse(this.pID_txt.Text)));

    command.Connection.Open();
    command.ExecuteNonQuery();
}

Incorrect integer value : 'System.Windows.Forms.Textbox, Text: 1' for column 'customer_id' at row 1

5 Answers5