无法在数据库中插入和检查类似数据

I'm trying to insert an account into the database. I shall briefly explain what my codes does before adding my syntax below. Generally, I'm checking if the particular textbox is empty. if it's not, it will attempt to insert data into the database. However, if the database contain a data similar to what is being type in the textbox they will prompt different error respectively. Unfortunately, for mine, i'm not even able to insert any data and there's also no error to show that the data in the textbox and database is the same

我正在尝试将帐户插入数据库。在下面添加语法之前，我将简要解释一下我的代码是做什么的。通常，我正在检查特定文本框是否为空。如果不是，它将尝试将数据插入数据库。但是，如果数据库包含的数据类似于文本框中的类型，则它们将分别提示不同的错误。不幸的是，对我来说，我甚至无法插入任何数据，也没有错误显示文本框和数据库中的数据是相同的

protected void btnAdd_Click(object sender, EventArgs e)
    {

        if (tbpid.Text.Equals(""))
        {
            lbmsg.Text = "Please generate a police ID for this account";
        }
        else if (tbfullname.Text.Equals(""))
        {
            lbmsg.Text = "Please type the full name of the police officer";
        }
        else if (tbnric.Text.Equals(""))
        {
            lbmsg.Text = "Please enter the NRIC of the police officer";
        }
        else if (ddllocation.SelectedValue.Equals("Select Location"))
        {
            lbmsg.Text = "Please select the location of the policepost he will be posted to";
        }
        else { 

        SqlConnection con = new SqlConnection("Data Source = localhost; Initial Catalog = MajorProject; Integrated Security= SSPI");
        con.Open();
        SqlCommand select = new SqlCommand("Select policeid, nric from PoliceAccount where policeid = @policeid", con);
        SqlDataReader dr;

select.Parameters.AddWithValue("@policeid", tbpid.Text);

        dr = select.ExecuteReader();
        if (dr.Read())
        {
            if (tbpid.Equals(dr["policeid"].ToString()))
            {

                lbmsg.Text = "Police ID already exists. Please generate another new Police ID";

            }
            else if (tbnric.Equals(dr["nric"].ToString()))
            {
                lbmsg.Text = "NRIC already exists. Please ensure the NRIC is correct";
            }

        }

        else
        {

            SqlConnection conn = new SqlConnection("Data Source = localhost; Initial Catalog = MajorProject; Integrated Security= SSPI");
            conn.Open();
            SqlCommand cmd = new SqlCommand("insert into PoliceAccount(policeid, password, nric, fullname, postedto)  values('" + tbpid.Text.Trim() + "','" + tbpid.Text.Trim() + "','" + tbnric.Text.Trim() + "','" + tbfullname.Text.Trim() + "', '" + ddllocation.SelectedValue + "')", conn);
            cmd.ExecuteNonQuery();
            conn.Close();

            Response.Redirect("AdminAddAccount");

        }

        }

    }

Please refer to this [thread for the correct answer][1]

请参考[线程获取正确答案] [1]

2 个解决方案

#1

It never gets into the else, to insert data, because your select statement isn't filtered. This statement:

它永远不会进入else，插入数据，因为你的select语句没有被过滤。这个说法：

Select policeid, nric from PoliceAccount

will return all rows from that table. However, what you really want is:

将返回该表中的所有行。但是，你真正想要的是：

Select policeid, nric from PoliceAccount where policeid = @policeid

and then, before executing the reader, add this line of code:

然后，在执行阅读器之前，添加以下代码行：

select.Parameters.AddWithValue("@policeid", tbpid.Text);

Finally, use that same parameterized syntax on the insert statement, it's safe from SQL Injection.

最后，在insert语句中使用相同的参数化语法，从SQL注入中是安全的。

#2

What The Solution has said is right. Also add cmd.CommandType = CommandType.Text; in your code before cmd.ExecuteNonQuery();.

解决方案所说的是对的。另外添加cmd.CommandType = CommandType.Text;在cmd.ExecuteNonQuery（）;之前的代码中

Also I think that you should add .aspx here Response.Redirect("AdminAddAccount.aspx");

另外我认为你应该在这里添加.aspx Response.Redirect（“AdminAddAccount.aspx”）;

#1