Parameter Queries in ASP.NET with MS Access

A selection of code samples for executing queries against MS Access using parameters.

Making use of the ASP.NET 2.0 datasource controls is fine, but it is important to understand how to manually create data access code. Best practice dictates that, at the very least, parameters are used to represent values that are passed into the SQL to be executed, rather than un-sanitised values straight from the user. The main reason for this cannot be over-emphasised in terms of its importance - it protects the application against SQL Injection attacks. In addition, parameters do not require delimiters. Therefore there is no need to worry about octothorpes (#) or apostrophes for dates, or doubling single quotes in strings.

These samples all assume that the values being passed into the parameters have been properly validated for datatype, existence, range etc, according to the business rules for the application. The serverside validation code is not included, as it will differ from app to app, and is not the focus of these samples anyway. However, it is important to stress that all user input must be validated server-side before being included in a SQL statement. Better to reject it outright, rather than have to unpick rubbish that pollutes the database...

The required components are an OleDbConnection object, a ConnectionString property, an OleDbCommand object and an OleDbParameterCollection. These all reside in the System.Data.OleDb namespace, which needs to be referenced. Also, the connection string is held in the Web.Config, and a static method GetConnString() has been created in a class called Utils (also static) to retrieve it:

For simplicity, you can replace Utils.GetConnString with a valid Access connection string such as:

"Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Northwind.mdb"

To make use of |DataDirectory| make sure that your database file is in the App_Data folder of your web site.

OleDb Parameters are recognised by their position, not by their name. Consequently, it is vital to ensure that parameters are added to the collection in the order they appear in the SQL, otherwise a "Too few parameters..." exception could occur. At the very least, your values will get inserted into the wrong fields, or nothing happens at all. For the sake of code readability, AddWithValues(string, object) can take a non-empty string giving a name to the parameter, although an empty string ("") will do.

One final note about parameter markers: in the samples below, the markers are represented by question marks ( ? ). Access (or the Jet provider) is also happy to work with SQL Server-style parameter markers that are prefixed with @, so the first example CommandText can be replaced with:

Saved Queries

The code samples above will work equally well with minimal changes for Saved Queries in MS Access. The CommandType will need to be changed to "StoredProcedure", and the name of the query needs to be passed as a string in place of the SQL statement. As an example, if a Saved Query was created in Access called AddContact, this is how the INSERT example would alter:

[C#]

string ConnString = Utils.GetConnString();

string SqlString = "AddContact";

using (OleDbConnection conn = newOleDbConnection(ConnString))

{

using (OleDbCommand cmd = newOleDbCommand(SqlString, conn))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

conn.Open();

cmd.ExecuteNonQuery();

}

}

[VB]

Dim ConnString AsString = Utils.GetConnString()

Dim SqlString AsString = "AddContact"

Using Conn AsNew OleDbConnection(ConnString)

Using Cmd AsNew OleDbCommand(SqlString, Conn)

Cmd.CommandType = CommandType.StoredProcedure

Cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)

Cmd.Parameters.AddWithValue("LastName", txtLastName.Text)

Conn.Open()

Cmd.ExecuteNonQuery()

EndUsing

EndUsing

You may end up using a legacy Access database, which has embedded spaces in the names of the queries. I know - only an idiot does this sort of thing. Well, the download version of Northwind.mdb (from Microsoft) has embedded spaces in object names... Anyway, the way to get round this is to surround the query name with [ ] brackets:

05 July 2010 01:48 - Pragya

23 July 2010 06:35 - DRM

25 January 2011 01:10 - David

good article. So this would mean if we use Parameters as descibed above, we can switch the connection string between SQl and Access and the querys will continue to run?

ThanksDavidPS. Can someone please tell me what 4 + 8 = :-)

25 January 2011 05:26 - Mike

@David,

Not necesssarily. The SQL is provider dependent, so some Access queries won't work with SQL Server, such as anything with a YESNO field - the values for true vary between the two providers. Not only that, but you have to use the OleDb provider with Access. That's not the most efficient way to connect to SQL Server.