DataAdapter and INNER JOIN

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hi,

I am trying to fill a DataTable using DataAdaper which has a stored
procedure as a data source. Now, if a stored procedure contains a SELECT
statement which reads data from two or more tables (using INNER JOIN),
DataAdapter creates an empty column for each table in the FROM clause and
calls them ID, ID1, ID2 and so on. Is this normal behaviour, what possible
purpose might it have and most importantly, how can I avoid it?
Click to expand...

What database server are you using, and what does your stored procedure
look like?
 
Hi,

I am trying to fill a DataTable using DataAdaper which has a stored
procedure as a data source. Now, if a stored procedure contains a SELECT
statement which reads data from two or more tables (using INNER JOIN),
DataAdapter creates an empty column for each table in the FROM clause and
calls them ID, ID1, ID2 and so on. Is this normal behaviour, what possible
purpose might it have and most importantly, how can I avoid it?

Thanks,

Niksa
 
I am using SQL Server 2000. There are several stored procedures, but they
are all basically the same. Each contains a single SELECT statement, with
only SELECT and FROM clauses. If the FROM clause contains inner or outer
join, the DataAdapter automatically adds one column per each table in the
FROM clause. I suppose that happens because I didn't include primary key
columns from all tables, but I don't need them because I am not going to do
anything with the data, I just want to display it.

I realize that I can easily remove the unwanted columns from the table, but
I wanted to know the cause of this behavior, because I don't like it one
bit.


Niksa
 
Hi Niksa,

DataAdapter creates a column per returned column and not per table in FROM
clause.
If there are duplicate names, it renames the offensive names.
It creates a table if there is no suitable table already in dataset.
Can you show us the content of your sp?
 
Hi,

perhaps I wasn't clear enough: DataAdapter creates a column for each field
in the SELECT clause, but *in addition* to that it creates a column for each
table in the FROM clause *if* their respective primary key is not in the
SELECT clause. From my perspective, such behavior is completely
unnecessary - new columns are filled with nulls and therefore useless.

Anyway, I found a workaround. Before calling the Fill method I fill the
table schema like this:

dadA.MissingSchemaAction = MissingSchemaAction.Ignore
dadA.FillSchema(tblA, SchemaType.Source)
dadA.Fill(tblA)

This way new columns are not created because DataAdapter is told to ignore
the "differences" in schema, although in this case there should be none.

Thanks,

Niksa
 
Just curious, did you have the column names typed out in the sp

ie.. table1.column1, table1.column2, table2.column1
or where you using * in your select.

Shannon
 
Back
Top