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




        }
4
  • Your example query seems a bit wrong status = @username? Commented Mar 22, 2015 at 21:00
  • you also have one placeholder in the query but add 2 to Parameters Commented Mar 22, 2015 at 21:02
  • The example doesn't matter, but well spotted. I've not changed the query to match better. Commented Mar 22, 2015 at 21:02
  • Apologies, I have multiple queries of which one is selected depending on if one or both combo boxes are used. I didn't include this factor as to not over complicate the question. Commented Mar 22, 2015 at 21:05

2 Answers 2

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.

Sign up to request clarification or add additional context in comments.

2 Comments

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).
@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.
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

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.