Simple Login and Redirect for ASP.NET and Access

3.97 (35 votes)

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

39 Comments

- Suresh

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

- Jayesh Jain

Hi there.

This has helped me a lot.
Thanks a lot man

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

- 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

- Mike

@ali

Add:

using System.Data.OleDb;

to the top of your aspx.cs file.

- w00t

Thank you so much for the code

- Amit

Use using System.Data.Sqlclient

- Mike

@Amit,

SqlClient will *not* work with Access databases.

- 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

- Mike

@sp

All the code you need is in the article.

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

- 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());

- 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

- 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

- 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

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



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

- 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

- swami vv

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

- 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

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

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

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

- 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

- Mike

@Kalpesh

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

- Aravinth

Thank u so much for the code !!

- Christie

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

- kasireddy

i learn alot... thanku sir

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

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

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

- Rahul Borate

Helpful to me.

- ammu

nice...

- 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: }

- Mike

@mic

You should be using OleDb, not SqlClient with Access.

- Bilal Yaqoob

Love Love Love sir...

- AbdGaniu Dosunmu

Thanks, for the simplification of the program

- Roberto

ty ty ty man!! is this the only code, worked for me!

You help my life XD

- sara

Hi there,

I am trying to validate and check for inputs entered. When I don't enter any inputs and click the submit button
I am having this error:
"Data type mismatch in criteria expression." and points me to the result = (int)cmd.ExecuteScalar();

what should I do? thanks

Recent Comments

Praveen 12/02/2016 14:22
In response to Migrating Classic ASP To ASP.NET Razor Web Pages Part One- Razor Syntax And Visual Basic
Nicely written article, just what I wanted to get me started, I am going to start working on a this...

Whitney W. 11/02/2016 15:37
In response to Adding A Controller
I am really new to everything and just started programming. I really need help in my project since I...

Fredrik 11/02/2016 13:10
In response to Request.Form Is Empty When Posting To ASPX Page
It worked. Thank you!...

David Valdez 11/02/2016 03:08
In response to Reading Excel Files Without Saving To Disk In ASP.NET
Muchas, muchas, gracias. Thank you so much from Dominican Republic....

Zahid 10/02/2016 00:42
In response to How To Send Email In ASP.NET MVC
Hello Sir, Great post. Just a quick question, is it possible if we can ask a client to save in an...

Al Wilton 07/02/2016 03:11
In response to Windows Authentication With ASP.NET Web Pages
I've been using this advise for quite a while. Today I was setting up a .NET 4.6.1 site and it drove...

Anders 06/02/2016 15:38
In response to iTextSharp - Working with Fonts
Thanks a lot for this excellent series on iTextSharp....

J_R 06/02/2016 02:45
In response to Simple File Download Protection with ASP.NET
Mike, Thank you for taking the time to write this. It really helped me - though I could not out a...

Nemat 04/02/2016 16:24
In response to Solved - The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine
Installing Microsoft Access Database Engine 2010 64 bit helped me. Thanks A lot!...

Bill Barbour 03/02/2016 18:34
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Wonderful example. I have it all working. I would like to add the image to each row of the index you...