Google How to create Login page/form and check username,password in asp.net using stored procedure and sql server database | Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

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

Introduction: 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.


login page example using stored procedure and sql server database in asp.net

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 and Get age in years,months,days,hours and seconds from DOB.

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.

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>

C#.Net 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();         
        }     
    }

VB.Net 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."

34 comments :

  1. 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.

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

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

    ReplyDelete
    Replies
    1. 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..

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

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

      Delete
  4. 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.

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

      Delete
    2. 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?

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

      Delete
  5. 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..

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

      Delete
  6. 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.

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

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

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

      Delete
  8. please send the formauthentication in c#

    ReplyDelete
  9. Respected to all friends and senior peoples


    could you please send the form authentication

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

      Delete
  10. 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

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

    ReplyDelete
    Replies
    1. 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..:)

      Delete
  12. Hi Sir,
    Send me program of SESSION TIME OUT

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

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

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

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

    ReplyDelete
  16. 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

    ReplyDelete
  17. 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

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

    ReplyDelete
  19. 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...

    ReplyDelete
  20. 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

    ReplyDelete
    Replies
    1. Thanks for the reply & thanks for the help. i got it...

      Delete
    2. I am glad you got the solution..:)

      Delete

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..