HOW TO CREATE LOGIN PAGE/FORM AND CHECK USERNAME,PASSWORD IN ASP.NET USING STORED PROCEDURE AND SQL SERVER DATABASE

In this example when user enter the credentials i.e. username and password and click on login button then the entered username and password will be checked from the Sql server database and if matched then the log in attempt will be successful otherwise user will get a message e.g. "Wrong Username/Password"

Notice that in stored procedure i have also used the COLLATE Latin1_general_CS_AS to check for the exact username and password match because it is used to make the sql queries case sensitive. e.g. if the username is admin and password is demo then if user enters Admin in username or Demo in password field then it will not match and the log in attempt will get failed. 

Implementation: let’s create an asp.net application to understand the concept.

First of all create a Sql server database and name it "MyDataBase" and create a table with the following fields and name it "Login_Tb"

Column Name	Data Type
Id	Int( Primary Key. So set Is Identity=True)
UserName	varchar(100)
Password	varchar(100)

• Then create a Stored Procedure to check the log in attempt as:

CREATE PROCEDURE Login_Check_Sp

                @username varchar(100),

                @pwd    varchar(100)

AS

BEGIN

                select * from Login_Tb

                where UserName COLLATE Latin1_general_CS_AS=@username

                and [Password] COLLATE Latin1_general_CS_AS=@pwd

END

• In the web.config file create the connection string to connect the asp.net web application with the sql server database as.

  <connectionStrings>

    <add name="conStr" connectionString="Data Source=lalit;Initial Catalog=MyDataBase;Integrated Security=True"/>

  </connectionStrings>

Note: Replace the Data Source and the Initial Catalog(database name) with your.

HTML Source Code

• In the <Form> tag of the design page (.aspx) place two textbox controls and a Button and a label controls and design the log in page as:

 <div>

    <fieldset style="width:280px">

    <legend>Login example in asp.net</legend>

    <table>

    <tr>

    <td>User Name: * </td><td>

        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox><br />

        <asp:RequiredFieldValidator

            ID="rfvUserName" runat="server" ErrorMessage="Please enter username"

            Display="Dynamic" SetFocusOnError="true" ForeColor="Red"

            ControlToValidate="txtUserName"></asp:RequiredFieldValidator></td>

    </tr>

     <tr>

    <td>Password: *</td><td>

        <asp:TextBox ID="txtPwd" runat="server" TextMode="Password"></asp:TextBox><br />

        <asp:RequiredFieldValidator

            ID="rfvPwd" runat="server" ErrorMessage="Please enter password"

            Display="Dynamic" SetFocusOnError="true" ForeColor="Red"

             ControlToValidate="txtPwd"></asp:RequiredFieldValidator></td>

    </tr>

     <tr>

     <td>&nbsp;</td>

    <td>

        <asp:Button ID="btnLogin" runat="server" Text="Login"

            onclick="btnLogin_Click" /></td>

    </tr>

     <tr>

     <td>&nbsp;</td>

     <td>

         <asp:Label ID="lblStatus" runat="server" Text=""></asp:Label>

         </td>

    </tr>

    </table>

    </fieldset>   

    </div>

Asp.Net C# code to create Login page/form and check for username and password

• In the code behind file (.aspx.cs) write the code on Login Button’s click event as:

First include the following namespaces

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

then write the code as:

protected void btnLogin_Click(object sender, EventArgs e)

    {

        DataTable dt = new DataTable();

        SqlDataAdapter adp = new SqlDataAdapter();    

        try

        {

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

            SqlCommand cmd = new SqlCommand("Login_Check_Sp", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@username", txtUserName.Text.Trim());

            cmd.Parameters.AddWithValue("@pwd", txtPwd.Text.Trim());

            adp.SelectCommand = cmd;          

            adp.Fill(dt);

            cmd.Dispose();

            if (dt.Rows.Count > 0)

            {

                lblStatus.Text = "Login Successfull";

                //Or in show messagebox using  ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Login Successfull');", true);

                //Or write using Response.Write("Login Successfull");

                //Or redirect using Response.Redirect("Mypanel.aspx");

            }

            else

            {

                lblStatus.Text = "Wrong Username/Password";

                //Or show in messagebox usingScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Wrong Username/Password');", true);

                //Or write using Response.Write("Wrong Username/Password");

            }  

        }

        catch (Exception ex)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! following error occured : " + ex.Message.ToString() + "');", true);

           // Response.Write("Oops!! following error occured: " +ex.Message.ToString());           

        }

        finally

        {

            dt.Clear();

            dt.Dispose();

            adp.Dispose();         

        }     

    }

Asp.Net VB code to create Login page/form and check for username and password

• Design the page as shown above in source code section but replace the line

   <asp:Button ID="btnLogin" runat="server" Text="Login"

            onclick="btnLogin_Click" />

with 

   <asp:Button ID="btnLogin" runat="server" Text="Login" />

• In the code behind file ( .aspx.vb) write the code on Login Button’s click event as:

First include the following namespaces 

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

 Then write the code as:

Protected Sub btnLogin_Click(sender As Object, e As System.EventArgs) Handles btnLogin.Click

        Dim dt As New DataTable()

        Dim adp As New SqlDataAdapter()

        Try

            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

            Dim cmd As New SqlCommand("Login_Check_Sp", con)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@username", txtUserName.Text.Trim())

            cmd.Parameters.AddWithValue("@pwd", txtPwd.Text.Trim())

            adp.SelectCommand = cmd

            adp.Fill(dt)

            cmd.Dispose()

            If dt.Rows.Count > 0 Then

                lblStatus.Text = "Login Successfull"

                'Or in show messagebox using  ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Login Successfull');", true);

                'Or write using Response.Write("Login Successfull");

                'Or redirect using Response.Redirect("Mypanel.aspx");

            Else

                lblStatus.Text = "Wrong Username/Password"

                'Or show in messagebox usingScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Wrong Username/Password');", true);

                'Or write using Response.Write("Wrong Username/Password"); 

            End If

        Catch ex As Exception

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! following error occured : " & ex.Message.ToString() & "');", True)

            ' Response.Write("Oops!! following error occured: " +ex.Message.ToString());           

        Finally

            dt.Clear()

            dt.Dispose()

            adp.Dispose()

        End Try

    End Sub