缺少必需的参数在参数化查询？ [英] Missing Required Parameter in Parameterized Query?

问题描述

我收到以下错误尝试执行以下

代码

没有值给出一个或多个必需的参数。的

 字符串paraName =CONTROL; 
串fullPathToExcel = @C：\Users\xbbjn2h\Desktop\Mapping.xlsx 
串CONNSTRING =的String.Format（@供应商= Microsoft.ACE.OLEDB.12.0;数据源= {0};扩展属性=创先争优12.0的Xml; HDR = YES;，fullPathToExcel）; 
字符串SQL =选择[功能]，[NAME]从[工作表Sheet1 $]，其中[FUNTION] =？; 
 
 OleDbConnection的康恩=新的OleDbConnection（）; 
 conn.ConnectionString = CONNSTRING; 
使用（OleDbCommand的CMD =新的OleDbCommand（SQL，康涅狄格州））
 {
 cmd.Parameters.AddWithValue（，paraName？）; 
的DataSet DS =新的DataSet（）; 
 conn.Open（）; 
 OleDbDataAdapter的DAB =新OleDbDataAdapter的（CMD）; 
 dab.Fill（DS）; 
 dataGridView1.DataSource = ds.Tables [0]; 
 conn.Close（）; 
} 
  

解决方案

詹姆斯，我可以看到，您尝试参数化，你会与 SQL 。然而的OleDb 不会遵循精确模式。在的OleDb 您可以简单地把问号在你的SQL语句和 Parameter.Add（）填补起来以后使用。值这是相当直截了当。

作为参考MSDN指出的：

的OLE DB .NET提供不不支持用于传递
参数的SQL语句或由
的OleDbCommand调用的时候CommandType设置为文本的存储过程的命名参数。

我已经重写你的代码应该看起来像

的方式

 字符串paraName =CONTROL; 
串fullPathToExcel = @C：\Users\xbbjn2h\Desktop\Mapping.xlsx 
串CONNSTRING =的String.Format（@供应商= Microsoft.ACE.OLEDB.12.0;数据源= {0};扩展属性=创先争优12.0的Xml; HDR = YES;，fullPathToExcel）; 
字符串SQL =的String.Format（SELECT [{0}]，[{1}]从[{2}]，其中[{0}] =？，strFunction，则strName，strSheetName）; 
 
 conn.ConnectionString = CONNSTRING;使用
（康涅狄格州的OleDbConnection =新的OleDbConnection（））
 {
 OleDbCommand的CMD =新的OleDbCommand（）; 
 cmd.Connection =康恩; 
 cmd.CommandText = SQL; 
 cmd.Parameter.Add（@参数1，OleDbType.VarChar）.value的= paraName; // paraName或你希望的任何值。 
 
的DataSet DS =新的DataSet（）; 
 conn.Open（）; 
 OleDbDataAdapter的DAB =新OleDbDataAdapter的（CMD）; //或cmd.ExecuteNonQuery（）;在插入SQL命令。 
 dab.Fill（DS）; 
 dataGridView1.DataSource = ds.Tables [0]; 
 conn.Close（）; 
} 
  

注意后，你应该把你的的OleDbConnection 的使用语句，而不是你的的OleDbCommand 里面。
我没有测试它，它可能包含一些小的错误。除了那一拳便在那里按PLAY（F5）。

您可以找到更多关于的OleDb 从参数化的 OleDbCommand.Parameters物业

祝你好运，让我们知道会发生什么。

I am getting the following error trying to execute the code below

No Value Given For One Or More Required Parameters.

string paraName = "CONTROL";
string fullPathToExcel = @"C:\Users\xbbjn2h\Desktop\Mapping.xlsx"; 
string connString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""",fullPathToExcel);
string sql = "SELECT [FUNCTION],[NAME] from [Sheet1$] WHERE [FUNTION] = ?";

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = connString;
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
    cmd.Parameters.AddWithValue("?", paraName);
    DataSet ds = new DataSet();
    conn.Open();
    OleDbDataAdapter dab = new OleDbDataAdapter(cmd);
    dab.Fill(ds);
    dataGridView1.DataSource = ds.Tables[0];
    conn.Close();
}        

解决方案

James, I can see that you are trying to parametrize as you would with SQL. However OleDb wouldn't follow the exact pattern. In OleDb you can simply put question marks in your sql sentence and fill them up later using Parameter.Add().value which is quite straight forward.

As MSDN reference points out:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text.

I have rewritten your code in the way it should look like

    string paraName = "CONTROL";
    string fullPathToExcel = @"C:\Users\xbbjn2h\Desktop\Mapping.xlsx"; 
    string connString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""",fullPathToExcel);
    string sql = string.Format("SELECT [{0}],[{1}] from [{2}] WHERE [{0}] = ?", strFunction, strName, strSheetName);

    conn.ConnectionString = connString;
    using (OleDbConnection conn = new OleDbConnection())
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        cmd.Parameter.Add("@Param1", OleDbType.VarChar).Value = paraName; // paraName or any value you wish.

        DataSet ds = new DataSet();
        conn.Open();
        OleDbDataAdapter dab = new OleDbDataAdapter(cmd); //or cmd.ExecuteNonQuery(); in Insert sql commands.
        dab.Fill(ds);
        dataGridView1.DataSource = ds.Tables[0];
        conn.Close();
    }       

Note, You should put your OleDbConnection inside the using statement rather than your OleDbCommand. I haven't tested it and it may contain some minor error. Other than that punch it there and press play (F5).

You can find more on OleDb parametrization from OleDbCommand.Parameters Property

Good luck and let us know what happens.

这篇关于缺少必需的参数在参数化查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！