Simple Login and Redirect for ASP.NET and Access

Quite often, I see questions asked about creating a simple login page for use with Access from people who don't want to take advantage of the built-in Forms Authentication framework within ASP.NET. The following samples show how relatively easy this is to accomplish.

The Login page consists purely of a Literal control, 2 TextBox controls and a Button control.

<form id="form1" runat="server">
<div>
    <asp:Literal ID="Literal1" runat="server" /><br />
    User Name: <asp:TextBox ID="UserName" runat="server" /><br />
    Password:  <asp:TextBox ID="Password" runat="server" /><br />
    <asp:Button ID="Login" runat="server"  Text="Log In" />
</div>
</form>

Double-click the button to generate an event-handler for the button_click event. Within that event handler, all that needs to be done is to test whether the user name and password that were supplied are valid. They are considered valid if they both exist together in any row in the Users table in the database, so we just need to find out if any rows contain both values.

Plenty of times, I have seen people construct DataSets or DataReaders to return the values being tested for. This is totally unnecessary. We just need a Count of the rows that meet the criteria being passed in the SQL, so

Select Count(*) From Users Where UserName = ? AND UserPassword = ?

is all that's needed. This will return 0 if no rows match, or a number indicating how many rows match (should ideally be 1 for user names and passwords). The ? are parameter markers, and it is vital that this sort of authentication is done using parameters. For more on parameter queries, check here. So now some logic to retrieve the number of matching rows and redirect the user if they authenticate, or show them a message if they didn't:

[VB]
Protected Sub Login_Click(ByVal sender As Object, ByVal e As System.EventArgs) 
		Handles Login.Click
  Dim connect As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
		"Data Source=|DataDirectory|contacts.mdb"
  Dim query As String
  query = "Select Count(*) From Users Where Username = ? And UserPassword = ?"
  Dim result As Integer = 0
  Using conn As New OleDbConnection(connect)
    Using cmd As New OleDbCommand(query, conn)
      cmd.Parameters.AddWithValue("", UserName.Text)
      cmd.Parameters.AddWithValue("", Password.Text)
      conn.Open()
      Session("User") = UserName.Text
      result = DirectCast(cmd.ExecuteScalar(), Integer)
    End Using
  End Using
  If result > 0 Then
    Response.Redirect("LoggedIn.aspx")
  Else
    Literal1.Text = "Invalid credentials"
  End If
End Sub
[C#]
string connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|contacts.mdb";
string query = "Select Count(*) From Users Where Username = ? And UserPassword = ?";
int result = 0;
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand(query, conn))
  {
    cmd.Parameters.AddWithValue("", UserName.Text);
    cmd.Parameters.AddWithValue("", Password.Text);
    conn.Open();
    Session["User"] = UserName.Text;
    result = (int)cmd.ExecuteScalar();
  }
}
if (result > 0)
{
  Response.Redirect("LoggedIn.aspx");
}
else
{
  Literal1.Text = "Invalid credentials";
}

Note: Avoid using "Password" as a field name in Access. It is a Jet reserved word, and will lead to a "Syntax error in Select From" message. If you find that you have to use Password, then wrap it in [ ] brakets in the SQL:

"Select Count(*) From Users Where Username = ? And [Password] = ?"

Date Posted: Thursday, May 1, 2008 10:02 AM
Last Updated: Monday, August 23, 2010 9:27 AM
Posted by: Mikesdotnetting
Total Views to date: 279879

35 Comments

Wednesday, December 31, 2008 2:55 PM - Suresh

Hi this is suresh, excellent website .............

Saturday, April 11, 2009 4:09 PM - Jayesh Jain

Hi there.

This has helped me a lot.
Thanks a lot man

Saturday, April 11, 2009 4:52 PM - Jayesh Jain

Hi there,
I am new to .Net and just went through your code and used it for login page. But i am getting the following errors:

The name 'UserName' does not exist in the current context
The name 'Password' does not exist in the current context
The name 'Literal1' does not exist in the current context

Can you help me out with this.

Wednesday, April 15, 2009 9:34 AM - ali

Hi there
I used the code in my page but it seems not working proper. Just in output I have an error like this:
----------------------------------
Compiler Error Message: CS0246: The type or namespace name 'OleDbConnection' could not be found (are you missing a using directive or an assembly reference?)

Source Error:



Line 20: string query = "Select Count(*) From Users Where Username = ? And UserPassword = ?";
Line 21: int result = 0;
Line 22: using (OleDbConnection conn = new OleDbConnection(connect))
Line 23: {
Line 24: using (OleDbCommand cmd = new OleDbCommand(query, conn))

----------------------------------------------------------------
This is the code in my Default.aspx.cs :





using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Login_Click(object sender, EventArgs e)
{
string connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|contacts.mdb";
string query = "Select Count(*) From Users Where Username = ? And UserPassword = ?";
int result = 0;
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
cmd.Parameters.AddWithValue("", UserName.Text);
cmd.Parameters.AddWithValue("", Password.Text);
conn.Open();
Session["User"] = UserName.Text;
result = (int)cmd.ExecuteScalar();
}
}
if (result > 0)
{
Response.Redirect("LoggedIn.aspx");
}
else
{
Literal1.Text = "Invalid credentials";
}

}
}





Please help me to find the problem.
thank you

Wednesday, April 15, 2009 10:34 AM - Mike

@ali

Add:

using System.Data.OleDb;

to the top of your aspx.cs file.

Saturday, May 23, 2009 9:56 PM - w00t

Thank you so much for the code

Saturday, May 30, 2009 9:02 PM - Amit

Use using System.Data.Sqlclient

Saturday, May 30, 2009 9:28 PM - Mike

@Amit,

SqlClient will *not* work with Access databases.

Monday, August 24, 2009 8:21 PM - sp

i tried this code for my page and its not working properly
once i enter name and password for every user its going on last else loop
so if possible please send me the solution for this

Monday, August 24, 2009 8:34 PM - Mike

@sp

All the code you need is in the article.

Friday, September 11, 2009 4:10 PM - Javier

This code is working fine.

If I add a field in users, called WebSite with a URL in it, how I can Redirect to it?.

Best regards,

Friday, September 11, 2009 11:47 PM - Mike

@Javier

You would have to retrieve the WebSite column value instead of a count. If the value is not null, pass it in as follows:

Response.Redirect(rdr["WebSite"].ToString());

Tuesday, September 22, 2009 9:59 PM - JW

I'm having a problem with your code. I'm getting an olDBException error, it doesn't like the name of my database table & I've changed the MSACCESS connection string to my SQL Server Connection String. Also, what is supposed to be substituted for the "?" in the query?

Thanks

Wednesday, September 23, 2009 7:36 AM - Mike

@JW

Are you trying to use the same code for SQL Server? If so, you need to change all the OleDb references to Sql eg change OleDbCommand to SqlCommand etc. Then change the SQL to this:

Select Count(*) From Users Where Username = @Username And UserPassword = @Password

Then make sure you put the names of the parameters in the AddWithValue() methods:

cmd.Parameters.AddWithValue(@Username, UserName.Text)
etc

Thursday, September 24, 2009 12:12 AM - JW

I'm still having a problem & get the error:
Incorrect syntax near the keyword 'User'.

I think it has something to do with the connection string or the query. My table is named User too not Users.

Imports System.Data
Imports System.Data.SqlClient
Imports System
Imports System.Web.Security
Imports System.Configuration

Partial Class Login
Inherits System.Web.UI.Page


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


Dim connect As String = "Data Source=XPVPC;Initial Catalog=CET4584;Integrated Security=SSPI;Persist Security Info=False;"
Dim query As String
query = "Select Count(*) From dbo.User Where UserName = @UserName And UserPassword = @UserPassword"
Dim result As Integer = 0
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@UserName", TextBox1.Text)
cmd.Parameters.AddWithValue("@UserPassword", TextBox2.Text)
conn.Open()
Session("User") = TextBox1.Text
result = DirectCast(cmd.ExecuteScalar(), Integer)
End Using
End Using
If result > 0 Then
Response.Redirect("Default3.aspx")
Else
Literal1.Text = "Invalid credentials"
End If
End Sub

End Class

Thanks for the assistance,
JW

Saturday, September 26, 2009 8:46 PM - Mike

@JW

User is a reserved word in SQL Server:

http://msdn.microsoft.com/en-us/library/aa238507(SQL.80).aspx

Ideally, you shouldn't use reserved words as field or table names. If you can't change them, wrap them in [ ] brackets in your SQL:

Select FROM [User].....



Tuesday, September 29, 2009 4:28 PM - JW

Thanks that did the trick. Would you like me to send you the SQL code so, you can put it on your site too?

Friday, October 2, 2009 2:04 PM - pradeep lakhotia

I have to replace the 2 lines of yours
Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand(query, conn)
with that of mine as
Using conn As New Data.OleDb.OleDbConnection(connect)
Using cmd As New Data.OleDb.OleDbCommand(query, conn) and it works great.
I cannot inherit the other class for your code as it give the message 'Inherit' can appear only once. Was I wrong somewhere (VB Part). anyway thatnks again

Thursday, March 22, 2012 3:37 PM - swami vv

the login coding provides by this website is very excellent it helps to me to make login page,Thanks

Thursday, August 9, 2012 5:46 PM - Christie

I am getting these errors....
The name 'txtUser.Text' does not exist in the current context
The name 'txtPass.Text' does not exist in the current context
The name 'Literal1' does not exist in the current context

Friday, August 10, 2012 7:17 AM - Mike

@Christie

You need to add textboxes and literal controls to the web form with the appropriate IDs. The errors you get tell me that you haven't done that yet.

Saturday, October 6, 2012 3:52 PM - Regina

I'm getting these,

Compiler Error Message: CS0103: The name 'connect' does not exist in the current context

Source Error:


Line 18: string query = "Select Count(*) From Users Where Username = ? And UserPassword = ?";
Line 19: int result = 0;
Line 20: using (OleDbConnection conn = new OleDbConnection(connect))
Line 21: {
Line 22: using (OleDbCommand cmd = new OleDbCommand(query, conn))

Monday, October 8, 2012 4:55 PM - Mike

@Regina,

"connect" should be a string representing your connection string. You must have omitted the first line of code in the C# sample above.

Thursday, October 11, 2012 8:25 AM - Kalpesh Thanki

Dear Sir,

i want to make login page in Asp.Net and backup on access and user can be download his excel file on their personal id and other i want to make upload excel file system in my site.

do the needful
thanks with regards,

kalpesh thanki

Thursday, October 11, 2012 8:35 AM - Mike

@Kalpesh

What do you mean "Do the needful"? Do you want me to send you a quote for writing your programme for you?

Monday, October 15, 2012 3:53 PM - Aravinth

Thank u so much for the code !!

Wednesday, December 19, 2012 3:06 PM - Christie

Thanks Mike, this worked great! I appreciate you sharing, especially for a beginner like me.

Thursday, January 31, 2013 7:02 AM - kasireddy

i learn alot... thanku sir

Monday, February 11, 2013 12:13 AM - Florin

Thanks for the easy to understand example.
Is there a way to prevent access to LoggedIn.aspx once user closes the browser? In other words to have a log out functionality?
Otherwise, this technique has a limited applicability.

Monday, February 11, 2013 4:56 PM - Gonqui

Hello, thanks for your code i´m using it! but the question is when i´m passing the validation form i´m redirecting for the default page, but the browser simply don´t leave the login page, and the dont render the needed one, can you please explain me why?

Thank you so much,

Thursday, March 21, 2013 6:42 AM - Aditya Nawandar

Excellent stuff! Just what I was looking for... Its surprising how sometimes you can get the most complicated stuff on google but have trouble finding something simple but important like this... Thanks!

Thursday, July 11, 2013 12:39 PM - Rahul Borate

Helpful to me.

Tuesday, August 27, 2013 12:13 PM - ammu

nice...

Saturday, January 4, 2014 12:04 AM - mic

i have this error when i run the code:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '?'.

Source Error:


Line 36: conn.Open();
Line 37: Session["User"] = txtusername.Text;
Line 38: result = (int)cmd.ExecuteScalar();
Line 39: }
Line 40: }

Tuesday, January 7, 2014 8:02 PM - Mike

@mic

You should be using OleDb, not SqlClient with Access.
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.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...