locked
how to write store procedure for login check RRS feed

  • Question

  • User-556790531 posted

    here  iam  having two tables  

    table1    

    table name tblLogin 

    tblLogin
    LoginId
    Name
    Password
    MemberId

                          

       

    table2  

    table name tblmembers 

     

    MemberId
    FirstName
    LastName
    Gender
    DOB
    Email
    Mobile
    Phone
    AddressLine1
    AddressLine2
    Country
    City
    ZipCode
    user type

                             

    here i just want  to  check

    username

    password

    user type 

    and after values are checked   it has to display related row

    like this

     loginid

    MemberId
    FirstName
    LastName
    Gender
    DOB
    Email
    Mobile
    Phone
    AddressLine1
    AddressLine2
    Country
    City
    ZipCode
    usertype


    Sunday, January 22, 2012 11:55 PM

Answers

  • User-1401636618 posted

     but the user  type  was mentioned in  member table

    Now Use below sp:

    CREATE PROCEDURE CheckLogin @UserName varchar(50), @Password varchar(50), @UserType varchar(50) 
    AS
    BEGIN
            DECLARE @MemberID INT
            IF EXISTS (SELECT LoginId FROM tblLogin INNER JOIN tblmembers ON tblLogin.MemberID = tblmembers.MemberID WHERE Name = @UserName AND [Password] = @Password AND tblmembers.[User Type] = @UserType)
            BEGIN
                    SELECT @MemberID = MemberID FROM tblLogin INNER JOIN tblmembers ON tblLogin.MemberID = tblmembers.MemberID WHERE Name = @UserName AND [Password] = @Password AND tblmembers.[User Type] = @UserType
                    SELECT * FROM tblmembers WHERE  MemberId = @MemberID
            END
    
    END
    

    I have changed the If Exists condition and select query..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 23, 2012 3:16 AM

All replies

  • User-372348838 posted

    Hello,

    Check out the link below.

    http://stackoverflow.com/questions/2190140/stored-procedure-in-sqlserver-user-login

    Monday, January 23, 2012 12:16 AM
  • Monday, January 23, 2012 12:59 AM
  • User-1401636618 posted
    There is no column related to userType in both tables I assume that it is exists in tbLogin table
    in database:
    CREATE PROCEDURE CheckLogin @UserName varchar(50), @Password varchar(50), @UserType varchar(50) 
    AS
    BEGIN
    	DECLARE	@MemberID INT
    	IF EXISTS (SELECT LoginId FROM tblLogin WHERE Name = @UserName AND [Password] = @Password AND UserType = @UserType)
    	BEGIN
    		SELECT @MemberID = MemberID FROM tblLogin WHERE Name = @UserName AND [Password] = @Password AND UserType = @UserType
    		SELECT * FROM tblmembers WHERE  MemberId = @MemberID
    	END
    
    END
    in c#:
    Protected void CheckLogin(string userName, string Password, string UserType)
    {
    
    	SqlConnection con = new SqlConnection("")// pass connection string here
    
    	SqlCommand cmd = new SqlCommand("CheckLogin");
    	cmd.CommandType = CommandType.StoredProcedure;
    	cmd.Parameter.Add("@UserName", SqlDBType.Varchar).Value = userName;
    	cmd.Parameter.Add("@Password", SqlDBType.Varchar).Value = Password;
    	cmd.Parameter.Add("@UserType", SqlDBType.Varchar).Value = UserType;
    	SqlDataAdapter da = new SqlDataAdapter(cmd, con);
    
    	DataTable dt = new DataTable();
    	da.Fill(dt);
    
    	if(dt.Rows.Count > 0)
    	{
    		// do your code to show data of datatable in grid or any controls if you are showing it in grid then below is code
    		GridView1.DataSource = dt;
    		GridView1.DataBind();
    		
    	} 
    	else
    	{
    		lblError.Text = "UserName, Password and UserType are not valid.";
    	}
    
    }
    Call this function to check login and fill the grid on login button click.
    Monday, January 23, 2012 2:16 AM
  • User-556790531 posted

    thanks  for  ur  comitment

     but the user  type  was mentioned in  member table

    so please help me  

    Monday, January 23, 2012 2:59 AM
  • User-1401636618 posted

     but the user  type  was mentioned in  member table

    Now Use below sp:

    CREATE PROCEDURE CheckLogin @UserName varchar(50), @Password varchar(50), @UserType varchar(50) 
    AS
    BEGIN
            DECLARE @MemberID INT
            IF EXISTS (SELECT LoginId FROM tblLogin INNER JOIN tblmembers ON tblLogin.MemberID = tblmembers.MemberID WHERE Name = @UserName AND [Password] = @Password AND tblmembers.[User Type] = @UserType)
            BEGIN
                    SELECT @MemberID = MemberID FROM tblLogin INNER JOIN tblmembers ON tblLogin.MemberID = tblmembers.MemberID WHERE Name = @UserName AND [Password] = @Password AND tblmembers.[User Type] = @UserType
                    SELECT * FROM tblmembers WHERE  MemberId = @MemberID
            END
    
    END
    

    I have changed the If Exists condition and select query..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 23, 2012 3:16 AM
  • User-556790531 posted

    any  way thanks for your work 

    but now  help me how  to add member table

    input  parameter  insert  

    store procedure

    Monday, January 23, 2012 5:03 AM
  • User-556790531 posted

    thanks  for  your job   but    in the result i didnt  find login id

    it was showing  only  tbl members  but it was not showing login  id

    please tell me  how to correct that one

    login id  from  login table

    SELECT * FROM tblmembers WHERE  MemberId = @MemberID
    Monday, January 23, 2012 5:44 AM