C# and MySql Inserting Data Error

Question

I have a problem in my application that I am developing.

I Insert 4 Data FieldsLets call them ("Age", "Gender" , "Name", "Tel") in to a MySQL database. 99% of the time it will work 100% then once in a while I will get something odd.

Row 1 will have the Age , Gender, "Null" , "Null" and row 2 Will have "Null" , "Null" , Name , Tel so basically it will insert one line of data into 2 rows.

Below is the sample of the code:

String constring = ConfigurationManager.ConnectionStrings["server"].ConnectionString;
            string Query = "insert into db.Table (Name , Surname , ID , Tel) 
values('" + this.textBox1.Text + "' , '" + this.textBox2.Text + "'  , 
'" + this.textBox6.Text + "'  , '" + this.textBox4.Text + "');";
            MySqlConnection conDataBase = new MySqlConnection(constring);
            MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
            MySqlDataReader myReader;
            conDataBase.Open();
            try
            {


                myReader = cmdDataBase.ExecuteReader();

                while (myReader.Read())
                {

                }
                conDataBase.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            MessageBox.Show("Cleint Details has been added to system");
        }

If anyone can think of a reason for this please help. I did check the environment (network connection etc.) but those are fine.

When you have this problem, does your data include quotes or anything that could be breaking your SQL statement "incorrectly"? — AlG
– AlG, Commented Jul 17, 2015 at 16:28
From what you stated, there are two queries getting submitted. Your code only shows how a single query gets submitted. I suggest you look at what's triggering your method. I'd also check to see if your code has events that are thrown such that multiple calls of incomplete data might get sent twice under specific circumstances. — Paurian
– Paurian, Commented Jul 17, 2015 at 16:45

Reece Kenney · Accepted Answer · 2015-07-17 16:28:02Z

2

It is possible there are values in the text box that are affecting the insert statement. It would be better if you used parameterised statements. E.g.:

string Query = "insert into db.Table (Name , Surname , ID , Tel) values(@param1, @param2 , @param3  , @param4);";

MySqlConnection conDataBase = new MySqlConnection(constring);
MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);

//Add paramter values
cmdDataBase.Parameters.AddWithValue("@param1", this.textBox1.Text);
cmdDataBase.Parameters.AddWithValue("@param2", this.textBox2.Text);
cmdDataBase.Parameters.AddWithValue("@param3", this.textBox6.Text);
cmdDataBase.Parameters.AddWithValue("@param4", this.textBox4.Text);

answered Jul 17, 2015 at 16:28

Reece Kenney

2,9743 gold badges28 silver badges59 bronze badges

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

1 Comment

Juliuz Delportt Over a year ago

Hi thank for the feed back this solved it but also not only that it showed me what the user did to make the error happen , if a person has a name like "Mike De'Le" the sql command will stop at the ' and just explode and go wrong. so this solved the problem thanks guys :)

Sgr3005 · Accepted Answer · 2015-07-17 16:55:53Z

0

I think Reece's answer is going to solve your problem, but I also recommend ditching the ExecuteReader() and while{} loop. Instead just use cmdDataBase.ExecuteNonQuery(); to fire the insert.

answered Jul 17, 2015 at 16:55

Sgr3005

1041 silver badge4 bronze badges

Collectives™ on Stack Overflow

C# and MySql Inserting Data Error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related