SQL Variation on the above Simple Login Page in ASP.Net (w/ VB.Net)
** I am using Visual Studio.Net and SQL Server 2000, but the principles are still the same regardless of your IDE tools ***
1. Follow the above steps for creation of the Application and Page
a. Created a WebForm called the ASP.Net Page Login.aspx
b. Coded the WebForm in HTML
2. Modify the Web.Config file and add the connection string to SQL Server(as was above..BUT DIFFERENT!)
- This assumes your SQL Server is located locally on your system, and not remotely.
- Add these lines of code the
Web.Config file just after
<configuration>.
Web.Config:
<!-- ||||| Application Settings ||||| -->
<appSettings>
<add key="strConn" value="Data Source=(local);database=Northwind;User id=Paladine;Password=;"/>
</appSettings>
<system.web>
- If the server is located remotely, then there is a slight modification to the connection string to be made
Remote Connection:
<appSettings>
<add key="strConn" value="Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=Paladine;Password=;"/>
</appSettings>
- Data Source is the IP Address of the Server hosting the SQL Server DB
- 1433 is the standard port for SQL Server
3. Declare the Imports required
- These give you access to the libraries and data access methods you need to connect the Login Page to the data base.
Imports:
Imports System.Web.Security ' |||||| Required Class for Authentication
Imports System.Data ' |||||| DB Accessing Import
Imports System.Data.SqlClient ' |||||| SQL Server Import
Imports System.Configuration ' |||||| Required for Web.Config appSettings |||||
4. As was done in the above example for access, create the OnClick Event code for the Submit Button
- I am using the codebind, rather than the script method of coding the Visual Basic Portion of the Application.
- Once this button is pressed, the ASP.Net Page will validate the data provided by the user before it transmits anything to the server.
- If the date meets the validation criteria, then the event will call the DBConnection Function.
- But before we get to the
DBConnection Function, here is the code for the
OnClick Event
OnClick_Event:
Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
If Page.IsValid Then ' ||||| Meaning the Control Validation was successful!
' ||||| Connect to Database for User Validation |||||
If DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()) Then
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False) ' ||||| default.aspx Page!
Else
' ||||| Credentials are Invalid
lblMessage.Text = "Invalid Login!"
End If
End If
End Sub
5. Create the table that stores the User Login Credentials
- This can be easily done in Visual Studio.NET or vial query analyzer.
- Use which ever method you want, I will show the code as you would enter it in query analyzer
Table Script:
CREATE TABLE NorthWindUsers
(UserID INT IDENTITY(1,1) NOT NULL,
UserName VARCHAR(50) NOT NULL,
Password VARCHAR(50) NOT NULL)
6. Insert some data into the newly created table.
- This is just for teaching purposes, but you could wrap this code up into a Store Procedure and use it in a Add New User page in your application.
Insert Script:
INSERT INTO NorthWindUsers
(UserName, Password)
VALUES ('Admin', 'Admin')
7. As in the AccessDB example you must create the stored procedure which takes the values pass to it and validates them against the database.
- The values being passed to the store procedure are the User Name and Password.
- The store procedure can be created by two methods with my setup :
a. In Visual Studio
- Create a server connection to your SQL Server in the Server Explorer.
- Once you have this connection select the
Northwind Database
- In the Visual Studio.Net IDE select the D
atabase Menu
->New Stored
Procedure
- Follow the code sample below to create the stored procedure. **DO NOT Forget to Rename the default stored procedure name
i.e.StoreProcedure1**
b. In Query Analyzer
- Create the stored procedure as by following the sample code below
Things to note:
/* JUST TO SHOW YOU A DIFFERENCE BETWEEN Visual Studio.Net and Query Analyzer */
CREATE PROCEDURE dbo.sp_ValidateUser /* How it would appear in Visual Studio.Net */
CREATE PROCEDURE sp_ValidateUser /* How it would appear in QUERY ANALYZER */
The Store Procedure Script:
CREATE PROCEDURE sp_ValidateUser /* How it would appear in QUERY ANALYZER */
(
@UserName VARCHAR(50) = NULL,
@Password VARCHAR(50) = NULL,
@Num_of_User INT = 0
)
AS
SET @Num_of_User = (SELECT COUNT(*) AS Num_of_User
FROM NorthWindUsers
WHERE UserName = @UserName AND Password = @Password)
RETURN @Num_of_User
8. As in the AccessDB Example of this login page you need to create the DBConnection Function
- You will have to modify the code from the AccessDB example to work for SQL server.
- You will declare a new parameter, add it to the parameter colleciton, and have it set to receive the return value from the stored procedure.
- Determine if the return value is greater than 0 (meaning the SQL DB found the user based on the creditenials provided).
DBConnection Function:
Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean
'<sumamry>
' ||||| Declare Required Variables
' ||||| Access appSettings of Web.Config for Connection String (Constant)
'</summary>
' ||||| This is the Connections Object for an SQL DB
Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
'<sumamry>
' ||||| Create a OleDb Command Object
' ||||| Pass in Stored procedure
' ||||| Set CommandType to Stored Procedure
'</summary>
' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object
Dim MyCmd As New SqlCommand("sp_ValidateUser", MyConn)
MyCmd.CommandType = CommandType.StoredProcedure
' ||||| Create Parameter Objects for values passed in
Dim objParam1, objParam2 As SqlParameter
' ||||| Create a parameter to store your Return Value from the Stored Procedure
Dim objReturnParam As SqlParameter
'<sumamry>
' ||||| Add the parameters to the parameters collection of the
' ||||| command object, and set their datatypes (OleDbType in this case)
'</summary>
objParam1 = MyCmd.Parameters.Add("@UserName", SqlDbType.VarChar)
objParam2 = MyCmd.Parameters.Add("@Password", SqlDbType.VarChar)
objReturnParam = MyCmd.Parameters.Add("@Num_of_User", SqlDbType.Int)
' ||||| Set the direction of the parameters...input, output, etc
objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
objReturnParam.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
'' ||||| 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()
MyCmd.ExecuteNonQuery()
End If
' ||||| Was the return value greater than 0 ???
If objReturnParam.Value < 1 Then
lblMessage.Text = "Invalid Login!"
Else
Return True
End If
' ||||| Close the Connection Closes with it
MyConn.Close()
Catch ex As Exception
lblMessage2.Text = "Error Connecting to Database!"
End Try
End Function
9. Create the default.aspx page (as in the above AccessDB example)
- This is the default page the application will go to following a successful login.
Your SIMPLE (kind of) SQL Login Page is COMPLETE! Compile and Run it, and if you followed my steps it will work!
Hope this helps everyone, and happy coding.
Disclaimer:
There is an even simplier version of this login page, down to the bare minimum of code. But I went the route I did for reasons of expanding, and eventually a tutorial on making this login code into a User Control that you can implement in a number of applications. Thus not having to recode this everytime you do a new application requiring a login.
