This is sample code for a ASP.Net Login page (using Visual Basic.Net code behind) with OleDB connection to an Access Database using ADO.Net.
The datebase used is the Access Northwind Database. With the connection string being placed in the
web.config file.
1. Web Config File code:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!-- ||||| Application Settings ||||| -->
<appSettings>
<add key="strConn" value="Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Northwind.mdb;User ID=Admin;Password=;" />
</appSettings>
<system.web>
....
...
Authentication will be conducted by "forms" authentication set in the web.config file:
....
....
<!-- AUTHENTICATION
This section sets the authentication policies of the application. Possible modes are "Windows",
"Forms", "Passport" and "None"
"None" No authentication is performed.
"Windows" IIS performs authentication (Basic, Digest, or Integrated Windows) according to
its settings for the application. Anonymous access must be disabled in IIS.
"Forms" You provide a custom form (Web page) for users to enter their credentials, and then
you authenticate them in your application. A user credential token is stored in a cookie.
"Passport" Authentication is performed via a centralized authentication service provided
by Microsoft that offers a single logon and core profile services for member sites.
-->
<!-- ||||| MY Authentication Setup ||||| -->
<authentication mode="Forms">
<forms name="NWLogin" loginUrl="Login.aspx" />
</authentication>
<!-- AUTHORIZATION
3. Login Page Creationg (HTML Side), with form validation controls, using a summary display for an controls not passing validation.:
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Login.aspx.vb" Inherits="NorthLogin.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Northwind Database Login</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</head>
<body>
<!-- ||||| Login Form ||||| -->
<form id="frmlogin" method="post" runat="server">
<asp:label id="lblMessage" runat="server" width="288px" font-bold="True" font-italic="True"
font-size="Medium" forecolor="#C00000"></asp:label>
<table id="mainTable" style="POSITION: absolute; TOP: 30%">
<tr>
<td>
<table cellspacing="15" id="loginTable" style="BORDER-RIGHT: #6699cc solid; BORDER-TOP: #6699cc solid; FONT-WEIGHT: bold; LEFT: 30%; BORDER-LEFT: #6699cc solid; BORDER-BOTTOM: #6699cc solid; FONT-FAMILY: Sans-Serif; BACKGROUND-COLOR: lightgrey">
<tr>
<td><b>Login: </b>
</td>
<td>
<asp:textbox id="txtUserName" runat="server" width="160px"></asp:textbox>
<asp:requiredfieldvalidator runat="server" id="rvUserValidator" controltovalidate="txtUserName" errormessage="You must supply a Username!"
display="None" />
</td>
</tr>
<tr>
<td><b>Password: </b>
</td>
<td>
<asp:textbox id="txtPassword" runat="server" textmode="Password" width="160px"></asp:textbox>
<asp:requiredfieldvalidator runat="server" id="rvPasswordValidator" controltovalidate="txtPassword" errormessage="Empty Passwords not accepted"
display="None" />
</td>
</tr>
<tr>
<td align="center" colspan="2"><asp:button id="cmdSubmit" text="Submit" runat="server" borderstyle="Solid"></asp:button></td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table id="messageDisplay">
<tr>
<td><asp:validationsummary runat="server" displaymode="BulletList" id="Validationsummary1" width="472px" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
<!-- ||||| End of Form ||||| -->
</body>
</html>
4. The Code behind (in visual basic.net)
a. Imports:
Imports System.Web.Security ' ||||| Required Class for Authentication
Imports System.Data ' ||||| DB Accessing Import
Imports System.Data.OleDb ' |||||| Access Database Required Import!
Imports System.Configuration ' |||||| Required for Web.Config appSettings |||||
' ||||| Connection String - XML coded in Web.Config
' ||||| Remember to set the Security Settings in Windows on the MDB file for IUSR
b. Add the code for the button click event (in this case cmdSubmit button):
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' ||||| Put user code to initialize the page here
End Sub
Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
If Page.IsValid Then
' ||||| Connect to Database for User Validation |||||
If DBConnection(txtUserName.Text, txtPassword.Text) Then
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False) ' ||||| default.aspx Page!
Else
' ||||| Credentials are Invalid
lblMessage.Text = "Invalid Login!"
End If
End If
End Sub
c. Write the code for the DBConnection Subroutine - Beginning with variable creation, and obtaining the connection string from web.config:
' ||||| Declare Required Variables
' ||||| Access appSettings of Web.Config for Connection String (Constant)
Dim LoginSQL As String
Dim MyConn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
d. Withing DBConnection Subroutine :
' ||||| Create a OleDb Command Object
' ||||| Pass in the SQL String and Connection Object related to SQL String.
' ||||| Passing in SQL string and Connection Obj to the OleDbCommand Constructor
' ||||| Pass in Stored procedure
' ||||| Set CommandType to Stored Procedure
Dim MyCmd As New OleDbCommand("sp_ValidateUser", MyConn)
MyCmd.CommandType = CommandType.StoredProcedure
' ||||| Create Parameter Objects for values passed in
Dim objParam1, objParam2 As OleDbParameter
***Note*** The SQL Stored Procedure in Access is :
SELECT Count(*) as Num_of_Users FROM tblUsers WHERE u_Name = @UserName AND u_Password = @Password;
Continue in DBConnection Subroutine :
' ||||| Add the parameters to the parameters collection of the
' ||||| command object, and set their datatypes (OleDbType in this case)
objParam1 = MyCmd.Parameters.Add("@UserName", OleDbType.Char)
objParam2 = MyCmd.Parameters.Add("@Password", OleDbType.Char)
' ||||| Set the direction of the parameters...input, output, etc
objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
' ||||| Set the value(s) of the parameters to the respective source controls
objParam1.Value = txtUserName.Text
objParam2.Value = txtPassword.Text
' ||||| Try, catch block!
Try
' ||||| Check if Connection to DB is already open, if not, then open a connection
If MyConn.State = ConnectionState.Closed Then
' ||||| DB not already Open...so open it
MyConn.Open()
End If
' ||||| Create OleDb Data Reader
Dim objReader As OleDbDataReader
objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
' ||||| Close the Reader and the Connection Closes with it
While objReader.Read()
If CStr(objReader.GetValue(0)) <> "1" Then
lblMessage.Text = "Invalid Login!"
Else
objReader.Close() ' ||||| Close the Connections & Reader
Return True
End If
End While
Catch ex As Exception
lblMessage.Text = "Error Connecting to Database!"
End Try
End Function
Voila, compile and run. Hopefully the comments in and outside of the code give you enough of an idea on how to customize this code for your needs.
Happy coding folks! :lol:
