Parameterized Query Build Error

Dan Cundy

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




        }
Dan Guzman

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

JavaScript error in Parameterized query

From Dev

parameterized insert query error

From Dev

How can I dynamically build a parameterized query for RedBeanPHP 4?

From Dev

Jenkins Pipeline check if parameterized build is really parameterized

From Dev

parameterized sql query issue

From Dev

Trouble with parameterized query

From Dev

Parameterized dynamic sql query

From Dev

Changing to a Parameterized Oracle query

From Dev

How to create a parameterized query?

From Dev

Parameterized create query for pyodbc

From Dev

parameterized sql query issue

From Dev

Sqlalchemy mysql parameterized query

From Java

How to access parameters in a Parameterized Build?

From Dev

Trigger parameterized build with curl and crumb

From Dev

ORA-00904 'invalide indentifier' error when using 'MERGE INTO' with parameterized query

From Dev

SQL parameterized query not showing result

From Dev

Parameterized Query Fat Free Framework

From Dev

Using the Replace() function in a parameterized query

From Dev

Inline Parameterized Query Timing out

From Dev

Parameterized query in Postgresql with a json array

From Dev

SQL parameterized query with LIKE '% ? %' PHP

From Dev

Couchbase parameterized query with numeric values

From Dev

Use Parameterized Query with Python and MySQL

From Dev

disadvantage of parameterized query to overcome SQLInjection?

From Dev

parameterized Sql Query in R not working

From Dev

Parameterized Query Fat Free Framework

From Dev

Parameterized SQL query in R with IN clause

From Dev

Using the Replace() function in a parameterized query

From Dev

Access Sql parameterized query assistance