RE: Problem: duplicate rows on OracleDataAdapter.fill, PKs are defined

Okay,

Nevermind. I've found that I had multiple rows coming back from the first
select statement, as well as the fact that I had an open column query were
causing my problem.

My select statement should have been:

SELECT DISTINCT CoAddr.* FROM EW.COMPANY_ADDRESS CoAddr, EW.INVOICE IH
WHERE CoAddr.COMPANY = IH.GENERATOR AND CoAddr.COMPANY_ADDRESS =
IH.GENERATOR_ADDRESS AND IH.BATCH = '0282' AND IH.SENT IS NULL

Though, this errors without the DISTINCT, which I thought was supposed to
work as long as you have your PKs defined, which I do. So, I'm still a bit
confused.

"kamueller" wrote:

> Problem
> --------------------------------------
> I'm running into a problem when I call successive OracleDataAdapter.Fill
> calls.
> The problem is, that I'm getting duplicate rows in my DataTable object even
> though I have the PKs defined on the DataTable. I've confirmed, by breaking
> into the code at runtime, that the Composite Primary Key columns are on the
> DataTable object.
>
> Background Information
> ---------------------------------------
> Using System.Data.OracleClient
> I have a COMPANY_ADDRESS DataTable with a composite key:
> Key Columns [ COMPANY | COMPANY_ADDRESS ]
> I am using a .FillSchema call to create the DataTable in a DataSet:
>
> private const string SQL_EW_COMP_ADDR_SCHEMA = "SELECT * FROM
> EW.COMPANY_ADDRESS";
>
> Fill Schema Code:
>
> // Retrieve the schema information for the Company Address table
> DatAdap_COMP_ADDR = new OracleDataAdapter();
> DatAdap_COMP_ADDR.MissingSchemaAction = MissingSchemaAction.AddWithKey;
> DatAdap_COMP_ADDR.TableMappings.Add("Table", "COMPANY_ADDRESS");
> ORACMD_COMP_ADDR = new OracleCommand(SQL_EW_COMP_ADDR_SCHEMA, EWConn);
> ORACMD_COMP_ADDR.CommandType = CommandType.Text;
> DatAdap_COMP_ADDR.SelectCommand = ORACMD_COMP_ADDR;
>
> DatAdap_COMP_ADDR.FillSchema(DS_EWTables, SchemaType.Mapped );
>
> Here are the SQL Statements that are used to fill the DataTable (They return
> the same row):
>
> private const string SQL_EW_COMP_ADDR_BILLTO_SELECT = "SELECT * FROM
> EW.COMPANY_ADDRESS CoAddr, EW.INVOICE IH\n" +
> "WHERE CoAddr.COMPANY = IH.COMPANY AND
> CoAddr.COMPANY_ADDRESS = IH.COMPANY_ADDRESS\n" +
> "AND IH.BATCH = '0282' AND IH.SENT IS NULL";
> private const string SQL_EW_COMP_ADDR_GENERATOR_SELECT = "SELECT * FROM
> EW.COMPANY_ADDRESS CoAddr, EW.INVOICE IH\n" +
> "WHERE CoAddr.COMPANY = IH.GENERATOR AND
> CoAddr.COMPANY_ADDRESS = IH.GENERATOR_ADDRESS\n" +
> "AND IH.BATCH = '0282' AND IH.SENT IS NULL";
.