CommandBehavior.SingleRow? RSS

• • Reply
  

  mmount

  Member

  1 Points

  18 Posts

  CommandBehavior.SingleRow?

  Nov 13, 2012 12:40 PM|LINK

  Hi all,

  I'm just getting started here and am piecing together some authentication code when I ran into this issue(?). I have the following code to check the user against the database:

  bool isValid = false;
  
          OleDbConnection conn = new OleDbConnection(connectionString);
          OleDbCommand cmd = new OleDbCommand(
                  "SELECT ID, FullName " +
                  "FROM UserTable " +
                  "WHERE Username = ? AND IsActive = 'Y'",
                  conn);
  
          cmd.Parameters.Add("@Username", OleDbType.VarChar, 8).Value = thisUserName;
  
          OleDbDataReader reader = null;
  
          try
          {
            conn.Open();
  
            reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
  
            isValid = reader.HasRows;
  
            if (isValid)
            {
              reader.Read();
              user.Name = reader.FieldAsString("FullName");
            }
  
            reader.Close();
          }
          catch (OleDbException e)
          {
            LogException("ValidateEmployee", e);
          }
          finally
          {
            if (reader != null) reader.Close();
            conn.Close();
          }
        }
        return isValid;

  When I run this, passing in a valid username, reader.HasRows returns false. If I remove the Command.SingleRow from ExecuteReader it returns true and all is well. My database is Sybase SQL Anywhere 11 - is this command just not supported for some databases or am I doing something wrong?

  Thanks,

  Mike

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: CommandBehavior.SingleRow?

  Nov 14, 2012 12:44 AM|LINK

  mmount

  mmount

  Hello,

  You can try to change your parameter the same formation as what you've added in your Parameters' list:

  OleDbCommand cmd = new OleDbCommand(
                  "SELECT ID, FullName " +
                  "FROM UserTable " +
                  "WHERE Username = @Username AND IsActive = 'Y'",
                  conn);

   

• • Reply
  

  mmount

  Member

  1 Points

  18 Posts

  Re: CommandBehavior.SingleRow?

  Nov 15, 2012 01:36 PM|LINK

  Thank you. My understanding is that OleDb does not support named parameters. I'm specifying a name when I create the parameter just for clarity. As I mentioned, if I remove the CommandBehavior.SingleRow then I do properly get my row back - which leads me to believe that the parameters are being properly substituted.

  For the time being I'm just not using the CommandBehavior since the documentation says it may or may not be more efficient to do so. This also allows me to throw an exception if I'm expecting a single row and get either none or more than one.

  Mike

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: CommandBehavior.SingleRow?

  Nov 16, 2012 12:21 AM|LINK

  mmount

  Thank you. My understanding is that OleDb does not support named parameters.

  Well……as far as I see it supports. Maybe you can have a try, if you insist using yours, I think you can do this:

  OleDbConnection conn = new OleDbConnection(connectionString);
          OleDbCommand cmd = new OleDbCommand(
                  "SELECT ID, FullName " +
                  "FROM UserTable " +
                  "WHERE Username = ? AND IsActive = 'Y'",
                  conn);
  
          cmd.Parameters.Add("Username", OleDbType.VarChar, 8).Value = thisUserName;

  And then CommandBehavior will return a whole complete row, so you can try this:

  OleDbCommand cmd = new OleDbCommand(
                  "SELECT * " +
                  "FROM UserTable " +
                  "WHERE Username = ? AND IsActive = 'Y'",
                  conn);

• • Reply
  

  mmount

  Member

  1 Points

  18 Posts

  Re: CommandBehavior.SingleRow?

  Nov 16, 2012 12:59 PM|LINK

  I did give the @Username instead of ? thing a try and just got an OleDb exception ("Column @Username was not found") indicating the parameter did not get substituted. I don't recall where I read that OleDb did not support named parameters but apparently I am remembering correctly :)

  Thanks for your time.

  Mike

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: CommandBehavior.SingleRow?

  Nov 17, 2012 12:08 AM|LINK

  mmount

  mmount

  Hi,

  Maybe your Username is a key-perserved word, you can add a pair of []——

  OleDbCommand cmd = new OleDbCommand(
                  "SELECT ID, FullName " +
                  "FROM UserTable " +
                  "WHERE [Username] = @UserName AND IsActive = 'Y'",
                  conn);
  
          cmd.Parameters.Add("@UserName", OleDbType.VarChar, 8).Value = thisUserName;

  • Marked as answer by decker dong - msft on Nov 19, 2012 12:35 AM