Using DataAdapter.Fill()

[Micus]

[Win XP Pro, SQL Server 2000, VS 2005 Pro, C++/CLI]


I'm having a problem with using the SqlDataAdapter->Fill() method. I
have 2 tables, Orders & Customers which are related by a CustomerID. I have
2 DataAdapters that I'm using to Fill() a dataset: the first to
Fill(dataset, "Orders") between 2 dates; the second to Fill(dataset,
"Customers") based on the order rows retrieved. Whenever the same customer
has 2 or more orders retrieved, Fill(dataset, "Customers") generates a
constraint error. The CustomerID, which is the only column with constraints,
is an autoincrement primary key and I'm using
MissingSchemaAction::AddWithKey. I'm still learning DB programming and I
can't find what I'm doing wrong.

Thank you,
M

 

[Marina Levit [MVP]]

It sounds like your query for the customers needs to retrieve unique
customers. So even if a customer has 10 orders, you should retrieve it only
once. Right now you are retrieving it 10 times. You probably need to put a
'distinct' in your query to retrieve each customer just once.

 

[Micus]

Would you have a short example or a link to an example... I'm really new to
ADO and DB programming, and what you describe is exactly what I need.

My Example Query:

SELECT Customer.* FROM Orders, Customers
WHERE Orders.Date >= @Date1 AND Orders.Date <= @Date2
AND Orders.CustomerID = Customers.CustomerID

Thank You Marina,
M

It sounds like your query for the customers needs to retrieve unique
customers. So even if a customer has 10 orders, you should retrieve it only
once. Right now you are retrieving it 10 times. You probably need to put a
'distinct' in your query to retrieve each customer just once.

[Win XP Pro, SQL Server 2000, VS 2005 Pro, C++/CLI]


I'm having a problem with using the SqlDataAdapter->Fill() method. I
have 2 tables, Orders & Customers which are related by a CustomerID. I
have
2 DataAdapters that I'm using to Fill() a dataset: the first to
Fill(dataset, "Orders") between 2 dates; the second to Fill(dataset,
"Customers") based on the order rows retrieved. Whenever the same customer
has 2 or more orders retrieved, Fill(dataset, "Customers") generates a
constraint error. The CustomerID, which is the only column with
constraints,
is an autoincrement primary key and I'm using
MissingSchemaAction::AddWithKey. I'm still learning DB programming and I
can't find what I'm doing wrong.

Thank you,
M

 

[Micus]

I found the DISTINCT keyword, played around, and it's working as desired.
Thank You!

A side question - I thought the DataAdapter.Fill() with the AddWithKey
would update, and not append, rows with the same primary key. Did I not read
that right?

Much appreciated Marina,
M

It sounds like your query for the customers needs to retrieve unique
customers. So even if a customer has 10 orders, you should retrieve it
only once. Right now you are retrieving it 10 times. You probably need to
put a 'distinct' in your query to retrieve each customer just once.

[Win XP Pro, SQL Server 2000, VS 2005 Pro, C++/CLI]


I'm having a problem with using the SqlDataAdapter->Fill() method.
I
have 2 tables, Orders & Customers which are related by a CustomerID. I
have
2 DataAdapters that I'm using to Fill() a dataset: the first to
Fill(dataset, "Orders") between 2 dates; the second to Fill(dataset,
"Customers") based on the order rows retrieved. Whenever the same
customer
has 2 or more orders retrieved, Fill(dataset, "Customers") generates a
constraint error. The CustomerID, which is the only column with
constraints,
is an autoincrement primary key and I'm using
MissingSchemaAction::AddWithKey. I'm still learning DB programming and I
can't find what I'm doing wrong.

Thank you,
M

 

[Marina Levit [MVP]]

AddWithKey refers to adding columns that are not already part of the schema
of the table. It has nothing to do with the rows themselves.

Here is the description from the documentation for AddWithKey:

Adds the necessary columns and primary key information to complete the
schema. For more information about how primary key information is added to a
DataTable, see FillSchema.To function properly with the .NET Framework Data
Provider for OLE DB, AddWithKey requires that the native OLE DB provider
obtains necessary primary key information by setting the DBPROP_UNIQUEROWS
property, and then determines which columns are primary key columns by
examining DBCOLUMN_KEYCOLUMN in the IColumnsRowset. As an alternative, the
user may explicitly set the primary key constraints on each DataTable. This
ensures that incoming records that match existing records are updated
instead of appended. When using AddWithKey, the .NET Framework Data Provider
for SQL Server appends a FOR BROWSE clause to the statement being executed.
The user should be aware of potential side effects, such as interference
with the use of SET FMTONLY ON statements. See SQL Server Books Online for
more information.

I found the DISTINCT keyword, played around, and it's working as desired.
Thank You!

A side question - I thought the DataAdapter.Fill() with the AddWithKey
would update, and not append, rows with the same primary key. Did I not
read that right?

Much appreciated Marina,
M

It sounds like your query for the customers needs to retrieve unique
customers. So even if a customer has 10 orders, you should retrieve it
only once. Right now you are retrieving it 10 times. You probably need
to put a 'distinct' in your query to retrieve each customer just once.

[Win XP Pro, SQL Server 2000, VS 2005 Pro, C++/CLI]


I'm having a problem with using the SqlDataAdapter->Fill() method.
I
have 2 tables, Orders & Customers which are related by a CustomerID. I
have
2 DataAdapters that I'm using to Fill() a dataset: the first to
Fill(dataset, "Orders") between 2 dates; the second to Fill(dataset,
"Customers") based on the order rows retrieved. Whenever the same
customer
has 2 or more orders retrieved, Fill(dataset, "Customers") generates a
constraint error. The CustomerID, which is the only column with
constraints,
is an autoincrement primary key and I'm using
MissingSchemaAction::AddWithKey. I'm still learning DB programming and I
can't find what I'm doing wrong.

Thank you,
M