写位值数据库 [英] Write bit value to database

问题描述

我想一个位值（true或false）写入我在一个名为field数据库处理。我目前正试图通过传递布尔值，要做到这一点，但我得到一个错误说不能从varchar类型转换为位。任何人都可以看到什么是在我的逻辑怎么回事？

 保护无效CheckBoxProcess_CheckedChanged（对象发件人，EventArgs的发送）
    {
        布尔更新;
        布尔trueBool = TRUE;
        布尔falseBool = FALSE;
        字符串checkedString =UPDATE SecureOrders SET加工=％+ trueBool +％'WHERE FNAME LIKE'％+ DefaultGrid.SelectedRow.Cells [2]。文本+％'AND LNAME LIKE'％+ DefaultGrid.SelectedRow。细胞[3]。文本+％';
        字符串uncheckedString =UPDATE SecureOrders SET加工=％+ falseBool +％'WHERE FNAME LIKE'％+ DefaultGrid.SelectedRow.Cells [2]。文本+％'AND LNAME LIKE'％+ DefaultGrid.SelectedRow。细胞[3]。文本+％';
        复选框CB =（复选框）发送;
        GridViewRow GVR =（GridViewRow）cb.Parent.Parent;
        DefaultGrid.SelectedIndex = gvr.RowIndex;
        更新= Convert.ToBoolean（DefaultGrid.SelectedValue）;        orderByString = orderByList.SelectedItem.Value;
        fieldString = searchTextBox.Text;
        System.Configuration.ConnectionStringSettings的connectionString;        的connectionString = rootWebConfig.ConnectionStrings.ConnectionStrings [secureodb];        //创建一个的SqlConnection到数据库。
        使用（SqlConnection的连接=新的SqlConnection（connectionString.ToString（）））
        {
            connection.Open（）;
            的SqlCommand checkedCmd =新的SqlCommand（checkedString，连接）;
            的SqlCommand uncheckedCmd =新的SqlCommand（uncheckedString，连接）;
            DataAdapter的=新的SqlDataAdapter（SELECT * FROM SecureOrders连接）;            //创建DataSet
            数据=新的DataSet（）;
            //使用我们的DataAdapter填充DataSet
            DataAdapter.Fill方法（数据集SecureOrders）;            数据视图源=新的数据视图（dataSet.Tables [0]）;
            DefaultGrid.DataSource =来源;
            如果（cb.Checked ==真）
            {
                checkedCmd.ExecuteNonQuery（）;            }
            其他
            {
                uncheckedCmd.ExecuteNonQuery（）;
            }            connection.close（）时;
        }
    }
 

解决方案

您需要将位字段设置为 1 或 0 取决于它是否是真的还是假的。

所以：

 字符串checkedString =UPDATE SecureOrders SET处理= 1，其中FNAME LIKE'％+ DefaultGrid.SelectedRow.Cells [2]。文本+％'AND LNAME LIKE'％+ DefaultGrid.SelectedRow.Cells [3]。文本+％';
字符串uncheckedString =UPDATE SecureOrders SET处理= 0 WHERE FNAME LIKE'％+ DefaultGrid.SelectedRow.Cells [2]。文本+％'AND LNAME LIKE'％+ DefaultGrid.SelectedRow.Cells [3]。文本+ ％';
 

此外，正如在评论中提到的，直接从用户输入构造SQL语句是牺牲品SQL注入攻击的最简单方法。它总是最好在这些情况下使用paramaterized查询（甚至是存储的特效）。

  ....
字符串checkedString =UPDATE SecureOrders SET处理= 1，其中FNAME如@ p1和LNAME LIKE @ P2;
字符串uncheckedString =UPDATE SecureOrders SET处理= 0 WHERE FNAME如@ p1和LNAME LIKE @ P2;
 

您可以创建参数传递给你的的ExecuteNonQuery 呼叫

 的SqlParameter P1 =新的SqlParameter（@ P1，SqlDbType.Varchar）{值=的String.format（％{0}％，DefaultGrid.SelectedRow.Cells [2]的.text）};
的SqlParameter P2 =新的SqlParameter（@ P2，SqlDbType.Varchar）{值=的String.format（％{0}％，DefaultGrid.SelectedRow.Cells [3]。文本）};
如果（cb.Checked ==真）
{
    checkedCmd.Parameters.Add（P1）;
    checkedCmd.Parameters.Add（P2）;
    checkedCmd.ExecuteNonQuery（）;}
其他
{
    uncheckedCmd.Parameters.Add（P1）;
    uncheckedCmd.Parameters.Add（P2）;
    uncheckedCmd.ExecuteNonQuery（）;
}
 

I'm trying to write a bit value (true or false) into my database in a field called "processed". i'm currently trying to do this by passing in bool values, but I get an error saying can't convert from type varchar to bit. Can anybody see what is going on in my logic?

       protected void CheckBoxProcess_CheckedChanged(object sender, EventArgs e)
    {
        bool update;
        bool trueBool = true;
        bool falseBool = false;
        string checkedString = "UPDATE SecureOrders SET processed = '%" + trueBool + "%' WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
        string uncheckedString = "UPDATE SecureOrders SET processed = '%" + falseBool + "%' WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
        CheckBox cb = (CheckBox)sender;
        GridViewRow gvr = (GridViewRow)cb.Parent.Parent;
        DefaultGrid.SelectedIndex = gvr.RowIndex;
        update = Convert.ToBoolean(DefaultGrid.SelectedValue);

        orderByString = orderByList.SelectedItem.Value;
        fieldString = searchTextBox.Text;


        System.Configuration.ConnectionStringSettings connectionString;

        connectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["secureodb"];



        // Create an SqlConnection to the database.
        using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
        {
            connection.Open();
            SqlCommand checkedCmd = new SqlCommand(checkedString, connection);
            SqlCommand uncheckedCmd = new SqlCommand(uncheckedString, connection);
            dataAdapter = new SqlDataAdapter("SELECT * FROM SecureOrders", connection);

            // create the DataSet
            dataSet = new DataSet();
            // fill the DataSet using our DataAdapter               
            dataAdapter.Fill(dataSet, "SecureOrders");

            DataView source = new DataView(dataSet.Tables[0]);
            DefaultGrid.DataSource = source;


            if (cb.Checked == true)
            {
                checkedCmd.ExecuteNonQuery();

            }
            else
            {
                uncheckedCmd.ExecuteNonQuery();
            }

            connection.Close();
        }






    }

解决方案

You need to set the bit fields to 1 or 0 depending on whether it is true or false.

So:

string checkedString = "UPDATE SecureOrders SET processed = 1 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
string uncheckedString = "UPDATE SecureOrders SET processed = 0 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";

Also, as has been mentioned in the comments, constructing your SQL statements directly from user input is the easiest way to fall victim to SQL Injection attacks. It's always best to use paramaterized queries (or even Stored Procs) in those cases.

....
string checkedString = "UPDATE SecureOrders SET processed = 1 WHERE fName LIKE @p1 AND lName LIKE @p2";
string uncheckedString = "UPDATE SecureOrders SET processed = 0 WHERE fName LIKE @p1 AND lName LIKE @p2";

You can then create parameters to pass to your ExecuteNonQuery call

SqlParameter p1 = new SqlParameter("@p1",SqlDbType.Varchar) { Value = string.Format("%{0}%",DefaultGrid.SelectedRow.Cells[2].Text) };
SqlParameter p2 = new SqlParameter("@p2",SqlDbType.Varchar) { Value = string.Format("%{0}%",DefaultGrid.SelectedRow.Cells[3].Text) };
if (cb.Checked == true)
{
    checkedCmd.Parameters.Add(p1);
    checkedCmd.Parameters.Add(p2);
    checkedCmd.ExecuteNonQuery();

}
else
{
    uncheckedCmd.Parameters.Add(p1);
    uncheckedCmd.Parameters.Add(p2);
    uncheckedCmd.ExecuteNonQuery();
}

这篇关于写位值数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！