Hi all!!!,
Happy Friendship day

So, please provide me help in the codding of Simple Login Page using asp.net(c#) with stored procedure.

Thanx,
With regds,
Rajendra Banker

hi bankerrajendra

First you have to Create a Storedprocure that counts the Records that match the username and password,

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
Set @Results = (Select count(*) from User_Table
where User_name =@User_name  and U_Password = @U_Password)

And after you are done with this , you have to do the Following in your C# Code

using System.Data.SqlClient;

String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB";
            SqlConnection con = new SqlConnection(strcon);
            SqlCommand cmdselect = new SqlCommand();
            cmdselect.CommandTimeout = 0;
            cmdselect.CommandType = CommandType.StoredProcedure;
            cmdselect.Connection = con;
            cmdselect.CommandText = "prclogin_check";
            cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);
            cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output;
            int Res;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Res = (int)cmdselect.Parameters["@Results"].Value;
                con.Close();

            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }

And in your Business logic or in your Form you can create a Function that will test if the Count of Records is greater than 0,if its less than that , then its invalid login

Hope this Helps

hi bankerrajendra

First you have to Create a Storedprocure that counts the Records that match the username and password,

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
Set @Results = (Select count(*) from User_Table
where User_name =@User_name  and U_Password = @U_Password)

And after you are done with this , you have to do the Following in your C# Code

using System.Data.SqlClient;

String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB";
            SqlConnection con = new SqlConnection(strcon);
            SqlCommand cmdselect = new SqlCommand();
            cmdselect.CommandTimeout = 0;
            cmdselect.CommandType = CommandType.StoredProcedure;
            cmdselect.Connection = con;
            cmdselect.CommandText = "prclogin_check";
            cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);
            cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output;
            int Res;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Res = (int)cmdselect.Parameters["@Results"].Value;
                con.Close();

            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }

And in your Business logic or in your Form you can create a Function that will test if the Count of Records is greater than 0,if its less than that , then its invalid login

Hope this Helps

Hi vuyiswamb!

Your store procedure will get more faster if we will change it to something like this:

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
IF EXISTS (Select * from User_Table
where User_name =@User_name and U_Password = @U_Password)
Set @Results =1


Here is the reason why:
http://www.sqlteam.com/article/using-exists

hi bankerrajendra

First you have to Create a Storedprocure that counts the Records that match the username and password,

Help with Code Tags
C# Syntax (Toggle Plain Text)

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
Set @Results = (Select count(*) from User_Table

where User_name =@User_name and U_Password = @U_Password)

Create Procedure prclogin_check ( @User_name varchar(20), @U_Password varchar(23), @Results int Output ) Set @Results = (Select count(*) from User_Table where User_name =@User_name and U_Password = @U_Password)

And after you are done with this , you have to do the Following in your C# Code

Help with Code Tags
C# Syntax (Toggle Plain Text)

using System.Data.SqlClient;

String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB";
SqlConnection con = new SqlConnection(strcon);
SqlCommand cmdselect = new SqlCommand();
cmdselect.CommandTimeout = 0;
cmdselect.CommandType = CommandType.StoredProcedure;
cmdselect.Connection = con;
cmdselect.CommandText = "prclogin_check";
cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);
cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output;
int Res;

try
{
con.Open();

cmdselect.ExecuteNonQuery();
Res = (int)cmdselect.Parameters["@Results"].Value;
con.Close();

}
catch (SqlException e)
{
MessageBox.Show(e.Message);
}

using System.Data.SqlClient; 
String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB"; 
SqlConnection con = new SqlConnection(strcon); 
SqlCommand cmdselect = new SqlCommand(); 
cmdselect.CommandTimeout = 0; 
cmdselect.CommandType = CommandType.StoredProcedure; 
cmdselect.Connection = con; 
cmdselect.CommandText = "prclogin_check"; 
cmdselect.Parameters.Add("@Results",SqlDbType.Int,4); 
cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output; 
int Res; 
try { 
  con.Open(); 
  cmdselect.ExecuteNonQuery(); 
  Res = (int)cmdselect.Parameters["@Results"].Value; 
  con.Close(); 
} catch (SqlException e) { 
  MessageBox.Show(e.Message); 
}

And in your Business logic or in your Form you can create a Function that will test if the Count of Records is greater than 0,if its less than that , then its invalid login

Hope this Helps

Hi vuyiswamb!

Your store procedure will get more faster if we will change it to something like this:

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
IF EXISTS (Select * from User_Table
where User_name =@User_name and U_Password = @U_Password)
Set @Results =1

I WAS MAKING A LOGIN FORM I GOT YOUR CODE BUT I HAVE PROBLEM WITH ONE LINE AS ITS GIVING AN ERROR

THE CODE WHICH GIVES ERROR IS ::

 Res = (int)cmd.Parameters["@results"].Value;

so when the user id & pass are fine i dont get any results and its fine
but if the pass nd uid does not matches it gives the error ::

"INVALIDCASTEXCEPTION WAS UNHANDELED"

instead of code

Res= (int)cmd.Parameters["@results"].Value;

i even tried

Int32 Res= Convert.ToInt32(cmdselect.Parameters["@Results"].Value);

but then i got d sub error as

"Object cannot be cast from DBNull to other types."

SO guys i think the code which u have given which is ::

cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);

above we should change this SqlDbType to some other type i think.

Please help...

Edited 3 Years Ago by mike_2000_17: Fixed formatting

HOW TO MAKE STORE PROCEDURE WITH :
USERNAME
PASSWORD AND
ROLE (ADMIN AND USER)
PLZ I HAVE this in project help me

This article has been dead for over six months. Start a new discussion instead.