OleDbCommand returns same reader with different parameters

  • Thread starter Thread starter Oikonomopoulos Spyros
  • Start date Start date
O

Oikonomopoulos Spyros

Hi to all,

Database: Access2000

I have a class with an OleDbCommand as a private member
(which is initialized at construction time, so that I can
reuse the command without creating it every time), and a
method to get a range of objects:

class MyClass
{
private OleDbCommand selectCommand;
MyClass() {
selectCommand = new OleDbCommand();
selectCommand.CommandText = MyAccessQuery;
selectCommand.CommandType=CommandType.StoredProcedure;
selectCommand.Parameters.Add("@ID",OleDbType.Integer,1);
}

public GetMyObjects(int[] objectIds) {
int objectSequence=0;
selectCommand.Connection = new OleDbConnection(...);
selectCommand.Connection.Open();
for (int i=0;i<objectIds.Length;i++) {
selectCommand.Parameters[0].Value=objectIds;
OleDbDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) {
sequence = reader.GetInt32(0);
}
reader.Close();
}

}

My problem is that I get the sequence for the first id
(objectIds[0]) for the whole loop!!! if I just create the
command in the GetMyObjects() method instead, everything
works fine:

public GetMyObjects(int[] objectIds) {
int objectSequence=0;

for (int i=0;i<objectIds.Length;i++) {
selectCommand = new OleDbCommand();
selectCommand.CommandText = MyAccessQuery;
selectCommand.CommandType=CommandType.StoredProcedure;
selectCommand.Parameters.Add("@ID",OleDbType.Integer,1);
selectCommand.Connection = new OleDbConnection(...);
selectCommand.Connection.Open();
selectCommand.Parameters[0].Value=objectIds;
OleDbDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) {
sequence = reader.GetInt32(0);
}
reader.Close();
selectCommand.Dispose();
}

Any ideas?

Thanks in advance
 
Hi,

I think that the problem lies within Access' so called stored procedures.
They will always use only the first set of parameters and ignore subsequent
ones.
I guess you are stuck with second example.
 
Back
Top