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

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

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

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

  End Using

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

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: Friday, April 13, 2007 10:17 PM
Last Updated: Tuesday, October 28, 2008 9:32 PM
Posted by: Mikesdotnetting
Total Views to date: 160630

46 Comments

Sunday, January 4, 2009 6:04 AM - DB Thapa

It is very helpful to me . thanks for it.

Monday, January 12, 2009 10:51 AM - Siddhartha

Thanks A lot for the Info.

Friday, January 30, 2009 4:15 PM - Nestor

tanks for it!!!!

Sunday, February 1, 2009 2:56 PM - Daniel Heath

Excellent, exactly what I was looking for

Monday, February 16, 2009 2:18 PM - Waheed

HI

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

Best Regards

Wednesday, February 25, 2009 10:35 AM - Vishal Gondil

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

Wednesday, February 25, 2009 1:36 PM - Oli

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

Best regards
oli

Thursday, February 26, 2009 3:36 PM - Jay

Perfect one!!
Thx a LOT

Saturday, March 21, 2009 4:30 AM - Butchiraju

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

Tuesday, March 31, 2009 11:41 AM - Deeno20

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

Friday, April 10, 2009 1:59 PM - Master

hey can u give any example of case matching in query

Friday, April 10, 2009 5:57 PM - Mike

@Master

Access is case-insensitive

Saturday, April 18, 2009 1:46 PM - Jayesh

Thanks a lot.
This really helped me a lot

Sunday, May 17, 2009 7:48 PM - Farrukh Ehsan

it is very helpful for me .
Thanks

Sunday, August 9, 2009 7:36 AM - Igor

Thanks,really helpful and easy to understand

Saturday, August 15, 2009 9:37 PM - 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?

Monday, August 17, 2009 7:32 AM - Mike

@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.

Monday, September 7, 2009 2:36 PM - Kailash singh

it is very helpful for me .

Saturday, September 12, 2009 6:05 PM - 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..

Monday, December 7, 2009 10:00 PM - Ant

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

Monday, December 7, 2009 10:38 PM - Mike

@Ant

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

Monday, December 28, 2009 9:06 AM - Rashi

extremely helpful...thnx a lot

Tuesday, February 9, 2010 11:02 AM - Gaurav

Very helping.. thanks mike.... :)

Tuesday, June 29, 2010 2:21 PM - Felix Kapya

Very useful at the right time

Monday, July 5, 2010 1:48 AM - Pragya

It was helpful in the project I am making.

Friday, July 23, 2010 6:35 AM - DRM

Why can't MS write something has helpful as this!

Many thanks from a frustrated Googler

Tuesday, January 25, 2011 1:10 AM - David

Hi Mike,

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?

Thanks
David
PS. Can someone please tell me what 4 + 8 = :-)

Tuesday, January 25, 2011 5:26 AM - 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.

PS. Glad you found a calculator, or a friend.

Friday, June 10, 2011 2:26 PM - Burton H Roberts

Very nice. You should write a book.

Friday, June 10, 2011 2:33 PM - Mike

@Burton

Thanks!

I am writing a book by the way

Saturday, July 16, 2011 7:03 AM - احمد يسري ahmed yousri

thanks . alot ,this fantastic man

thanks tooooooooooo much

Ahmed From SUDAN -north africa

Monday, July 30, 2012 10:49 AM - punitha subramanian

good

Monday, August 13, 2012 2:06 AM - syphax

Hi,
But it doesnt work with the access 2007

Tuesday, August 14, 2012 8:33 AM - Mike

@syphax,

It will work with Access 2007 without any changes to the above code whatsoever. However, you need to change the connection string to use the correct database provider: http://www.connectionstrings.com/access-2007

Friday, September 7, 2012 4:49 PM - jayr

SIR for the Insert Queries if I added some data like
"Insert Into Contacts (FirstName, LastName, Address, Contacts, . .) Values (?,?,?,?,?,?,?,?,?,?)"

it shows some the error "syntax error in insert into statement." ??

Saturday, September 8, 2012 8:44 PM - Mike

@jayr,

You didn't provide your complete SQL statement so my guess is that you have usd a reserved word for one of the fields you omitted: http://www.mikesdotnetting.com/Article/76/80040E14-MS-Access-Syntax-Error-messages. That covers MS Access, but the problem and solution is the same for other databases.

Monday, September 10, 2012 1:15 AM - jayr

thnx a lot Mikesdotnetting!!

Wednesday, January 23, 2013 4:05 PM - madhuri

thanx a lot it was very useful

Monday, January 28, 2013 11:58 AM - Søren Petersen

If you have a accdb containing "Firstname" and "Lastname", and set

Select * From Contacts Where FirstName = 'Bill' And LastName = 'Gates'

how can you get the record number (1 or more) of the selected records?

Wednesday, February 6, 2013 5:11 PM - sheshi

on point... thanks alot

Saturday, February 9, 2013 10:21 AM - pooja

super.....

Tuesday, March 19, 2013 1:48 PM - Abel Motla

Its helpfull thank you

Wednesday, April 3, 2013 5:35 PM - Derek

I am using Microsoft Access 2010 and Visual Studio 2010.

What must be done to handle the case where the user does not enter something in txtLastName.Text?

I have searched and tried several things without luck.

Like "*" does not seem to work...

Must you build the query string based on which textboxes have been filled in?

Wednesday, May 15, 2013 8:09 AM - kanchana

I need exact delete query in c#.net using MS access

Thursday, May 16, 2013 8:59 AM - Bhati

How to write store procedure in ms-access

Sunday, June 23, 2013 3:44 PM - Umair Hassan

Sir You are just Excellent ....
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 including gibberish.

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