1

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");
            }




        }
DeanOC
  • 7,142
  • 6
  • 42
  • 56
Dan Cundy
  • 2,649
  • 2
  • 38
  • 65

2 Answers2

4

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • I do usually use the sqlclient variant of ADO.net however I thought I'd practice using OLE DB. I was under the impression the OLE DB could be used for any database, thus having an advantage over other ADO.net types (I'm not that concerned about performance). – Dan Cundy Mar 22 '15 at 21:23
  • @DanCundy, you are partially correct. You can use OLE DB against any database, if the DBMS product includes an OLE DB provider. Some do and some don't. SQLOLEDB is has been deprecated for a decade. More databases include (at least) an ODBC driver, which is why Microsoft aligned with ODBC for relational database access. However, a .NET provider is always preferred in .NET whereas ODBC from unmanaged native code. When you can't use a .NET provider, I suggest you at least use the database interfaces (IDbConnection, IDbCommand, etc.) in your code. – Dan Guzman Mar 22 '15 at 21:32
1

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

Tito
  • 364
  • 1
  • 4
  • Please include the name of the article in the link, rather than just "please check this link" - in this case, the name of the article is on MSDN: OleDbCommand.Parameters Property. – Metro Smurf Mar 22 '15 at 21:10