Looping through a result set and accessing the values should

[bclayshannon]

...not be this hard.

Here's my method (ACurrentUserRoles is a List of Strings):

Code: Select all

public void PopulateCurrentUserRoles(String AUserName, List ACurrentUserRoles) { 
  _UserName = AUserName; 
 
  String query = "select roleid from ABCrole where ABCid = :ABCID"; 
  Devart.Data.Oracle.OracleCommand cmd = new Devart.Data.Oracle.OracleCommand(query, con); 
  cmd.CommandType = CommandType.Text; 
  int _ABCID = GetABCIDForUserName(); 
  cmd.Parameters.Add("ABCID", _ABCID); 
  cmd.Parameters["ABCID"].Direction = ParameterDirection.Input; 
  cmd.Parameters["ABCID"].DbType = DbType.String; 
  cmd.Parameters.Add("cur", Devart.Data.Oracle.OracleDbType.Cursor); 
  cmd.Parameters["cur"].Direction = ParameterDirection.Output; 
  //cmd.ExecuteNonQuery(); blows up: "illegal variable name/number" 
  //cmd.ExecuteCursor();   " " 
  //cmd.ExecuteReader();   " " 
  Devart.Data.Oracle.OracleCursor oraCursor = 
    (Devart.Data.Oracle.OracleCursor)cmd.Parameters["cur"].Value; 
  Devart.Data.Oracle.OracleDataReader odr = oraCursor.GetDataReader(); // "Object reference not set to an instance of an object" 
  while (odr.Read()) { 
    ACurrentUserRoles.Add(odr.GetString(0)); 
  } 
}

The err msgs I'm getting are appended as comments to the lines where they occur.

Can anybody show me how to get this to work – loop through a result set and assign the values to a List?

[Pinturiccio]

In your code you use the following CommandText:

Code: Select all

"select roleid from ABCrole where ABCid = :ABCID"

in this query you define only one parameter, but you add two parameters for your OracleCommand. Therefore you receive "illegal variable name/number". You should delete the cur parameter.

You assign the Null value to the oraCursor variable when you use the following row in your code:

Code: Select all

Devart.Data.Oracle.OracleCursor oraCursor = (Devart.Data.Oracle.OracleCursor)cmd.Parameters["cur"].Value;

Because you add the new parameter and do not execute the command. So the value of "cur" is still equal to Null.

I edit your sample:

Code: Select all

public void PopulateCurrentUserRoles(String AUserName, List ACurrentUserRoles) {
  String query = "select roleid from ABCrole where ABCid = :ABCID";
  Devart.Data.Oracle.OracleCommand cmd = new Devart.Data.Oracle.OracleCommand(query, con);
  cmd.CommandType = CommandType.Text;
  int _ABCID = GetABCIDForUserName();
  cmd.Parameters.Add("ABCID", _ABCID);
  cmd.Parameters["ABCID"].Direction = ParameterDirection.Input;
  cmd.Parameters["ABCID"].DbType = DbType.String;
  Devart.Data.Oracle.OracleDataReader odr = cmd.ExecuteReader();
  while (odr.Read()) {
    ACurrentUserRoles.Add(odr.GetString(0));
  }
}

[bclayshannon]

Thanks! I knew it should be something simple like that.

[asiknoki]

keep up the terrific work.