Click here to Skip to main content
15,899,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
C#
public void Authenticate(string Username, string Password)
        {
            string Encryptpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(txtpassword.Text, "SHA1");
            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; 
            using(SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("sptblUserAuthentication", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Username", txtuname.Text);  
                cmd.Parameters.AddWithValue("@Password", Encryptpassword);
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    int retryattempts = Convert.ToInt32(dr["retryattempts"]);             
                    if(Convert.ToBoolean(dr["Accountlocked"]))
                    {
                        Label1.Text = "Account locked Please contact Administrator";
                    }                                         
                    else if (retryattempts > 0)
                    { 
                        int Attemptsleft = (4 - retryattempts);
                        Label1.Text = "Invalid Username or Password" + Attemptsleft.ToString() + "Attemptsleft";
                    }

                    else if (Convert.ToBoolean(dr["Authenticated"]))
                    {
                        FormsAuthentication.RedirectFromLoginPage(txtuname.Text, CheckBox1.Checked);

                    }                      
                            
                    }

getting the exception in
SqlDataReader dr = cmd.ExecuteReader(); line how to solve?
and my stored procedure is
C#
alter Procedure sptblUserAuthentication
 @Username nvarchar(50),
 @Password nvarchar(50)
 As
 Begin
 Declare @Accountlocked bit
 Declare @Count int
 Declare @Retrycount int
 End
  -- Declaration is finished--
  Select @Accountlocked = Islocked from tblUsers Where Username=@Username

  if(@Accountlocked = 1)
  Begin
     Select 1 as Accountlocked,0 as RetryAttempts,0 as Authenticated
     End
     Else
     Begin
     --Check if the username and password is match--
     Select @Count=(Username) from tblUsers Where Username=@Username and Password=@Password
     --if match found--
     if(@Count = 1)
     Begin
     --Reset values --
     Update tblUsers set RetryAttempts=0 Where Username=@Username
     Select 0 as RetryAttempts,1 as Authenticated,0 as Accountlocked
     End
     Else
     Begin
     --if match not found--
     Select @Retrycount = IsNull(RetryAttempts,0) from
     tblUsers Where Username=@Username

     Set @Retrycount = @Retrycount + 1
     if(@Retrycount <= 3)
     Begin
     --if retry attempts are not completed--
     Update tblUsers Set RetryAttempts = @Retrycount Where Username=@Username
     Select 0 as Accountlocked,0 as Authenticated,@Retrycount as RetryAttempts
     End
     Else
     Begin
     --if  retry attempts are not completed--
     Update tblUsers set RetryAttempts=@Retrycount ,Islocked=1,LockDatetime=GETDATE() Where Username=@Username
     Select 1 as Accountlocked,0 as Authenticated,0 as RetryAttempts
     End
     End
     End
Posted
Updated 27-Oct-14 19:59pm
v6
Comments
Rajesh waran 28-Oct-14 1:23am    
Try this,
int retryattempts = Convert.ToInt32(dr["retryattempts"].ToString());
instead of
int retryattempts = Convert.ToInt32(dr["retryattempts"]);
Robert Welliever 28-Oct-14 1:30am    
stop trying to convert an invalid string to int would be the best solution. For one, you aren't converting nvarchar because that's SQL talk (you're using a C# string). Second you should be testing to see if it can by converted by using Int32.TryParse(). If the value is not parse-able, then don't try.
raxhemanth 28-Oct-14 1:43am    
still getting the same even use int32.TryParse() method please suggest something work out
raxhemanth 28-Oct-14 1:32am    
getting the same error gopu raj thanks for your initiative
Robert Welliever 28-Oct-14 1:48am    
Lol, sorry you're right. While you should still test that int for int-ness as I stated, your error is being thrown on the execution of the sql statement and is bubbling from Sql Server to the C# code. I promise you there is a spot in the select statement where you're converting. Post the command text if you can't find the error.

1 solution

Here I'll put it in the answer spot instead of comments:

Change: Select @Count=(Username)from...
To: Select @Count=Count(Username)from...

So you aren't selecting a varchar into an int container.
 
Share this answer
 
Comments
raxhemanth 28-Oct-14 2:11am    
Got it thankyousomuch Robert thanks alot!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900