CODEWITHSUNDEEP
c#sql-serveroledbselectcommand
Dan Cundy
Dan Cundy

Reputation: 2849

Parameterized Query Build Error

Why aren't my parameterized variables being added to my Sql query?

I have two parametrized variables set by combobox.text which is selected by the end user.

I get the error below when trying to use a query that uses a parameterized variable.

Additional information: Must declare the scalar variable "@username"

Am I missing something?

Example Query

  SQL = "SELECT stationID, LocationName, plandate, username, status  FROM dbo.joblist WHERE username = @username and status = @status";

Code Snippet

            //Decide what query
            String SQL = SQLSelection();
            //Connection String
            String ConnString = "Data Source=dbsqlexpress; Provider=SQLOLEDB; Initial Catalog=Data; User ID=mobile; Password=PW";
            //Create  and initalize Oledbconnection object and pass connection string into it.
            OleDbConnection con = new OleDbConnection(ConnString);

            //open connection to database
            con.Open();

           //create adapter that sits inbetween dataset and datbase
            OleDbDataAdapter adapter = new OleDbDataAdapter();

            adapter.SelectCommand = new OleDbCommand(SQL,con);
            adapter.SelectCommand.Parameters.Add("@username", OleDbType.VarChar).Value = auditorCmb.Text;
            adapter.SelectCommand.Parameters.Add("@status", OleDbType.VarChar).Value = statusCmb.Text;


            //Create dataset
            DataSet dataset = new DataSet();

            using (DataTable dt = new DataTable())
            {
                adapter.Fill(dt);
                dataGridView1.AutoResizeColumns();
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

                con.Close();
                dataGridView1.DataSource = dt;

            int rowCount = rowCount = dt.Rows.Count;
            label10.Text = rowCount.ToString("n0");
            }




        }

Upvotes: 1

Views: 206

Answers (2)

Tito
Tito

Reputation: 364

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. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

MSDN:OleDbCommand.Parameters Property

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

With OLE DB (and ODBC), you need to specify ? as parameter markers in the SQL statement. These are then mapped by ordinal according to the order parameters were mapped to the collection.

SQL = "SELECT stationID, LocationName, plandate, username, status FROM dbo.joblist WHERE username = ? and status = ?;";

Avoid using OLE DB and ODBC in .NET applications. The .Net Provider for SQL Server (a.k.a SqlClient) will provide better performance from .Net Applications. Also, Microsoft has announced deprecation of OLE DB for relational database access in SQL Server.

Upvotes: 4

Related Questions