如何修复将nvarchar值转换为数据类型int时转换失败。 [英] how to fix Conversion failed when converting the nvarchar value to data type int.?

问题描述

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);

                    }                      
                            
                    }

在

中获取异常SqlDataReader dr = cmd。的ExecuteReader（）;如何解决？

和我的存储过程是

getting the exception in
SqlDataReader dr = cmd.ExecuteReader(); line how to solve?
and my stored procedure is

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

推荐答案

在这里我会把它放在答案点而不是评论中：



更改：从...中选择@ Count =（用户名）

To：从...中选择@ Count = Count（用户名）



所以你没有在一个int容器中选择一个varchar。

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.

这篇关于如何修复将nvarchar值转换为数据类型int时转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！