一个简单的sql问题 [英] A simple sql questions

问题描述

我正在制作登录页面。现在，当我输入用户名，密码时，它会一直旋转并且什么都不做。



我在调试模式下启动它，看到它没有循环遍历代码里面的语句



> ;>>>>>>>>>>>>>>>>>>  >>        > while （rdr.Read（））
{

int RetryAttempts = Convert.ToInt32（rdr [< span class =code-string> RetryAttempts]）;
if （Convert.ToBoolean（rdr [ AccountLocked]））
{

Label3.Text = 帐户已锁定。请联系管理部门;
}

else if （RetryAttempts> 0）
{
int AttemptsLeft =（ 4 - RetryAttempts）;
Label3.Text = 无效的用户名和/或密码 + AttemptsLeft.ToString（ ）+ 尝试离开;

}

else if （转换。 ToBoolean（rdr [ 经过验证的]））
{

FormsAuthentication.RedirectFromLoginPage（TextBox1.Text，CheckBox1.Checked）;
Response.Redirect（ welcome.aspx）;
}


}

当我检查时，它在循环中没有通过代码。







这是webconfig文件

< connectionStrings> 
 
< add name =   connection connectionString =  数据源= ANK \ SQLEXPRESS;初始目录=样本;集成安全性=真; providerName =   System.Data.SqlClient /> 
 
< / connectionStrings> 

这是sql代码

 更改  proc  spAuthenticateUser 
  @ UserName   nvarchar （ 100 ），
  @ Password   nvarchar （ 200 ）
 
  as  
 开始 
 声明  @ AccountLocked  位<​​/ span> 
 声明  @ Count   int  
 声明  @ RetryCount   int  
 
 选择 @ AccountLocked = IsLocked 
 来自 tblUsers 其中 UserName = @ UserName 
 
   -   如果帐户是已锁定 
 如果（@ AccountLocked = 1）
 开始 
 选择  1    AccountLocked ， 0   as 经过身份验证， 0   as  RetryAttempts 
 结束 
 其他 
 开始 
   -   检查我们是否ername和密码匹配 
 选择  @ Count  = Count（UserName）来自 tblUsers 
 其中 [UserName] =  @ UserName  和 [密码] = @密码
   -   如果找到匹配 
 如果（@ Count = 1）
 开始 
   -   重置RetryAttempts  
 更新 tblUsers  set  RetryAttempts = 0 
 其中​​ UserName =  @ UserName  
 选择  0   as  AccountLocked， 1   as 经过身份验证， 0   as  RetryAttempts 
 结束 
 否则 
 开始 
   -   如果找不到匹配项 
 
 选择  @ RetryCount  = IsNull（RetryAttempts， 0 ）
 来自 tblUsers 
 其中 UserName =  @ UserName  
 设置  @ RetryCount  =  @ RetryCount  +1 
 
  if （@ RetryCount< ; = 3）
 开始 
 
   -   如果重试尝试未完成 
 
 更新 tblUsers  set  RetryAttempts =  @ RetryCount  
 其中​​ UserName =  @ UserName  
 
 设置  @ RetryCount  =  @ RetryCount  +1 
 
  if （ @ RetryCount < = 3）
 
 开始 
   -   如果重试尝试未完成 
 
 更新 tblUsers  set  RetryAttempts =  @ RetryCount  
 
  where  UserName =  @ UserName  
 选择  0   as  AccountLocked， 0   as 经过身份验证， 0   as  RetryAttempyts 
 
 结束 
 结束 
 结束 
 
 结束 
 结束 

可以有人请帮帮我吗？

解决方案

在while循环外放一个if条件并检查读者对象的'HasRows'属性，如'if（rdr.HasRows）' 。通过执行此操作，您将确保仅当读取器中有行时才执行while循环。

尝试

  while （reader.HasRows）
 {
 // Code 
 reader.NextResult（）; 
} 

I am making login page. Now, when i put username, password, it keeps rotating and doesnt do anything.

I started it in debug mode and saw that it doesnt loop through code inside while statement

>>>>>>>>>>>>>>>>>

while(rdr.Read())
            {

                int RetryAttempts = Convert.ToInt32(rdr["RetryAttempts"]);
                if(Convert.ToBoolean(rdr["AccountLocked"]))
                {

                    Label3.Text = "Account Locked.Please Contact administration";
                }

                else if(RetryAttempts>0)
                {
                    int AttemptsLeft = (4 - RetryAttempts);
                    Label3.Text = "Invalid user name and/or password" + AttemptsLeft.ToString() + "attempts left";

                }

                else if(Convert.ToBoolean(rdr["Authenticated"]))
                {

                    FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, CheckBox1.Checked);
                    Response.Redirect("welcome.aspx");
                }


            }

When i checked, it doesnt go through code inside while loop.



Here's webconfig file

<connectionStrings>

    <add name="connection" connectionString="Data Source=ANK\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=true;" providerName="System.Data.SqlClient"/>

  </connectionStrings>

Here's sql code

Alter proc spAuthenticateUser
@UserName nvarchar(100),
@Password nvarchar(200)

as 
Begin
Declare @AccountLocked bit
Declare @Count int
Declare @RetryCount int

Select @AccountLocked=IsLocked
from tblUsers where UserName=@UserName

--if the account is already locked
             if(@AccountLocked=1)
              Begin
             Select 1 as AccountLocked, 0 as Authenticated, 0 as RetryAttempts
             End
             Else
             Begin
--check if the username and password match
Select @Count = Count(UserName) from tblUsers
Where [UserName] = @UserName and [Password]=@Password
--if match found
if(@Count=1)
Begin
--Reset RetryAttempts
Update tblUsers set RetryAttempts=0
Where UserName = @UserName
Select 0 as AccountLocked, 1 as Authenticated, 0 as RetryAttempts
End
Else
Begin
--If a match is 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

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, 0 as RetryAttempyts

End
End
End

End
End

Can someone please help me?

解决方案

Put a if condition outside the while loop and check the 'HasRows' property of the reader object like 'if(rdr.HasRows)'. By doing this you will make sure that the while loop will be executed only if there are rows in reader.

Try

while (reader.HasRows)
{
    //Code
    reader.NextResult();
}

这篇关于一个简单的sql问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！