DataAdapter and INNER JOIN

[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?

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

 

[Niksa Baldun]

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

 

[Niksa Baldun]

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

 

[Miha Markic]

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?

 

[Niksa Baldun]

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

 

[Shannon Ramirez]

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

 

[Niksa Baldun]

I never use SELECT *, all columns are specifically requested.

Niksa