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:
Last Updated:
Posted by:
Total Views to date: 182667

48 Comments

- DB Thapa

It is very helpful to me . thanks for it.

- Siddhartha

Thanks A lot for the Info.

- Nestor

tanks for it!!!!

- Daniel Heath

Excellent, exactly what I was looking for

- Waheed

HI

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

Best Regards

- Vishal Gondil

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

- Oli

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

Best regards
oli

- Jay

Perfect one!!
Thx a LOT

- Butchiraju

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

- Deeno20

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

- Master

hey can u give any example of case matching in query

- Mike

@Master

Access is case-insensitive

- Jayesh

Thanks a lot.
This really helped me a lot

- Farrukh Ehsan

it is very helpful for me .
Thanks

- Igor

Thanks,really helpful and easy to understand

- 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?

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

- Kailash singh

it is very helpful for me .

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

- Ant

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

- 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

- Rashi

extremely helpful...thnx a lot

- Gaurav

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

- Felix Kapya

Very useful at the right time

- Pragya

It was helpful in the project I am making.

- DRM

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

Many thanks from a frustrated Googler

- 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 = :-)

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

- Burton H Roberts

Very nice. You should write a book.

- Mike

@Burton

Thanks!

I am writing a book by the way

- احمد يسري ahmed yousri

thanks . alot ,this fantastic man

thanks tooooooooooo much

Ahmed From SUDAN -north africa

- punitha subramanian

good

- syphax

Hi,
But it doesnt work with the access 2007

- 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

- 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." ??

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

- jayr

thnx a lot Mikesdotnetting!!

- madhuri

thanx a lot it was very useful

- 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?

- sheshi

on point... thanks alot

- pooja

super.....

- Abel Motla

Its helpfull thank you

- 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?

- kanchana

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

- Bhati

How to write store procedure in ms-access

- Umair Hassan

Sir You are just Excellent ....

- mike

If the insert statement got failed how
w can i get the error message of as string?

- Mike

@mike,

You can put the database operation in a try-catch block and get the exception's Message property in the catch block.
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 I end up deleting quite a lot. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Requests to fix your code (post a question to forums.asp.net instead, please)
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam

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

Recent Comments

kawish 4/24/2015 7:04 AM
In response to Using The Themes Helper In Razor Web Pages
Nice:)...

Mahdi 4/23/2015 9:49 PM
In response to Adding a New Row In The Razor WebGrid
Thank u very very. its useful for me...

Jerrie Pelser 4/23/2015 4:24 PM
In response to Entity Framework 6 Recipe: Storing And Managing Time
Nice article Mike! I think there is an error in the House property getter of MovieFormModel. Your...

mike 4/22/2015 4:45 PM
In response to Parameter Queries in ASP.NET with MS Access
If the insert statement got failed how w can i get the error message of as string?...

frank 4/22/2015 9:44 AM
In response to Sessions in ASP.NET 5
Mike can you write up an article showcasing the difference / upgrade paths of ASP.net Web Pages and...

Scott 4/21/2015 11:39 PM
In response to ASP.NET MVC DropDownLists - Multiple Selection and Enum Support
Excellent! Just what I need for the current application I am working on. Even to the extent that I a...

Benjamin 4/21/2015 4:57 AM
In response to Creating a Connection String and Working with SQL Server LocalDB
Hi Mike, great article. I am learning a lot. As commented @Evita, the <Key> Data Annotation is I...

Wayne Hudson 4/20/2015 9:14 PM
In response to Inline Editing With The WebGrid
Thank you very much for your articles. They've helped me a lot. How would you handle inline such...

Dan 4/20/2015 5:01 PM
In response to Sessions in ASP.NET 5
Can I ask how these sort of options are affected by the IIS configuration options when hosted in be...

Shaheen 4/20/2015 3:36 PM
In response to Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
Perfect! Saved me from wasting hours....