CODEWITHSUNDEEP
c#mysql
user3806048
user3806048

Reputation: 73

Updating mysql database cause a error

I was trying to update my mysql database but it didn't work and showed me error.

The code :

string constring = "datasource=localhost;port=3306;username=root;password=root";
string Query = "update  database.check set  namethestore = '" + this.textBox65.Text + "' , checkername= '" + this.textBox66.Text + "' ,  where namethestore = '" + this.textBox65.Text + "'  ;";
MySqlConnection conDataBase = new MySqlConnection(constring);
MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
MySqlDataReader myReader;
try
{
   conDataBase.Open();
   myReader = cmdDataBase.ExecuteReader();
   MessageBox.Show("saved");

   while (myReader.Read())
   {
   }

Upvotes: 0

Views: 110

Answers (3)

Rahul
Rahul

Reputation: 77934

You have a extra , in your UPDATE statement before WHERE condition as below

update  database.check set  namethestore = '" + this.textBox65.Text + "' , 
checkername= '" + this.textBox66.Text + "' ,  where ...;

                                           <--Here

As other's have already commented, your code is vulnerable to SQL Injection. Try using a parametrized query rather.

Also, since you are performing a DML operation, no need of a DataReader. You can either use ExecuteNonQuery or ExecuteScalar.

For your reference:

string constring = "datasource=localhost;port=3306;username=root;password=root";
string Query = "update  database.check set  namethestore = 
@namethestore,checkername=@checkername where namethestore = @namethestore";
using(MySqlConnection conDataBase = new MySqlConnection(constring))
{
  using(MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase))
   {
     conDataBase.Open();
     cmdDataBase.CommandText = Query;
     cmdDataBase.Parameters.Add("@namethestore", this.textBox65.Text);
     cmdDataBase.Parameters.Add("@checkername", this.textBox66.Text);

     cmdDataBase.ExecuteNonQuery();
   }
}

Upvotes: 0

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98868

ExecuteReader returns a MySqlDataReader which contains the result of your MySqlCommand. Since you use INSERT statement, there is no data your statement that return. Your statement is just insert a value from your database.

That's why you should use ExecuteNonQuery. It just executes your statement and for INSERT statement, it returns that the number of rows affected.

And you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your database connections.

And you have an extra comma (,) before your WHERE condition.

using(MySqlConnection conn = new MySqlConnection(constring))
using(MySqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "update database.check set namethestore = @namethestore, checkername= @checkername where namethestore = @namethestore";
    cmd.Parameters.AddWithValue(@namethestore, this.textBox66.Text);
    cmd.Parameters.AddWithValue(@checkername, this.textBox65.Text);
    conn.Open()
    if(cmd.ExecuteNonQuery() > 0)
       MessageBox.Show("saved");
}

I used AddWithValue in my example because I don't know your column types but you don't use it. Use SqlParameterCollection.Add method instead.

Read http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

Upvotes: 0

Bruno Costa
Bruno Costa

Reputation: 2720

There are so many things wrong on that code.

  1. You should use ExecuteNonQuery to an update statement
  2. You must use parameterised query to protect your code from SQL Injection
  3. AS Rahul said, you have an extra ,
  4. I would like to know if you are closing the connection at the end.

Upvotes: 1

Related Questions