SQL - OleDbCommand不改变SQL参数

Below is the code for my Select * Function - It WORKS well and does everything great until i change the SQL string from Select * From Company to

下面是我的Select *函数的代码——它工作得很好，做任何事情都很好，直到我将SQL字符串从Select *改为

        query = "Select * From @1";

and then do the following

然后做下面的

        query = "Select * From @1";
        OleDbCommand Command = new OleDbCommand(query, sqlConnStr);

        DataTable Table = new DataTable();
        DataSet dataSet = new DataSet();
        Table = null;

        //Add Parameters
        Command.Parameters.AddWithValue("@1", SQLTables.Company);

        try
        {
            Command.ExecuteNonQuery();
            adapter.SelectCommand = Command;
            adapter.Fill(dataSet);
            Table = dataSet.Tables[0];
        }
        catch (Exception e)
        {
            MessageBox.Show("A Error occured whilst trying to execute the command.\n" + e.Message);
        }

        return Table;

The DBMS keeps sending back "Query incomplete" - I assume The Command variable is sending the string query through without changing the Parameter from @1 to Company

DBMS不断返回“查询不完整”——我假设命令变量是在不将参数从@1更改为Company的情况下发送字符串查询

Here is a piece of code (mine) where this does work. This is an insert statement rather that a select - Correct me if i am wrong but should it not also work with the SELECT aswell

这是一段代码(我的)，它在其中起作用。这是一个插入语句，而不是一个select - Correct me(如果我错了)，但它是否也适用于select

private void MainActionsInsert(string Action, bool Checked)
{
    OleDbCommand Command = new OleDbCommand("INSERT INTO MainActions Values (ID, Action, BoolValue)", DataBaseConnection);
    //Add Parameters
    Command.Parameters.AddWithValue("ID", GenerateID());
    Command.Parameters.AddWithValue("Action", Action);
    Command.Parameters.AddWithValue("BoolValue",Checked);
    //Add Command
    MainActionsAdapter.InsertCommand = Command;
    //Execute Agains DataBase
    Command.ExecuteNonQuery();
    //Accept Changes
}

”

2 个解决方案

#1

OleDbCommand Does accept Parameterized SQL just not in the From Clause - It Has to be either in a WHERE clause or something like that. Like you said it Worked with the insert function because it expects "parameters" there. For example this will work

OleDbCommand确实接受参数化SQL，只是不在From子句中——它必须在WHERE子句或者类似的东西中。就像你说的，它使用insert函数是因为它需要参数。例如，这是可行的

    query = "Select * From Company Where @param = 1";
    OleDbCommand Command = new OleDbCommand(query, sqlConnStr);

    DataTable Table = new DataTable();
    DataSet dataSet = new DataSet();
    Table = null;

    //Add Parameters
    Command.Parameters.AddWithValue("param", "ID");

    try
    {
        Command.ExecuteNonQuery();
        adapter.SelectCommand = Command;
        adapter.Fill(dataSet);
        Table = dataSet.Tables[0];
    }
    catch (Exception e)
    {
        MessageBox.Show("A Error occured whilst trying to execute the command.\n" + e.Message);
    }

    return Table;

Funny though that it doesn't work for the Select part though

有趣的是，它对Select部分不起作用

#2

OLEdb doesn't recognize named parameters. You must use ? in the query text.

OLEdb不认识命名参数。你必须使用吗?在查询文本。

However, you also can't use dynamic table names with parameterized queries, so even using a ? will not help.

但是，您也不能使用带有参数化查询的动态表名，所以即使使用a ?不会有帮助。

You need to use full dynamic SQL, though that can open you up to SQL Injection. Make sure you read the full article I linked.

您需要使用完整的动态SQL，尽管这可能会让您接受SQL注入。请务必阅读我链接的全文。

#1

    query = "Select * From Company Where @param = 1";
    OleDbCommand Command = new OleDbCommand(query, sqlConnStr);

    DataTable Table = new DataTable();
    DataSet dataSet = new DataSet();
    Table = null;

    //Add Parameters
    Command.Parameters.AddWithValue("param", "ID");

    try
    {
        Command.ExecuteNonQuery();
        adapter.SelectCommand = Command;
        adapter.Fill(dataSet);
        Table = dataSet.Tables[0];
    }
    catch (Exception e)
    {
        MessageBox.Show("A Error occured whilst trying to execute the command.\n" + e.Message);
    }

    return Table;