Article discussion for:
Using parameterized SQL queries

Hi Jeff,

I am just beginning to work with parameterized queries and unforunately I'm starting using Access as my database. I am having particular problems with date comparison queries such as the following:

SELECT * FROM Calendar WHERE BeginDate > @FirstDay

The result is backwards! In other words, I am getting all dates that are LESS THEN the @FirstDay value. This happens within Access itself as well. The only way I can get it to work within Access is to do the following:

SELECT * FROM Calendar WHERE BeginDate > DateValue('12/1/2004')

Unfortunately, I can't figure out how to parameterize a query in vb.net that will get the correct result.

I have your access.cs file from PopForums and I've been using it as a guide, and it's been very helpful. What are some of the quirks of working with Access that I should expect?

One that I've already come across has to do with Memo fields. I couldn't get a parameterized INSERT statement to run if the Memo field wasn't first in the list.

Thanks in advance,
Stuart

I think with Access you simply use question marks, and then add the parameters in order. So your SQL would be:

SELECT * FROM Calendar WHERE BeginDate > ?

A regular Access user could probably back me up on this. I'm not positive.

Jeff 'Jones' Putz
POP World Media, LLC
Maximizing ASP.NET

Thanks. Actually I was using the question marks so that wasn't the problem. I gave up trying to get it to work myself. Instead, I downloaded Pete Wilson's ORMapper assembly which is completely database agnostic. I pass the same code to the methods regardless of the database on the back end. A great tool for $50.

I'm trying to do a parameterised query using the following code

in c# below... the db is oracle8i....

the code below is not working at all... any ideas

string objConn = "Provider = MSDAORA;User ID=103109798;Password=password;Data Source=orabis;";
OleDbConnection myConnection = new OleDbConnection(objConn);
string commandString = "INSERT INTO users(username,password)VALUES(:username,:password)";
OleDbCommand myCommand = new OleDbCommand(commandString, myConnection);
myCommand.Parameters.Add(":username", txtUsername.Text)
myCommand.Parameters.Add(":password", txtPassword.Text)
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

By "not working" you mean what? Do you get an error? I've not used Oracle.

Jeff 'Jones' Putz
POP World Media, LLC
Maximizing ASP.NET

the page just hangs, it does not do anything,

basically i want the user to be able to enter their username

with a ' or ; without affecting the sql statement.

First off, I'd use the Oracle classes instead of OleDb. Second, I don't know how you specify parameters in Oracle, but if the page doesn't appear to be doing anything then you should try to use whatever tools Oracle has to see what chatter is going on between the site and the database.

Jeff 'Jones' Putz
POP World Media, LLC
Maximizing ASP.NET

Ace, did you get it to work? I've come across this before and like Jeff said, use the Oracle provider (you'll need to add a reference to it.)

Then import it: system.data.oracleclient

I've had to develop w/ asp.net and Oracle 8i before, and if you have questions, feel free to email me.

Ciao!

If you're like me, and use MySQL, the code is like this...

Parameters must be added in the order in which they appear in the query string.

d8

The cause of the problem is:
The POP server is out of Coke .

Does anyone know if it's possible to do a "Like" query using parameters?

Select * from table where field1 like @Param1

?

Thanks,

D8

The cause of the problem is:
The POP server is out of Coke .

SELECT * FROM table WHERE field LIKE '%' + @param + '%'

Jeff 'Jones' Putz
POP World Media, LLC
Maximizing ASP.NET