oledbException未处理错误

Question

我试图使用下面的代码填充一个带有名字和姓氏的文本框：

using (OleDbConnection connName = new OleDbConnection(strCon))
            {

                String sqlName = "SELECT forename, Surname FROM customer WHERE [customerID]=" + txtCustomerID.Text;

                // Create a command to use to call the database.
                OleDbCommand commandname = new OleDbCommand(sqlName, connName);
                 connName.Open();
                // Create a reader containing the results


                using (OleDbDataReader readerName = commandname.ExecuteReader())
                {
                    readerName.Read(); // Advance to the first row.
                    txtName.Text = readerName[0].ToString();
                }
                connName.Close();                 

             }

但是，我得到了一个错误：OleDbException没有被处理。

“对于一个更需要的参数，没有要求的值”

在ExecuteReader，我不知道该如何解决这个问题。

编辑:下面的代码与查询中的信息几乎完全相同，但是这个异常不会出现。

  string strCon = Properties.Settings.Default.PID2dbConnectionString;

        using (OleDbConnection conn = new OleDbConnection(strCon))
        {
            String sqlPoints = "SELECT points FROM customer WHERE [customerID]=" + txtCustomerID.Text;
            conn.Open();

            // Create a command to use to call the database.
            OleDbCommand command = new OleDbCommand(sqlPoints, conn);
            // Create a reader containing the results

            using (OleDbDataReader reader = command.ExecuteReader())
            {
                reader.Read(); // Advance to the first row.
                txtPoints.Text = reader[0].ToString(); // Read the contents of the first column
            }
            conn.Close();
        }

Answer 1

这样做的通常原因是空字符串或空字符串，即txtCustomerID.Text没有值，所以发送到服务器的查询是：

SELECT forename, Surname FROM customer WHERE [customerID]= 

您可以避免这样的错误和SQL注入，使用强类型的参数，并避免使用参数化查询(我假设客户ID是一个int字段)。

    using (OleDbConnection connName = new OleDbConnection(strCon))
    {
        String sqlName = "SELECT forename, Surname FROM customer WHERE customerID = @CustomerID";

        // Create a command to use to call the database.
        using (OleDbCommand commandname = new OleDbCommand(sqlName, connName))
        {

            //Check the input is valid
            int customerID = 0;
            if (!int.TryParse(txtCustomerID.Text, out customerID))
            {
                txtName.Text = "Customer ID Text box is not an integer";
                return;
            }


            connName.Open();

            // Add the parameter to the command
            commandname.Parameters.Add("@CustomerID", OleDbType.Integer).Value = customerID;

            // Create a reader containing the results
            using (OleDbDataReader readerName = commandname.ExecuteReader())
            {
                readerName.Read(); // Advance to the first row.
                txtName.Text = readerName[0].ToString();
            }
            connName.Close();                 
        }
    }

Answer 2

必须对字符串查询中使用的参数进行编码。

  String sqlName = String.Format("SELECT forname, Surname FROM customer WHERE customerID={0}",txtCustomerID.Text);

但我建议您不要使用字符串硬编码的SQL查询。这是一种简单的SQL注入攻击方式。你应该用参数表代替。