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:

[C#]

public static string GetConnString()

{

return WebConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;

}

[VB]

Public Shared Function GetConnString() As String

Return WebConfigurationManager.ConnectionStrings("myConnStr").ConnectionString

End Function

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:

"Insert Into Contacts (FirstName, LastName) Values (@FirstName, @LastName)"

INSERT

[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Insert Into Contacts (FirstName, LastName) Values (?,?)";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

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

{

cmd.CommandType = CommandType.Text;

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

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

conn.Open();

cmd.ExecuteNonQuery();

}

[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Insert Into Contacts (FirstName, LastName) Values (?,?)"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

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

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

conn.Open()

cmd.ExecuteNonQuery()

End Using

UPDATE

[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Update Contacts Set FirstName = ?, LastName = ?";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

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

{

cmd.CommandType = CommandType.Text;

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

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

conn.Open();

cmd.ExecuteNonQuery();

}

[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

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

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

conn.Open()

cmd.ExecuteNonQuery()

End Using

DELETE

[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Delete * From Contacts Where FirstName = ? And LastName = ?";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

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

{

cmd.CommandType = CommandType.Text;

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

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

conn.Open();

cmd.ExecuteNonQuery();

}

[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Delete * From Contacts Where FirstName = ? And LastName = ?"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

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

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

conn.Open()

cmd.ExecuteNonQuery()

End Using

SELECT

[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Select * From Contacts Where FirstName = ? And LastName = ?";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

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

{

cmd.CommandType = CommandType.Text;

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

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

conn.Open();

using (OleDbDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

{

Response.Write(reader["FirstName"].ToString() + " " + reader["LastName"].ToString());

}

[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Select * From Contacts Where FirstName = ? And LastName = ?"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

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

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

conn.Open()

Using reader As OleDbDataReader = cmd.ExecuteReader()

While reader.Read()

Response.Write(reader("FirstName").ToString() + " " + reader("LastName").ToString())

End While

End Using

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 = new OleDbConnection(ConnString))

{

using (OleDbCommand cmd = new OleDbCommand(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 As String = Utils.GetConnString()

Dim SqlString As String = "AddContact"

Using Conn As New OleDbConnection(ConnString)

Using Cmd As New OleDbCommand(SqlString, Conn)

Cmd.CommandType = CommandType.StoredProcedure

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

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

Conn.Open()

Cmd.ExecuteNonQuery()

End Using

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:

string query = "[Current Product List]";

Date Posted: 13 April 2007 22:17
Last Updated: 28 October 2008 21:32
Posted by: Mikesdotnetting
Total Views to date: 33712
Printer Friendly Version

Comments

04 January 2009 06:04 from DB Thapa

It is very helpful to me . thanks for it.

12 January 2009 10:51 from Siddhartha

Thanks A lot for the Info.

30 January 2009 16:15 from Nestor

tanks for it!!!!

01 February 2009 14:56 from Daniel Heath

Excellent, exactly what I was looking for

16 February 2009 14:18 from Waheed

HI

Thanks for this article it is very simple and Undestandable .
plz post such article and expecting more
Excellent

Best Regards

25 February 2009 10:35 from Vishal Gondil

Excellent...........
Very Useul for me...
thank you

25 February 2009 13:36 from Oli

Thanks alot also from me!!! This saved my day ;)

Best regards
oli

26 February 2009 15:36 from Jay

Perfect one!!
Thx a LOT

21 March 2009 04:30 from Butchiraju

Thanks a lot.. it is so helpful for me...

31 March 2009 11:41 from Deeno20

Hai
Thanks...
It is much more to saolve my most of the queries

10 April 2009 13:59 from Master

hey can u give any example of case matching in query

10 April 2009 17:57 from Mikesdotnetting

@Master

Access is case-insensitive

18 April 2009 13:46 from Jayesh

Thanks a lot.
This really helped me a lot

17 May 2009 19:48 from Farrukh Ehsan

it is very helpful for me .
Thanks

09 August 2009 07:36 from Igor

Thanks,really helpful and easy to understand

15 August 2009 21:37 from Chuck

When I read.ToString() on an Access memo field, it cuts it off at 255 characters. How do I get it to read all of the field? Answer in VB?

17 August 2009 07:32 from Mikesdotnetting

@Chuck

I don't see how that is relevant to the article. General ASP.NET questions should be posted to a forum, like the one at http://forums.asp.net. You are likely to get a quicker response there.

07 September 2009 14:36 from Kailash singh

it is very helpful for me .

12 September 2009 18:05 from jj

i have a question here..please help me..im a beginner in using vb/mysql..
With cmd2
.CommandText = "INSERT INTO stu_table(student_id, student_name) VALUES (@stuId, @stuName)"
.Connection = conn
.Parameters.AddWithValue("@stuId", STU_Id.Text.Trim)
.Parameters.AddWithValue("@stuName", STU_Name.Text.Trim)

it is stated that the STU_Id and STU_Name are undeclared..
i don't know where the problem is..

07 December 2009 22:00 from Ant

I so wish you had included writing a DateTime to the database too.

07 December 2009 22:38 from Mikesdotnetting

@Ant DateTimes? That deserves a topic all of its own: http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

28 December 2009 09:06 from Rashi

extremely helpful...thnx a lot

Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

Not relevant to the article
Gratuitous links to your own site or product
Anything abusive or libellous
Spam
Anything in a language I don't understand (English is best)

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Your name:

Your email address:

Your comments:

6 + 8

Mikesdotnetting

Tags

Blogs I Like

Parameter Queries in ASP.NET with MS Access

INSERT

UPDATE

DELETE

SELECT

Saved Queries

Comments