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

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

• Tweet
• Share
• Share
• Share

Related Post