问题描述:

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 database is TimeSet. But currentdate is also of data type datetime.

DateTime currentdate = DateTime.Now.ToLocalTime();

Then why is this error popping up? Please help.

网友答案:

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).
网友答案:

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.

网友答案:

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

相关阅读:
Top