sqlcommand for updation

I have a table which I want to update using a simple update command.

protected void UpdateButton_Click(object sender, EventArgs e)
        {

        SqlCommand cmd = new SqlCommand("UPDATE KPI_DETAILS_TABLE SET KPI1_Status =
         @KPI1_Status, KPI2_Status = @KPI2_Status, KPI3_Status = @KPI3_Status,
         KPI4_Status = @KPI4_Status, KPI5_Status = @KPI5_Status, KPI6_Status =
         @KPI6_Status, Overall_Status= @Overall_Status WHERE TokenID = '" + 
         DropDownList1.SelectedItem.Text + "' AND TimeSet = '" 
         + currentdate + "'", connection);

        cmd.Parameters.AddWithValue("@KPI1_Status", DropboxKPI1.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@KPI2_Status", DropboxKPI2.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@KPI3_Status", DropboxKPI3.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@KPI4_Status", DropboxKPI4.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@KPI5_Status", DropboxKPI5.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@KPI6_Status", DropboxKPI6.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@Overall_Status", FinalStatus.SelectedItem.Text);

        try
        {
            cmd.ExecuteNonQuery();
            Error1.Text = "KPI Status Successfully Updated !!";
        }
        catch { Error1.Text = "Error during Updating status of KPIs"; }
        finally { connection.Close(); }
    }

However it's throwing the following exception error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The only column of datatype datetime in the dat开发者_StackOverflow社区abase is TimeSet. But currentdate is also of data type datetime.

DateTime currentdate = DateTime.Now.ToLocalTime();

Then why is this error popping up? Please help.

a) Use parameters for the values in your WHERE clause, as well as for the SET part, and

b) Then use cmd.Parameters.AddWithValue("@TimeSet", DateTime.Now.ToLocalTime());

This will also protect you from SQL injection.

I.e. if you've got a datetime value, try to keep it as a datetime value, and don't muck about with trying to treat it as a string at any point. Let ADO.Net and SQL Server deal with any necessary conversions.

Your code should look like this:

    protected void UpdateButton_Click(object sender, EventArgs e)
    {

    SqlCommand cmd = new SqlCommand("UPDATE KPI_DETAILS_TABLE SET"+
        "KPI1_Status = @KPI1_Status, KPI2_Status = @KPI2_Status,"+
        "KPI3_Status = @KPI3_Status, KPI4_Status = @KPI4_Status,"+
        "KPI5_Status = @KPI5_Status, KPI6_Status = @KPI6_Status,"+
        "Overall_Status= @Overall_Status"+
        "WHERE TokenID = @ID AND TimeSet = @Time", connection);

    cmd.Parameters.AddWithValue("@KPI1_Status", DropboxKPI1.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@KPI2_Status", DropboxKPI2.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@KPI3_Status", DropboxKPI3.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@KPI4_Status", DropboxKPI4.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@KPI5_Status", DropboxKPI5.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@KPI6_Status", DropboxKPI6.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Overall_Status", FinalStatus.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@ID", DropDownList1.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Time", DateTime.Now.ToLocalTime());
    try
    {
        cmd.ExecuteNonQuery();
        Error1.Text = "KPI Status Successfully Updated !!";
    }
    catch { Error1.Text = "Error during Updating status of KPIs"; }
    finally { connection.Close(); }
}

1. Repaired the mess in the string of your SqlCommand object.
2. Instead of adding local variables to your SqlCommand I added new SqlParameters and defined where they'd get their values from (@ID, @Time).

Instead you use DateTime.Now.ToString(); for giving the Currentdate and try again.