Looping through a result set and accessing the values should

Looping through a result set and accessing the values should

Postby bclayshannon » Wed 28 Mar 2012 23:21

...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?
bclayshannon
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Postby Pinturiccio » Fri 30 Mar 2012 08:38

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));
  }
}
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

That works!

Postby bclayshannon » Fri 30 Mar 2012 16:08

Thanks! I knew it should be something simple like that.
bclayshannon
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Re: Looping through a result set and accessing the values should

Postby asiknoki » Fri 29 Jun 2012 07:14

keep up the terrific work.
asiknoki
 
Posts: 1
Joined: Fri 29 Jun 2012 07:09


Return to dotConnect for Oracle