没有给出一个或多个参数的值 [英] No value given for one or more parameters requited

问题描述

我在执行以下代码时遇到错误'没有给出一个或多个参数的值'：

I am experiencing an error 'No value given for one or more parameters required ' when executing the following code:

private void availbleproduct_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            string connstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\OBS\OBSAccounts.accdb";
            string productname = availbleproduct.Rows[e.RowIndex].Cells["prodname"].Value.ToString();
            string sql = "SELECT prodname FROM inventory WHERE prodname=" + productname + "";
            OleDbConnection conn = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader;
            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                string proname = reader["prodname"].ToString();
                prod_name.Text = proname;
            }
        }

推荐答案

不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

这可能会同时解决您的问题。

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
The chances are that that will cure your problem at the same time.

string sql = "SELECT prodname FROM inventory WHERE prodname=@PN";
using (OleDbConnection conn = new OleDbConnection(connstring))
   {
   using (OleDbCommand cmd = new OleDbCommand(sql, conn))
      {
      cmd.Parameters.AddWithValue("@PN",  productname);
      conn.Open();
      using (OleDbDataReader reader = cmd.ExecuteReader())
          {
          while (reader.Read())
              {
              ...

但请注意，这只会显示最后一个这样的值表格 - 如果有多个表格，则只显示最后一个表格。就个人而言，我会检查计数并确保只有一个。

But do note that that will display only the last such value in the table - if there are more than one, only the last one returned will be displayed. Personally, I'd check the count and make sure there was only one.

首先，不要将值直接连接到SQL语句。这使您容易受到SQL注入的攻击。而是使用 OleDbParameter [ ^ ]。



因此查询应该类似于

First of all, do not concatenate values directly to the SQL statement. This leaves you vulnerable to SQL injections. Instead use OleDbParameter[^].

So the query should look something like

string connstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\OBS\OBSAccounts.accdb";
string productname = availbleproduct.Rows[e.RowIndex].Cells["prodname"].Value.ToString();
string sql = "SELECT prodname FROM inventory WHERE prodname=@prodname";
using (OleDbConnection conn = new OleDbConnection(connstring)) {
   using (OleDbCommand cmd = new OleDbCommand(sql, conn)) {
      OleDbDataReader reader;
      conn.Open();
      adapter.SelectCommand.Parameters.Add("@prodname", OleDbType.VarChar, 100).Value = productname;

      reader = cmd.ExecuteReader();

      while (reader.Read()) {
         string proname = reader["prodname"].ToString();
         prod_name.Text = proname;
      }
   }
}

同时检查prodname变量是否包含查询的正确值（例如，不为空） 。

Also check that the prodname variable contains proper value for the query (for example isn't empty).

可能是因为你错过了引号

It might be because you're missing the quotes

string sql = "SELECT prodname FROM inventory WHERE prodname='" + productname + "'";

但是这是一个坏主意，因为它让你对sql注入攻击开放，你应该使用参数化查询。

However this is a bad idea as it leaves you open to sql injection attacks, you should use parameterised queries instead.

这篇关于没有给出一个或多个参数的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！