Oledb Parameterized Query

OLEDB Parameterized Query

In my test program, the ds.Tables[0].Rows.Count datatable actually had 1 row returned (as there was one row in my test database that matched the query itself). If you put a break on this line you should be able to see whether or not data is getting into the datatable in the first place. Try this:

dataGridView1.DataSource = ds.Tables[0];

What does the front end binding of dataGridView1 look like? Running the query in Access could shed some light on the situation too.

Parameterized query in C# OleDb. Incomplete query issue

You will have to concatenate the SQL and the table name, like:

cmd.CommandText = "insert into " + Data + " ([Firstname],[Lastname]) values (?,?)";

Parameterized query for inserting values

A single parameter for the Add object is expecting an OleDBParameter object. You are just passing strings and data.

A simple fix would be to use the AddWithValue method:

oleDbCommand1.Parameters.AddWithValue("?", txtquotationno.Text);
oleDbCommand1.Parameters.AddWithValue("?", cmbjobcode.Text);

OleDB does not really use parameter names, it's index based, which is why you can pass the question mark for each one of your parameters as the name. You do have to make sure your parameters are in the same order as your query statement.

Syntax for adding parameters to a query

Obviously parameterizing queries is important to help prevent you from sql-injection. Different databases use different constructs to use parameters. For example SQL-Server uses the "@" as a place-holder for a parameter but DOES allow for parameters to be named... likewise, others may use ":" also as a named-place-holder. However, with OleDB, it uses a single "?" as the place-holder and is NOT named. Therefore, you must add your parameters in exact order as they represent in your query command.

Also, I have found instances when using NAMED parameters, that if you have a column and parameter by the same, it might not work as intended as the database might resolve itself by the column -- which is not the intended purpose. Rename the parameter with some prefix (or suffix) to help clarify. Such example might be..

sqlCommand.CommandText = "update MyCustomerTable set email = @email where customerID = @customerID";

sqlCommand.Parameters.AddWithValue("email", "someValue@anywhere.com");
sqlCommand.Parameters.AddWithValue("customerID", someIDValue );

Also note.. you don't actually include the "@" or ":" for named parameters. The engines will know how to handle them.

all looks good, but to prevent ambiguity if the "parameter" is not found and it falls back on the column name, try..

sqlCommand.CommandText = "update MyCustomerTable set email = @parmEmail where customerID = @parmCustomerID";

sqlCommand.Parameters.AddWithValue("parmEmail", "someValue@anywhere.com");
sqlCommand.Parameters.AddWithValue("parmCustomerID", someIDValue );

This way there is no confusion.

Now, back to the "?" place-holder. If you have a single parameter value that is applied multiple times, you need to add the parameter for each instance. If named parameters are allowed, you might get away with..

    sqlCommand.CommandText = 
@"update SomeTable
   set Rate1 = Rate1 * @newRateFactor,
       Rate2 = Rate2 * @newRateFactor";

    sqlCommand.Parameters.AddWithValue("newRateFactor", 1.15);

Notice only a single named-parameter is required... but with the "?", you have to add it EACH TIME

    sqlCommand.CommandText = 
@"update SomeTable
   set Rate1 = Rate1 * ?,
       Rate2 = Rate2 * ?";

    sqlCommand.Parameters.AddWithValue("ratePlaceHolder1", 1.15);
    sqlCommand.Parameters.AddWithValue("ratePlaceHolder2", 1.15);

Doing things like sql inserts and updates can also get tricky when you have a bunch of parameters for all column names. You can still give a parameter NAME value, but it must be in the same ordinal position within the query for execution.

.NET OleDb parameterized query not working as expected

You need to reverse the order in which you add the parameters to the OleDbCommand object. OleDb allows us to assign names to parameters but it ignores the names and only pays attention to the order in which the parameters appear in the CommandText.

Therefore, since your SQL statement refers to Htype and then Hdate you need to add the parameters in that same order.

Parameterized Query Build Error

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.

OleDbcommand: How to use 'In' - clause with parameters?

I think you need to change this line:

and ([Position] in @LineNumbers or @LINENUM ='')

to something like this in C#:

string LineNumber = "('2','3','8')"; // build this string dynamically

string sqlCondition = " and ([Position] in " + LineNumbers + " or @LINENUM ='')";

then concatenate the sqlCondition string to your main SELECT string.

If you want the complete example then post your complete code snippet which runs the SQL query via OleDB.

Using Parameters with OleDbDataAdapter in C#

See http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx:

"The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters."

So you cannot use the @Parameter syntax, you have to indicate parameters with question marks, and assign your parameter values in the exact same sequence as they appear in the query.

How to use parameterized queries correctly?

This is working for me:

string query = "SELECT * FROM mytable WHERE db_id=@ID";
var command = new OleDbCommand(query, connection);
command.Parameters.Add("@ID", OleDbType.BSTR);
command.Parameters[0].Value = pair.Value;

---

Related Topics