How to pass parameter to stored procedure using SqlDataAdapter and check login in asp.net

IntroductionIn previous articles i explained How to create Login page/form and check username,password in asp.net using stored procedure and sql server database and Create Change password form/page in asp.net using Sql server and  How to bind gridview using SqlDataAdapter, SqlCommand, DataTable and Stored procedure in Asp.net and How to bind gridview using SqlDataAdapter, DataTable and query in Asp.net.
As we know Stored procedures are always better than inline queries. Here in this article i have explained an example of login form where user has to enter username and password and his authentication is checked by passing his username and password to stored procedure.Lets create this application:
  • First Create a table in Sql server Database having USERNAME and PASSWORD column and name it "ADMIN_LOGIN" then Create a stored procedure in sql server as:

CREATE PROCEDURE CHECK_ADMIN_LOGIN   
                        @USERNAME VARCHAR(50),
                        @PASSWORD VARCHAR(50)
AS
BEGIN
 SELECT * FROM ADMIN_LOGIN WHERE USERNAME COLLATE Latin1_general_CS_AS =@USERNAME AND [PASSWORD] COLLATE Latin1_general_CS_AS=@PASSWORD           
END
  • Now in web.config file add the connection string under <configuration> element tag :

<connectionStrings>
    <add name="EmpCon" connectionString="Data Source=LocalServer;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>
 
  • Now in design page(.aspx) the place two TextBox for entering username and password and a Button control for submitting.
        <table>
            <tr>
                <td>
                    UserName</td>
                <td>
                    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Password</td>
                <td>
                    <asp:TextBox ID="txtPwd" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="btnLogin" runat="server" onclick="btnLogin_Click"
                        Text="Login" />
                </td>
            </tr>
        </table

C#.Net code to pass parameter to stored procedure using SqlDataAdapter and check login in asp.net
  • Now in code behind file(.aspx.cs) write the code:
    protected void btnLogin_Click(object sender, EventArgs e)
    {      
        DataTable dt=new DataTable();
        try
        {        
            SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
            SqlDataAdapter adp=new SqlDataAdapter("CHECK_ADMIN_LOGIN",con);
            adp.SelectCommand.CommandType=CommandType.StoredProcedure;
            adp.SelectCommand.Parameters.Add("@USERNAME",SqlDbType.VarChar,50).Value=txtUserName.Text.Trim();
            adp.SelectCommand.Parameters.Add("@PASSWORD",SqlDbType.VarChar,50).Value=txtPwd.Text.Trim();
            adp.Fill(dt);

            if (dt.Rows.Count>0)
            {
                Response.Write("Login Successfull.");
            }
            else
            {
                Response.Write("Invalid username or passwrod.");
            }
        }
        catch(Exception ex)
        {
            Response.Write("Error occured : " + ex.ToString() );
        }
        finally
        {
            dt.Clear();
            dt.Dispose();         
        }
    }


VB.Net Code to pass parameter to stored procedure using SqlDataAdapter and check login in asp.net
  • Now in code behind file(.aspx.vb) write the code:
Protected Sub btnLogin_Click(sender As Object, e As EventArgs)
               Dim dt As New DataTable()
               Try
                               Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)
                               Dim adp As New SqlDataAdapter("CHECK_ADMIN_LOGIN", con)
                               adp.SelectCommand.CommandType = CommandType.StoredProcedure
                               adp.SelectCommand.Parameters.Add("@USERNAME", SqlDbType.VarChar, 50).Value = txtUserName.Text.Trim()
                               adp.SelectCommand.Parameters.Add("@PASSWORD", SqlDbType.VarChar, 50).Value = txtPwd.Text.Trim()
                               adp.Fill(dt)

                               If dt.Rows.Count > 0 Then
                                              Response.Write("Login Successfull.")
                              Else
                                              Response.Write("Invalid username or passwrod.")
                               End If
               Catch ex As Exception
                               Response.Write("Error occured : " & ex.ToString())
               Finally
                               dt.Clear()
                               dt.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 and stay connected for more technical updates."
Previous
Next Post »

7 comments

Click here for comments
Anonymous
admin
September 09, 2013 ×

Hi,I have a doubt on your code.Password is basically case-sensitive;Let's take an example as follows

UserName - raja
password - Raja

i can login if if i enter the password as[raja,rAjA,rAJA,rajA] so the above code is not secured one.

Please change the above code style so this wil be useful for beginners.


Reply
avatar
September 09, 2013 ×

hi, Thanks for the suggestion. Actually i have implemented the case sensitivity using COLLATE Latin1_general_CS_AS for the password in my articles
http://www.webcodeexpert.com/2013/09/how-to-create-login-formpage-and.html

http://www.webcodeexpert.com/2013/08/how-to-recover-and-reset-forgotlost.html

Reply
avatar
September 09, 2013 ×

I have also made the changes in this article to make the password case sensitive and thus secured.

Reply
avatar
Anonymous
admin
November 15, 2013 ×

hello sir,
want to asking you. i have problem to update my data using store procedure.
can i have your email..i want to send my source code to you. please help me.
i have facing this problem almost 1 week..
tq

Reply
avatar
November 15, 2013 ×

Hi, send me your project on lalit2425@gmail.com..

Reply
avatar
sheismeggie
admin
April 04, 2014 ×

hi sir, i have one question, how to execute the sitemap sql in webconfig?

Reply
avatar
Unknown
admin
June 04, 2014 ×

sir pease use the data access layer not write code at the button click

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