Looping through a result set and accessing the values should

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
bclayshannon
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Looping through a result set and accessing the values should

Post by 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?

Pinturiccio
Devart Team
Posts: 2141
Joined: Wed 02 Nov 2011 09:44

Post by 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));
  }
}

bclayshannon
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

That works!

Post by bclayshannon » Fri 30 Mar 2012 16:08

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

asiknoki
Posts: 1
Joined: Fri 29 Jun 2012 07:09

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

Post by asiknoki » Fri 29 Jun 2012 07:14

keep up the terrific work.

Post Reply