Simple Login and Redirect for ASP.NET and Access
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] = ?"
Currently rated 4.00 by 7 people
Rate Now!
Date Posted:
01 May 2008 10:02
Last Updated:
26 June 2008 10:57
Posted by:
Mikesdotnetting
Total Views to date:
8134
Printer Friendly Version
Comments
11 April 2009 16:09 from Jayesh Jain
Hi there.
This has helped me a lot.
Thanks a lot man
11 April 2009 16:52 from 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.
15 April 2009 09:34 from 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
15 April 2009 10:34 from Mikesdotnetting
@ali
Add:
using System.Data.OleDb;
to the top of your aspx.cs file.
23 May 2009 21:56 from w00t
Thank you so much for the code
30 May 2009 21:02 from Amit
Use using System.Data.Sqlclient
30 May 2009 21:28 from Mikesdotnetting
@Amit,
SqlClient will *not* work with Access databases.
24 August 2009 20:21 from 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
24 August 2009 20:34 from Mikesdotnetting
@sp
All the code you need is in the article.
11 September 2009 16:10 from 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,
11 September 2009 23:47 from Mikesdotnetting
@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());
22 September 2009 21:59 from 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
23 September 2009 07:36 from Mikesdotnetting
@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
24 September 2009 00:12 from 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
26 September 2009 20:46 from Mikesdotnetting
@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].....
29 September 2009 16:28 from 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?
02 October 2009 14:04 from 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
31 December 2008 14:55 from Suresh
Hi this is suresh, excellent website .............