c# - gridview使用时间戳更新更新 asp.net 时出错

Question

我有两个按钮，SetIn 和 SetOut。它们都有 commandName UPDATE (我有两个按钮，因为我希望按钮上的文本根据行中一个字段的值而有所不同。

我遇到一个问题，当我运行更新 SQL 语句时，它会向我抛出错误。它说:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
Additional information: Incorrect syntax near '14'.

这意味着我尝试更新行的日期和时间戳存在问题(我在下午 14.02 进行更新)。

我的aspx.cs页面代码是:

  protected void GridView1_RowUpdating(object sender, System.Web.UI.WebControls.GridViewUpdateEventArgs e)
{
     Label id = GridView1.Rows[e.RowIndex].FindControl("lbl_Index") as Label;
     int rowToUpdate = Int32.Parse(id.Text);
     con = new SqlConnection(cs);

     int scopeValue;
     con = new SqlConnection(cs);
     cmd = new SqlCommand();

     cmd.CommandText = "SELECT in_scope FROM " + databaseName + " WHERE id = '" + rowToUpdate + "'";
     cmd.Parameters.AddWithValue("@id", rowToUpdate);
     cmd.Connection = con;

     try
     {
         con.Open();
         scopeValue = Convert.ToInt32(cmd.ExecuteScalar());
         //database_entries.Text = recordCount.ToString();
     }
     finally
     {
         con.Close();
     }

    string modifiedBy = userManagement.getWeldID();
    string updateDate = DateTime.UtcNow.ToString("dd/MMM/yyyy HH:mm:ss");
    {
        if (scopeValue == 1)
        {
           // Label id = GridView1.Rows[e.RowIndex].FindControl("lbl_Index") as Label;
            string sqlStatement = "";
            con = new SqlConnection(cs);
           // SqlCommand cmd = new SqlCommand();
            sqlStatement = @"update dbo.Fair_Use_InScope_MIF_Alex_temp set in_scope = '0', modified_by = " + modifiedBy + ", date_updated = " + updateDate + " where id = '" + rowToUpdate + "'";

            con.Open();
            cmd = new SqlCommand(sqlStatement, con);
            cmd.ExecuteNonQuery();
            con.Close();
            ShowData();
        }
        if (scopeValue == 0)
        {
           // Label id = GridView1.Rows[e.RowIndex].FindControl("lbl_Index") as Label;
            string sqlStatement = "";
            con = new SqlConnection(cs);
           // SqlCommand cmd = new SqlCommand();
            sqlStatement = @"update dbo.Fair_Use_InScope_MIF_Alex_temp set in_scope = '1', modified_by = " + modifiedBy + ", date_updated = " + updateDate + " where id = '" + rowToUpdate + "'";
            con.Open();
            cmd = new SqlCommand(sqlStatement, con);
            cmd.ExecuteNonQuery();
            con.Close();
            ShowData();
        }
    }
}

我基本上试图获取该行的scopeValue(0或1)，如果调用Update命令，并且scopeValue以前为0，现在将其设置为1，反之亦然。我还需要更新进行更改的时间以及谁在该行中进行了更改。 (这些都显示在gridview中)

任何帮助将不胜感激，因为我是 ASP.NET、C# 和 SQL SERVER 的初学者!!

Answer 1

您将 DateTime 作为字符串发送(甚至没有用单引号括起来 date_updated = '"+ updateDate + "')，这总是会导致问题。

最好在查询中使用参数。这样可以防止 SQL 注入(inject)，提高可读性，确保类型安全，不再担心引号的正确使用等。

        string sqlStatement = "UPDATE dbo.Fair_Use_InScope_MIF_Alex_temp SET in_scope = 0, modified_by = @modifiedBy, date_updated = @updateDate WHERE (id = @rowToUpdate)";

        using (SqlConnection connection = new SqlConnection(cs))
        using (SqlCommand command = new SqlCommand(sqlStatement, connection))
        {
            command.Parameters.Add("@modifiedBy", SqlDbType.VarChar).Value = modifiedBy;
            command.Parameters.Add("@updateDate", SqlDbType.DateTime).Value = DateTime.Now;
            command.Parameters.Add("@rowToUpdate", SqlDbType.Int).Value = 35;

            connection.Open();
            command.ExecuteNonQuery();
        }