Extra DataColumn's in DataTable

[Faz]

Have a strange problem using a sproc to populate a DataTable.

The sproc itself is fairly simple and returns a selection of columns from
three tables, a master table and two additional tables which are joined via
a LEFT JOIN.

The resultant DataTable schema after a fillschema looks good however after
a 'fill' the DataTable has two extra columns at the end which represent the
unselected columns from the two LEFT JOIN tables. These columns do not form
part of the SELECT statement yet are appearing in the DataTable with all
values set to null.

I have tried the same query with a DataReader and see the correct columns
(obviously executing the same sproc from query analyser shows the correct
column set) therefore it appears to be a DataAdaptor issue which is somehow
pulling down these unselected columns. This problem is a right pain as the
I am binding a DataGrid to the DataTable via a DataView and the extra
columns appear at the end of the grid.

I know I could remove the columns via TableStyles however I might change
the database schema at some point and I would need to know dynamically at
run-time which columns were garbage and at the moment I can't see any
properies on these columns which I could process against.

This looks like a bug to me therefore interested if anyone has come across.
Can post sample code (c#) and sproc if anyone would like to see.

Regards
Paul

 

[Miha Markic]

Hi Faz,

Seems strange to me that dataadapter would know which columns are not
retrieved from database.
Can you show us your select and your fill code?

 

[Faz]

Hi Faz,

Seems strange to me that dataadapter would know which columns are not
retrieved from database.
Can you show us your select and your fill code?

Hi Miha

Here's the relevant sproc code :


************************************************************************
Stored Procedure
************************************************************************
SELECT
Inventory.[MachineID] ,
SubnetLocation.Location ,
Inventory.[Model] ,
InventoryModel.[Description] AS ModelDesc ,
Inventory.[SerialNumber] ,
Inventory.[Username] ,
Inventory.[Campus] ,
Inventory.[BuildVersion] ,
Inventory.[ReleaseVersion] ,
Inventory.[Processor] ,
Inventory.[CPUSpeed] ,
Inventory.[Memory] ,
Inventory.[IPAddress] ,
Inventory.[SubNetMask] ,
Inventory.[NetworkAddress] ,
Inventory.[MACAddress] ,
Inventory.[OperatingSystem] ,
Inventory.[DiskSize] ,
Inventory.[DiskSpace_C] ,
Inventory.[VideoCard] ,
Inventory.[RecordTime]

FROM Inventory
LEFT JOIN InventoryModel ON InventoryModel.ID = Inventory.Model
LEFT JOIN SubnetLocation ON SubnetLocation.NetworkAddress =
Inventory.NetworkAddress

This is basically pulling h/w inventory information from an Inventory
table and joining to a SubnetLocation table to look up geographical info
as well as an InventoryModel which associates a Model ID with a model
description. The details aren't important however thought it worth
mentioning.

The C# code is below :

************************************************************************
C# Code
************************************************************************
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = new SqlCommand();
sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDa.SelectCommand.Connection = dbConn.Connection;
sqlDa.SelectCommand.CommandText = "GetInventory";

try
{
//Create new DataTable and populate Schema
ResultsTable = new DataTable ( "Inventory" );
sqlDa.FillSchema ( ResultsTable , SchemaType.Source );

//Set dataType of various columns to 'long'. These are varchar in
Database as SQL7 does not
//support int > 4bytes. Setting to long here means DataGrid will
sort correctly
if ( ResultsTable.Columns["CPUSpeed"] != null )
ResultsTable.Columns["CPUSpeed"].DataType = typeof(long);
if ( ResultsTable.Columns["Memory"] != null )
ResultsTable.Columns["Memory"].DataType = typeof(long);
if ( ResultsTable.Columns["Disksize"] != null )
ResultsTable.Columns["Disksize"].DataType = typeof(long);
if ( ResultsTable.Columns["Diskspace_C"] != null )
ResultsTable.Columns["Diskspace_C"].DataType = typeof(long);

//Get Data from Database
sqlDa.Fill ( ResultsTable );

//Setup a DataView for filtering and bind DataGrid to View
ResultsView = new DataView ( ResultsTable );
dgInventory.DataSource = ResultsView;
}
catch ( Exception sqlE )
{
//Error Handling
}

The only slightly odd thing here is that I populate the table via
FillSchema first as I need to convert the format on a number of columns
to numerical (SQL7 on back end which does not have bigint support).
However, if I watch the ResultsTable object (class level variable) then
it has the required columns (21) after a fill schema. However, after the
fill, it has 23 columns. The extra columns are ID and NetworkAddress1.
ID is the join field from InventoryModel and NetworkAddress is also the
join field for SubnetLocation. However, a 1 has been suffixed otherwise
it would clash with the selected NetworkAddress field. Obviously I
cannot explain why these field are appearing at all in ResultsTable
anyway.

Executing the same query via ExecuteReader gives me a SqlDataReader with
the correct columns. Any help would be appreciated. In the meantime I'm
going to set up some generic test tables and do a similar LEFT JOIN
query and see if this is a general SqlDataAdaptor, DataTable issue.

Thanks

Faz