MySql Update won't work

Question

Good day! I need help please.. This is my code on c# whenever I execute it nothing happens no error or hint

string myConnection = " datasource=localhost;port=3306;username=root;password=wolf";
        string Query = " UPDATE bikerentaldb.tblbikes SET status='Rented',renteddate=NOW(),assignedreturndate='" + txtToBeReturned.Text + "' WHERE bikeID='" + txtBikeIdRent.Text + "'"; 
        MySqlConnection myConn = new MySqlConnection(myConnection);
        MySqlCommand SelectCommand = new MySqlCommand(Query, myConn);
        myConn.Open();
        MessageBox.Show("Data Saved");
        myConn.Close();

I am not sure why the Upate won't work but when I execute this code on MySql

UPDATE bikerentaldb.tblbikes SET status='Rented',renteddate=NOW(),assignedreturndate=NOW() WHERE bikeID='2';

It works just fine can someone help me?

consider renaming the command variable if it does not actually select anything. — Crowcoder, Sep 18 '16 at 22:08

score 2 · Accepted Answer · edited May 23 '17 at 12:00

A command should be executed to do anything. Your code misses the call to SelectCommand.ExecuteNonQuery() line after the open connection. However after fixing this trivial error you could encounter other problems with the values concatenated to form your command text. What if the user types an invalid date? Have you ever heard of Sql Injection hacks?

This is how your code should be written after adding validation to your inputs and parameters to send values to your database

int bikeID = 0;
if(!Int32.TryParse(txtBikeIdRent.Text, out bikeID)
{
     MessageBox.Show("Invalid number");
     return;
}
DateTime returnDate;
if(!DateTime.TryParse(txtToBeReturned.Text , out returnDate)
{
     MessageBox.Show("Invalid date");
     return;
}
string myConnection = ".....";
string Query = @"UPDATE bikerentaldb.tblbikes 
    SET status='Rented', renteddate=NOW(),
        assignedreturndate=@date
        WHERE bikeID=@id"; 
using(MySqlConnection myConn = new MySqlConnection(myConnection))
using(MySqlCommand cmd = new MySqlCommand(Query, myConn))
{
     myConn.Open();
     cmd.Parameters.Add("@date", MySqlDbType.Date).Value = returnDate;
     cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = bikeID;
     int rowUpdated = cmd.ExecuteNonQuery();
     if(rowUpdated > 0)
         MessageBox.Show("Record updated");
     else
         MessageBox.Show("No record match");
}

MySql Update won't work

1 Answers1