How to create Login page/form and check username,password in asp.net using stored procedure and sql server database

login page example using stored procedure and sql server database in asp.netIntroduction: In this article I am going to explain with example how to create a Log In/ Sign In page/form in asp.net in both the C# and VB.Net languages and using stored procedure and Sql server database as a back end database to store and check the authentication for the credentials i.e. username and password.


Description: In previous related articles i explained How to Create login form/page and implement remember me next time checkbox and  How to encrypt and decrypt username,password and store in Sql Server database using asp.net and Create Change password form/page in asp.net using Sql server and Stored procedure and Recover and reset the forgot/lost password using reset password link in email id and Pass parameter to stored procedure using SqlDataAdapter and check login and Ajax ModalPopupExtender example to open login form in popup window and Create drop down menu for login and signup using jQuery and CSS in asp.net and Fill Country,State,Cities in the DropDownList and fill dropdownlist with days, month and year 

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


Now over to you: 
If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates.
Previous
Next Post »

36 comments

Click here for comments
Unknown
admin
September 01, 2013 ×

Good day sir,iam new to my favourite windows /web apps developing platform.Your Snippets are very good to understand for a basic learner and very very useful in his career. Thank you.

Reply
avatar
September 01, 2013 ×

i am glad my articles helped you..Thanks for your appreciation..stay tuned and stay connected

Reply
avatar
Anonymous
admin
September 20, 2013 ×

I get the following error: Object reference not set to an instance of an object. I used the VB.Net example

Reply
avatar
September 20, 2013 ×

I suggest you to recheck your code thoroughly and try again..if still you face the problem then let me know, i will help you sort out the problem..

Reply
avatar
Anonymous
admin
September 21, 2013 ×

Thanks for the reply... I found the issue. This was very helpful. Thanks

Reply
avatar
September 21, 2013 ×

i am glad you found and solved the issue..great..keep reading :)

Reply
avatar
Unknown
admin
September 24, 2013 ×

Sir, I'm getting error at ScriptManager tag as "The 'ScriptManager' does not exist in the current context". What might be the problem.Can u please help me our from this error.

Reply
avatar
September 24, 2013 ×

Hello Nerella Sunil..Add a reference to System.Web.Extensions to the project and let me know..

Reply
avatar
Unknown
admin
September 24, 2013 ×

Sir,I added it in name space still it showing error as
"The type or namespace name 'Extensions' does not exist in the namespace 'System.Web' (are you missing an assembly reference?)"
What should I do now sir?

Reply
avatar
Unknown
admin
September 24, 2013 ×

Sir,i was getting error as
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at adp.Fill(dt); when i started debugging it..

Reply
avatar
September 24, 2013 ×

Follow the steps:
Right click on your project in solution explorer ->Add reference -> .NET -> System.Web.Extensions -> Ok

Reply
avatar
September 24, 2013 ×

Have you replaced the Data Source and the Initial Catalog(database name) as per your application in the connection string?

Reply
avatar
Unknown
admin
September 25, 2013 ×

Yeah sir!! I provided DataSource with my server name along with pc name (Sunil-PC\MyServer) and database name with the name I gave in SQL server.Still its showing same error.

Reply
avatar
September 26, 2013 ×

It seems you are having some problem while connecting with sql server..Are you able to connect sql server with your asp.net website?

Reply
avatar
Unknown
admin
October 05, 2013 ×

hi sir, i want to learn mvc .is there any Artical for that

Reply
avatar
October 05, 2013 ×

Hello srikant..i will soon post the article on MVC for beginners..so stay connected and keep reading :)

Reply
avatar
Unknown
admin
October 28, 2013 ×

please send the formauthentication in c#

Reply
avatar
Unknown
admin
October 28, 2013 ×

Respected to all friends and senior peoples


could you please send the form authentication

Reply
avatar
October 28, 2013 ×

Hi Ramesh, i will create an article on forms authentication as per your request very soon. So keep reading for more updates..:)

Reply
avatar
Anonymous
admin
October 29, 2013 ×

Hello sir I have made a login form where when a user logs in it displays
Welcome [USERNAME].
I want to know how to use another information from table user where it contains a field "course registered for it to display as follows.
Dear [USERNAME] Welcome to [COURSENAME] course.Best Of luck.
I've used session for the Username

Reply
avatar
Anonymous
admin
November 29, 2013 ×

hiii...sir your snippets are easy to understand and are very useful for a basic learner.....

Reply
avatar
November 29, 2013 ×

Thanks for appreciating my work and it is always nice to hear that my snippets helped anyone..keep reading and stay tuned for more updates like this..:)

Reply
avatar
Unknown
admin
December 11, 2013 ×

Hi Sir,
Send me program of SESSION TIME OUT

Reply
avatar
Unknown
admin
December 12, 2013 ×

How to create Login page in three attempts in asp.net c#.

Reply
avatar
Mohsin Azam
admin
December 25, 2013 ×

hello sir plz do some work on "Jquery images slideshow in masterpage asp.net"...

Reply
avatar
December 25, 2013 ×

Hello Mohsin Azam..i will create an article as per your requirement..so stay connected and keep reading..:)

Reply
avatar
Prince
admin
January 04, 2014 ×

Sir,can you please help how to create database . . And liked it with your website

Reply
avatar
Anonymous
admin
February 06, 2014 ×

Hello sir, I found lots of you code use stored procedure. it this good approach. It had to learn or not. I am wondering, why you need create a login table. You should find the exit users from registration table. Could tell me how it works.
Thanks

Reply
avatar
Anonymous
admin
March 16, 2014 ×

hello sir my question is different how can we bind country state and city into 3 dropdownlist control that means all country state and city in the world if we select country then we have to allow to select state of that country like that way for the city
plz help me my ID=Desaimandar2@gmail.com

Reply
avatar
Unknown
admin
March 29, 2014 ×

con open is not required..............in C# file please help im new to this

Reply
avatar
Unknown
admin
May 02, 2014 ×

Hi brother, i just had a small enhancement that if suppose the login is used by 5 room mates with different usernames and for the next time if we click the username textbox previously entered usernames appeared in the drop down if it is possible to do, please help me in this...

Reply
avatar
May 04, 2014 ×

Hello varun..i suggest you to read the article as per your requirement..
How to Disable/Turn off browser autocomplete feature in TextBox in asp.net
http://www.webcodeexpert.com/2013/10/how-to-disableturn-off-browser.html

Reply
avatar
Unknown
admin
May 04, 2014 ×

Thanks for the reply & thanks for the help. i got it...

Reply
avatar
May 06, 2014 ×

I am glad you got the solution..:)

Reply
avatar
Anonymous
admin
December 17, 2014 ×

sir,,,in mvc2 web application it gives you whole package of username and password form and register form but my question is how to get dabase connectivity for that register form and username form

Reply
avatar
Unknown
admin
December 31, 2014 ×

I need same example using sessions and three tier sir

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..