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] = ?"

You might also like...