OracleDataAdapter filling Dataset with duplicate rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am using OracleDataAdapter to fill my DataSet. I am using Select Distinct
in my sql querry to get unique recored.

I am getting duplicate rows in DataSet.

My SqlPlus shows only one row of Data. When I execute the code to fill DataSet

My DataSet.tables[0]. rows.count gives me count of 2.

When I loop through DataSet to display Values I am getting same values twice.

Anyone has any suggestion on how to get distinct rows in dataSet.

Thanks in Advance,

MS
 
Hi,

Either your commandtext is returning two records or you have a row hanging
in datatable from before.
Make sure you clear dataset (DataSet.Clear()) before filling data.
 
¤
¤ Hi,
¤
¤ I am using OracleDataAdapter to fill my DataSet. I am using Select Distinct
¤ in my sql querry to get unique recored.
¤
¤ I am getting duplicate rows in DataSet.
¤
¤ My SqlPlus shows only one row of Data. When I execute the code to fill DataSet
¤
¤ My DataSet.tables[0]. rows.count gives me count of 2.
¤
¤ When I loop through DataSet to display Values I am getting same values twice.
¤
¤ Anyone has any suggestion on how to get distinct rows in dataSet.
¤
¤ Thanks in Advance,

Keep in mind that the SQL statement you execute via SQL Plus may not be parsed exactly the same way
as through the data provider (not sure which one you are using).

You may want to post an example of your PL/SQL statement and identify the data provider you are
using.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hi Paul,

Really appriciate your response,

I am using following code:

DataSet Ds = new DataSet();
Ds.Clear();

StringBuilder commandText = new StringBuilder("select DISTINCT
eoi.eqsz_id,eoi.eqtp_id,eoi.eqht_id from equipment_order_items eoi,
equipment_orders eo where eo.GKEY = eoi.EQO_GKEY and eo.NBR ='" +
bookingId.Trim() + "'");

System.Data.OracleClient.OracleConnection Conn = new
OracleConnection(this.strConnect);
Conn.Open();
OracleCommand DSCmd = new OracleCommand(commandText.ToString(),Conn);
DSCmd.CommandType = CommandType.Text;
OracleDataAdapter orAd = new OracleDataAdapter(DSCmd);
orAd.Fill(Ds);
Conn.Close();

System.Windows.Forms.MessageBox.Show( Ds.Tables[0].Rows.Count.ToString());

This code is in a funtion that my application calls to get the size, type
and height by passing booking id.

I am using micorsoft OracleClient, I have Oracle 9i client installed on
machine.

I ran this querry in Toad and I got the count of 1

Querry in Toad
select count(*) from (select distinct eoi.eqsz_id,eoi.eqtp_id,eoi.eqht_id
from equipment_order_items eoi, equipment_orders eo where eo.GKEY =
eoi.EQO_GKEY and eo.NBR ='098060007')

Any help would be appreciated.

Really appreciate your help

Thanks
MS
 
¤ Hi Paul,
¤
¤ Really appriciate your response,
¤
¤ I am using following code:
¤
¤ DataSet Ds = new DataSet();
¤ Ds.Clear();
¤
¤ StringBuilder commandText = new StringBuilder("select DISTINCT
¤ eoi.eqsz_id,eoi.eqtp_id,eoi.eqht_id from equipment_order_items eoi,
¤ equipment_orders eo where eo.GKEY = eoi.EQO_GKEY and eo.NBR ='" +
¤ bookingId.Trim() + "'");
¤
¤ System.Data.OracleClient.OracleConnection Conn = new
¤ OracleConnection(this.strConnect);
¤ Conn.Open();
¤ OracleCommand DSCmd = new OracleCommand(commandText.ToString(),Conn);
¤ DSCmd.CommandType = CommandType.Text;
¤ OracleDataAdapter orAd = new OracleDataAdapter(DSCmd);
¤ orAd.Fill(Ds);
¤ Conn.Close();
¤
¤ System.Windows.Forms.MessageBox.Show( Ds.Tables[0].Rows.Count.ToString());
¤
¤ This code is in a funtion that my application calls to get the size, type
¤ and height by passing booking id.
¤
¤ I am using micorsoft OracleClient, I have Oracle 9i client installed on
¤ machine.
¤
¤ I ran this querry in Toad and I got the count of 1
¤
¤ Querry in Toad
¤ select count(*) from (select distinct eoi.eqsz_id,eoi.eqtp_id,eoi.eqht_id
¤ from equipment_order_items eoi, equipment_orders eo where eo.GKEY =
¤ eoi.EQO_GKEY and eo.NBR ='098060007')
¤
¤ Any help would be appreciated.
¤
¤ Really appreciate your help
¤
¤ Thanks
¤ MS

I don't seem to be able to repro your results. Using the DISTINCT keywords eliminates any duplicate
rows - a duplicate row being one which all columns specified in the SELECT statement have values
identical to another row.

I tested this under the Oracle 8.1.7 client. Not sure if there would be any difference when using
the 9i client.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top