c#如何在sql查询中更新数据库中的bool值

本文关键字：更新 数据库 bool 查询 sql | 更新日期: 2023-09-27 18:11:27

如何在sql查询中更新数据库中的bool值

下面是我的代码，但我不确定如何实现复选框。谢谢你的帮助。

我已经更新了代码，以消除SQL注入问题。

con.Open();
OleDbCommand cmd = new OleDbCommand(String.Concat("Select * From ", comboBox1.Text), con);
cmd.CommandType = CommandType.Text;
string tableName = comboBox1.Text.ToString();
cmd.CommandText = @"UPDATE [" + tableName + "] SET"
    +"People_Call_Status = @People_Call_Status,"
    +"Research_Date=@Research_Date,"
    + "tblCompanies_Area_Dialling_Code = @tblCompanies_Area_Dialling_Code,"
    + "Work_Number = @Work_Number,"
    + "building_Address = @building_Address,"
    + "[Street Address] = @[Street Address],"
    + "suburb = @suburb,"
    + "city = @city,"
    + "res_Code = @res_Code,"
    + "industry_Vertical_ID = @industry_Vertical_ID,"
    + "pO_Box = @pO_Box,"
    + "post_Office = @post_Office,"
    + "postal_Code = @postal_Code,"
    + "country_ID = @country_ID,"
    + "province_ID = @province_ID," //this line
    + "prospect = @prospect"
    + "WHERE Company_ID = @Company_ID ";
cmd.Parameters.AddWithValue("@People_Call_Status", Status_textBox1.Text);
cmd.Parameters.AddWithValue("@Research_Date", Date_textBox.Text);
cmd.Parameters.AddWithValue("@Company_Name", company_NameTextBox.Text);
cmd.Parameters.AddWithValue("@tblCompanies_Area_Dialling_Code", tblCompanies_Area_Dialling_CodeTextBox.Text);
cmd.Parameters.AddWithValue("@Work_Number", work_NumberTextBox.Text);
cmd.Parameters.AddWithValue("@building_Address", building_AddressTextBox.Text);
cmd.Parameters.AddWithValue("@[Street Address]", street_AddressTextBox.Text);
cmd.Parameters.AddWithValue("@suburb", suburbTextBox.Text);
cmd.Parameters.AddWithValue("@city", cityTextBox.Text);
cmd.Parameters.AddWithValue("@res_Code", res_CodeTextBox.Text);
cmd.Parameters.AddWithValue("@industry_Vertical_ID", industry_Vertical_IDTextBox.Text);
cmd.Parameters.AddWithValue("@pO_Box", pO_BoxTextBox.Text);
cmd.Parameters.AddWithValue("@post_Office", post_OfficeTextBox.Text);
cmd.Parameters.AddWithValue("@postal_Code", postal_CodeTextBox.Text);
cmd.Parameters.AddWithValue("@country_ID", country_IDTextBox.Text);
cmd.Parameters.AddWithValue("@province_ID", province_IDTextBox.Text);
cmd.Parameters.AddWithValue("@prospect", prospectCheckBox.Checked);                                     
cmd.Parameters.AddWithValue("@Company_ID", company_IDTextBox.Text);
cmd.ExecuteNonQuery();
{
    MessageBox.Show("Update Success!");
    con.Close();
}

c#如何在sql查询中更新数据库中的bool值

在SQL Server中，bool被映射为具有0和1值的bit数据类型

所以你需要做的是:

"', prospect = '" + prospectCheckBox.Checked ? 1 : 0

附注:

不要从用户数据输入连接字符串来构建查询，这很容易受到SQL注入的攻击。可以使用参数化查询或存储过程。

您使用的是什么DBMS平台，您可以显示表的DDL吗?

我不能保证这将工作，但代替使用AddWithValue尝试使用Add方法与显式类型声明:

cmd.Parameters.Add(new OleDbParameter("@People_Call_Status", OleDbType.VarChar));
cmd.Parameters.Add(new OleDbParameter("@Research_Date", OleDbType.VarChar));
...
cmd.Parameters.Add(new OleDbParameter("@prospect", OleDbType.Boolean));
cmd.Parameters.Add(new OleDbParameter("@Company_ID", OleDbType.VarChar));
cmd.Parameters[0].Value = Status_textBox1.Text;
cmd.Parameters[1].Value = Date_textBox.Text;
...
cmd.Parameters[16].Value = prospectCheckBox.Checked;
cmd.Parameters[17].Value = company_IDTextBox.Text;

另外，关于SQL注入，你仍然有一个理论上的漏洞:

cmd.CommandText = @"UPDATE [" + tableName + "] SET"
    + "People_Call_Status = @People_Call_Status,"
    + "Research_Date=@Research_Date,"
    ...
    + "prospect = @prospect"
    + "WHERE Company_ID = @Company_ID ";

我意识到这是一个模糊的可能性，但如果tableName变量包含这个字符串或类似的东西:

Table1 set foo = 'bar';
truncate table ba2;
update table3

你可以看到它是如何编译、执行和做一些你想象不到的事情的。再一次，我意识到这有点过分，而且您的输入确实来自组合框，但这仍然是理论上的风险。

相关文章：