title: Return one row from stored procedure in asp.net
body: <p>I one to create a stored procedure to retrieve one row in the table:</p>
  
  <pre><code>create procedure LogInUser
      @username nvarchar(64),
      @password nvarchar(64),
      @succeed bit out,   
      @not_exist_err bit out
  as
  declare @exist_user nvarchar(64)
  select @exist_user = username from users
  where username = @username
  
  if @exist_user is null
  begin
      set @succeed = 0
      set @not_exist_err = 1
      return
  end
  else
  begin
      select * from users
      where username = @username and password = @password 
  end
  return
  </code></pre>
  
  <p>First, I verify if a user exist or not by using:</p>
  
  <pre><code>select @exist_user = username from users
  where username = @username
  </code></pre>
  
  <p>Then get the row with every column:</p>
  
  <pre><code>select * from users
  where username = @username and password = @password 
  </code></pre>
  
  <p>However, by using <code>SqlDataReader</code>, the program won't enter the while loop to retrieve the information.</p>
  
  <p>It just don't enter the loop. I don't understand why, even I executed it using SQL Server like this, it did return a row:</p>
  
  <pre><code>declare @succeedResult bit
  declare @existErr bit
  exec LogInUser @username=admin, @password =admin, @succeed = @succeedResult, @not_exist_err = @existErr
  </code></pre>
  
  <p><strong>EDIT</strong>: The C# method which executes the store procedure:</p>
  
  <pre><code>public User LogIn(User usr)
  {
     SqlConnection conn = A2.Controller.Utils.conn;
     SqlCommand loginCmd = new SqlCommand("LogInUser", conn);
     loginCmd.CommandType = CommandType.StoredProcedure;
  
     User result = new User();
  
     try
     {
         conn.Open();
         loginCmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = usr.Username;
         loginCmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = usr.Password;
         loginCmd.Parameters.Add("@succeed", SqlDbType.Bit).Direction = ParameterDirection.Output;
         loginCmd.Parameters.Add("@not_exist_err", SqlDbType.Bit).Direction = ParameterDirection.Output;                
         SqlDataReader dr = loginCmd.ExecuteReader();              
  
         if (loginCmd.Parameters["@succeed"].Value != DBNull.Value){
            Console.WriteLine("User does not exist");
            SqlParameter notExistErr = loginCmd.Parameters["@not_exist_err"];
  
            if (notExistErr.Value != DBNull.Value){
               throw new NotExistException("The username or password is incorrect.", "Users");
            }
         }
  
         while (dr.Read()) {
             Console.WriteLine("Looping dr");
             result.Username = (string) dr["username"];
             result.Password = (string) dr["password"];
             result.FirstName = (string) dr["first_name"];
             result.MiddleName = (string) dr["middle_name"];
             result.LastName = (string) dr["last_name"];
             result.ManagerID = (int) dr["manager_id"];
             result.IsAdmin = (int) dr["is_admin"];
  
             return result;
          }
          Console.WriteLine("Done reading");
      }             
      finally {
         if (conn.State == ConnectionState.Open) conn.Close();
      }
      return result;
   }
  </code></pre>