SqlDataApdapter.Fill is bringing in NULL references for columns

T

Tolga Erdogus

Hi,

I have some code that executes some parameterized queries and then tries to
iterate through the resulting DataTables (in the dataset) to do some
processing.

When I try to get the necessary DataColumns from the DataRow object (during
each cycle of the iteration) I get NULL references.
The weird thing is the schema of the DataTable is fine as I confirm the
column names and datatypes from the DataTable.Columns properties. I execute
the underlying parametric queries in SQL Query Analyzer and I indeed get the
results (all non NULL values).

When I execute SqlDataAdapter.Fill I get the correct amount of DataRows
back, so the data is coming in. When I examine the number of columns in
each row of the DataTable I have the right number of DataColumns.

BUT, when I try to grab a value from the column I get "Specified Cast is not
valid". When I inspect a valid index in the DataRow I see a null reference.

I tried to do a FillSchema first thinking that maybe Fill doesn't quite
create the columns and expects them to be already there. Same result.

I tried adding the columns to the DataTable in advance and that works! I
don't get null references anymore.

Can someone be kind enough to explain this behaviour to me?

I am really stuck and have lost a couple of days on this stupid issue. Any
help would be greatly appreciated.

Here is my code:

public WinForm1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();

//
// TODO: Add any constructor code after InitializeComponent call
//


string cString = "Integrated Security=SSPI;Initial Catalog=DEV;Data
Source=TOLGA-ERDOGUS\\SENTIENT";
SqlConnection connection = new SqlConnection(cString);

optimizationInputDataSet = new DataSet("optimizationInputDataSet");

airportDataAdapter = new SqlDataAdapter();
aircraftDataAdapter = new SqlDataAdapter();
flightLegDataAdapter = new SqlDataAdapter();

airportDataAdapter.MissingMappingAction =
MissingMappingAction.Passthrough;
aircraftDataAdapter.MissingMappingAction =
MissingMappingAction.Passthrough;
flightLegDataAdapter.MissingMappingAction =
MissingMappingAction.Passthrough;

airportCommand = new SqlCommand("",connection);
aircraftCommand = new SqlCommand("",connection);
flightLegCommand = new SqlCommand("",connection);

airportCommand.CommandType = CommandType.Text;
aircraftCommand.CommandType = CommandType.Text;
flightLegCommand.CommandType = CommandType.Text;

airportDataAdapter.SelectCommand = airportCommand;
aircraftDataAdapter.SelectCommand = aircraftCommand;
flightLegDataAdapter.SelectCommand = flightLegCommand;

Console.WriteLine("The connection is open");

optimizationInputDataSet.Tables.Add("AIRPORTS");
optimizationInputDataSet.Tables.Add("AIRCRAFT");
optimizationInputDataSet.Tables.Add("FLIGHT_LEGS");

//
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("id",System.Type.Get
Type("System.Int64"));
//
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("name",System.Type.G
etType("System.String"));
//
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("long",System.Type.G
etType("System.Double"));
//
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("lat",System.Type.Ge
tType("System.Double"));

// airportDataView = new
DataView(optimizationInputDataSet.Tables["AIRPORTS"]);
// aircraftDataView = new
DataView(optimizationInputDataSet.Tables["AIRCRAFT"]);
// flightLegDataView = new
DataView(optimizationInputDataSet.Tables["FLIGHT_LEGS"]);


airportCommand.CommandText = "select
foobar.*,a.airpor_longitude,airpor_latitude from airport a,"+
"(select distinct airport_id,airport_abbrev from "+
"(select origin_airport_id airport_id,origin_airport_abbrev
airport_abbrev from SCHEDULE_OPTIMIZATION_CALENDAR_SNAPSHOT where
SCHOPT_ID=@SCHOPT_ID1 "+
"union all "+
"select dest_airport_id airport_id,dest_airport_abbrev
airport_abbrev from SCHEDULE_OPTIMIZATION_CALENDAR_SNAPSHOT where
SCHOPT_ID=@SCHOPT_ID2 "+
"union all "+
"select a.AIRPOR_ID,a.AIRPOR_ABBREV from
SCHEDULE_OPTIMIZATION_AIRCRAFT_AVAILABILITY aa,AIRPORT a where "+
"aa.SOAIRA_START_AIRPOR_ID=a.AIRPOR_ID and SCHOPT_ID=@SCHOPT_ID3
"+
"union all "+
"select a.AIRPOR_ID,a.AIRPOR_ABBREV from
SCHEDULE_OPTIMIZATION_AIRCRAFT_AVAILABILITY aa,AIRPORT a where "+
"aa.SOAIRA_END_AIRPOR_ID=a.AIRPOR_ID and SCHOPT_ID=@SCHOPT_ID4 "+
"union all "+
"select airp.AIRPOR_ID,airp.AIRPOR_ABBREV from
SCHEDULE_OPTIMIZATION_AIRCRAFT_AVAILABILITY aa,AIRCRAFT a,AIRPORT airp "+
"where a.AIRPOR_ID=airp.AIRPOR_ID and "+
"aa.AIRCRA_ID=a.AIRCRA_ID and "+
"aa.SCHOPT_ID=@SCHOPT_ID5) foo) foobar "+
"where "+
"a.airpor_id=foobar.airport_id "+
"and a.airpor_id <> 0 "+
"order by foobar.airport_abbrev ";

aircraftCommand.CommandText = "select foo1.*, "+
"acat.aircat_id, "+
"acat.aircat_name, "+
"sap.airpor_longitude as start_airport_longitude, "+
"sap.airpor_latitude as
start_airport_latitude, "+
"eap.airpor_longitude as end_airport_longitude, "+
"eap.airpor_latitude as
end_airport_latitude, "+
"bap.airpor_longitude as base_airport_longitude, "+
"bap.airpor_latitude as
base_airport_latitude, "+
"ac.aircra_tail_number,bap.airpor_id as base_airport_id, "+
"bap.airpor_abbrev as
base_airport_abbrev, "+
"ac.aircra_positioning_rate as deadhead_rate, "+
"ac.aircra_net_charter_rate as
live_rate, "+
"ac.aircra_overnight_cost as overnight_cost, "+
"ac.[aircra_landing_cost] as
landing_cost, "+
"ac.aircra_crew_cost as crew_cost, "+
"ac.aircra_core_network as
core_network, "+
"ac.aircra_target_hours as target_hours "+
"from
get_aircraft_availability(@SCHOPT_ID1,1) foo1, "+
"(select aircraft_id,min(start_zulu_time) as start_zulu_time from
get_aircraft_availability(@SCHOPT_ID2,1) "+
"group by aircraft_id) foo2, "+
"AIRPORT bap, AIRPORT sap, AIRPORT eap, AIRCRAFT ac,
AIRCRAFT_TYPE aty, AIRCRAFT_CATEGORY acat "+
"where
foo1.aircraft_id=foo2.aircraft_id "+
"and foo1.start_zulu_time=foo2.start_zulu_time "+
"and foo1.aircraft_id=ac.aircra_id "+
"and ac.airpor_id=bap.airpor_id "+
"and
foo1.start_airport_id=sap.airpor_id "+
"and foo1.end_airport_id=eap.airpor_id "+
"and ac.airtyp_id=aty.airtyp_id "+
"and aty.aircat_id=acat.aircat_id "+
"and ac.aircra_id <> 0 "+
"order by ac.aircra_tail_number";

flightLegCommand.CommandText = "select flightleg_id, flight_id, aircat_id,
flileg_preferred, roundtrip, upgrade_type,
origin_airport_id,dest_airport_id, origin_airport_abbrev,
dest_airport_abbrev,origin_longitude,origin_latitude, dest_longitude,
dest_latitude, zuluStartDateTime from
SCHEDULE_OPTIMIZATION_CALENDAR_SNAPSHOT where SCHOPT_ID=@SCHOPT_ID "+
"order by flightleg_id";



airportCommand.Parameters.Add("@SCHOPT_ID1",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID2",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID3",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID4",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID5",SqlDbType.Int);

aircraftCommand.Parameters.Add("@SCHOPT_ID1",SqlDbType.Int);
aircraftCommand.Parameters.Add("@SCHOPT_ID2",SqlDbType.Int);

flightLegCommand.Parameters.Add("@SCHOPT_ID",SqlDbType.Int);

connection.Open();
}

public void generateOptimizationParametersForOptimizationID(int
optimizationID)
{
airportCommand.Parameters[0].Value=optimizationID;
airportCommand.Parameters[1].Value=optimizationID;
airportCommand.Parameters[2].Value=optimizationID;
airportCommand.Parameters[3].Value=optimizationID;
airportCommand.Parameters[4].Value=optimizationID;

aircraftCommand.Parameters[0].Value=optimizationID;
aircraftCommand.Parameters[1].Value=optimizationID;

flightLegCommand.Parameters[0].Value=optimizationID;
try
{
airportDataAdapter.FillSchema(optimizationInputDataSet,SchemaType.Mapped,
"AIRPORTS");

airportDataAdapter.Fill(optimizationInputDataSet,"AIRPORTS");
aircraftDataAdapter.Fill(optimizationInputDataSet,"AIRCRAFT");
flightLegDataAdapter.Fill(optimizationInputDataSet,"FLIGHT_LEGS");

}
catch (Exception e)
{
MessageBox.Show(e.Message);
}

createAirportInfoFile();
}

public void createAirportInfoFile()
{
StreamWriter sw = new StreamWriter("airportInfo.txt");
DataTable airportTable=optimizationInputDataSet.Tables["AIRPORTS"];

for (int i = 0; i < airportTable.Rows.Count; i++)
{
DataRow airportRow=airportTable.Rows;

if (i != 0)
sw.WriteLine("");

sw.Write(i);
sw.Write(" ");
sw.Write(airportRow["airport_abbrev"]); //throws exception because null
reference
}
sw.Close();
}
 
K

Kathleen Dollard

Tolga,

Check the easy stuff first, insure that you have the number of datatables in
your dataset that you expect.

Then reprodcude the problem in a very small sample and tell us which line of
this code has the null error and the exact wording of the error.

Kathleen


Tolga Erdogus said:
Hi,

I have some code that executes some parameterized queries and then tries to
iterate through the resulting DataTables (in the dataset) to do some
processing.

When I try to get the necessary DataColumns from the DataRow object (during
each cycle of the iteration) I get NULL references.
The weird thing is the schema of the DataTable is fine as I confirm the
column names and datatypes from the DataTable.Columns properties. I execute
the underlying parametric queries in SQL Query Analyzer and I indeed get the
results (all non NULL values).

When I execute SqlDataAdapter.Fill I get the correct amount of DataRows
back, so the data is coming in. When I examine the number of columns in
each row of the DataTable I have the right number of DataColumns.

BUT, when I try to grab a value from the column I get "Specified Cast is not
valid". When I inspect a valid index in the DataRow I see a null reference.

I tried to do a FillSchema first thinking that maybe Fill doesn't quite
create the columns and expects them to be already there. Same result.

I tried adding the columns to the DataTable in advance and that works! I
don't get null references anymore.

Can someone be kind enough to explain this behaviour to me?

I am really stuck and have lost a couple of days on this stupid issue. Any
help would be greatly appreciated.

Here is my code:

public WinForm1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();

//
// TODO: Add any constructor code after InitializeComponent call
//


string cString = "Integrated Security=SSPI;Initial Catalog=DEV;Data
Source=TOLGA-ERDOGUS\\SENTIENT";
SqlConnection connection = new SqlConnection(cString);

optimizationInputDataSet = new DataSet("optimizationInputDataSet");

airportDataAdapter = new SqlDataAdapter();
aircraftDataAdapter = new SqlDataAdapter();
flightLegDataAdapter = new SqlDataAdapter();

airportDataAdapter.MissingMappingAction =
MissingMappingAction.Passthrough;
aircraftDataAdapter.MissingMappingAction =
MissingMappingAction.Passthrough;
flightLegDataAdapter.MissingMappingAction =
MissingMappingAction.Passthrough;

airportCommand = new SqlCommand("",connection);
aircraftCommand = new SqlCommand("",connection);
flightLegCommand = new SqlCommand("",connection);

airportCommand.CommandType = CommandType.Text;
aircraftCommand.CommandType = CommandType.Text;
flightLegCommand.CommandType = CommandType.Text;

airportDataAdapter.SelectCommand = airportCommand;
aircraftDataAdapter.SelectCommand = aircraftCommand;
flightLegDataAdapter.SelectCommand = flightLegCommand;

Console.WriteLine("The connection is open");

optimizationInputDataSet.Tables.Add("AIRPORTS");
optimizationInputDataSet.Tables.Add("AIRCRAFT");
optimizationInputDataSet.Tables.Add("FLIGHT_LEGS");

//
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("id",System.Type.Get
Type("System.Int64"));
//
Click to expand...
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("name",System.Type.G
etType("System.String"));
//
Click to expand...
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("long",System.Type.G
etType("System.Double"));
//
Click to expand...
optimizationInputDataSet.Tables["AIRPORTS"].Columns.Add("lat",System.Type.Ge
tType("System.Double"));

// airportDataView = new
DataView(optimizationInputDataSet.Tables["AIRPORTS"]);
// aircraftDataView = new
DataView(optimizationInputDataSet.Tables["AIRCRAFT"]);
// flightLegDataView = new
DataView(optimizationInputDataSet.Tables["FLIGHT_LEGS"]);


airportCommand.CommandText = "select
foobar.*,a.airpor_longitude,airpor_latitude from airport a,"+
"(select distinct airport_id,airport_abbrev from "+
"(select origin_airport_id airport_id,origin_airport_abbrev
airport_abbrev from SCHEDULE_OPTIMIZATION_CALENDAR_SNAPSHOT where
SCHOPT_ID=@SCHOPT_ID1 "+
"union all "+
"select dest_airport_id airport_id,dest_airport_abbrev
airport_abbrev from SCHEDULE_OPTIMIZATION_CALENDAR_SNAPSHOT where
SCHOPT_ID=@SCHOPT_ID2 "+
"union all "+
"select a.AIRPOR_ID,a.AIRPOR_ABBREV from
SCHEDULE_OPTIMIZATION_AIRCRAFT_AVAILABILITY aa,AIRPORT a where "+
"aa.SOAIRA_START_AIRPOR_ID=a.AIRPOR_ID and SCHOPT_ID=@SCHOPT_ID3
"+
"union all "+
"select a.AIRPOR_ID,a.AIRPOR_ABBREV from
SCHEDULE_OPTIMIZATION_AIRCRAFT_AVAILABILITY aa,AIRPORT a where "+
"aa.SOAIRA_END_AIRPOR_ID=a.AIRPOR_ID and SCHOPT_ID=@SCHOPT_ID4 "+
"union all "+
"select airp.AIRPOR_ID,airp.AIRPOR_ABBREV from
SCHEDULE_OPTIMIZATION_AIRCRAFT_AVAILABILITY aa,AIRCRAFT a,AIRPORT airp "+
"where a.AIRPOR_ID=airp.AIRPOR_ID and "+
"aa.AIRCRA_ID=a.AIRCRA_ID and "+
"aa.SCHOPT_ID=@SCHOPT_ID5) foo) foobar "+
"where "+
"a.airpor_id=foobar.airport_id "+
"and a.airpor_id <> 0 "+
"order by foobar.airport_abbrev ";

aircraftCommand.CommandText = "select foo1.*, "+
"acat.aircat_id, "+
"acat.aircat_name, "+
"sap.airpor_longitude as start_airport_longitude, "+
"sap.airpor_latitude as
start_airport_latitude, "+
"eap.airpor_longitude as end_airport_longitude, "+
"eap.airpor_latitude as
end_airport_latitude, "+
"bap.airpor_longitude as base_airport_longitude, "+
"bap.airpor_latitude as
base_airport_latitude, "+
"ac.aircra_tail_number,bap.airpor_id as base_airport_id, "+
"bap.airpor_abbrev as
base_airport_abbrev, "+
"ac.aircra_positioning_rate as deadhead_rate, "+
"ac.aircra_net_charter_rate as
live_rate, "+
"ac.aircra_overnight_cost as overnight_cost, "+
"ac.[aircra_landing_cost] as
landing_cost, "+
"ac.aircra_crew_cost as crew_cost, "+
"ac.aircra_core_network as
core_network, "+
"ac.aircra_target_hours as target_hours "+
"from
get_aircraft_availability(@SCHOPT_ID1,1) foo1, "+
"(select aircraft_id,min(start_zulu_time) as start_zulu_time from
get_aircraft_availability(@SCHOPT_ID2,1) "+
"group by aircraft_id) foo2, "+
"AIRPORT bap, AIRPORT sap, AIRPORT eap, AIRCRAFT ac,
AIRCRAFT_TYPE aty, AIRCRAFT_CATEGORY acat "+
"where
foo1.aircraft_id=foo2.aircraft_id "+
"and foo1.start_zulu_time=foo2.start_zulu_time "+
"and foo1.aircraft_id=ac.aircra_id "+
"and ac.airpor_id=bap.airpor_id "+
"and
foo1.start_airport_id=sap.airpor_id "+
"and foo1.end_airport_id=eap.airpor_id "+
"and ac.airtyp_id=aty.airtyp_id "+
"and aty.aircat_id=acat.aircat_id "+
"and ac.aircra_id <> 0 "+
"order by ac.aircra_tail_number";

flightLegCommand.CommandText = "select flightleg_id, flight_id, aircat_id,
flileg_preferred, roundtrip, upgrade_type,
origin_airport_id,dest_airport_id, origin_airport_abbrev,
dest_airport_abbrev,origin_longitude,origin_latitude, dest_longitude,
dest_latitude, zuluStartDateTime from
SCHEDULE_OPTIMIZATION_CALENDAR_SNAPSHOT where SCHOPT_ID=@SCHOPT_ID "+
"order by flightleg_id";



airportCommand.Parameters.Add("@SCHOPT_ID1",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID2",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID3",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID4",SqlDbType.Int);
airportCommand.Parameters.Add("@SCHOPT_ID5",SqlDbType.Int);

aircraftCommand.Parameters.Add("@SCHOPT_ID1",SqlDbType.Int);
aircraftCommand.Parameters.Add("@SCHOPT_ID2",SqlDbType.Int);

flightLegCommand.Parameters.Add("@SCHOPT_ID",SqlDbType.Int);

connection.Open();
}

public void generateOptimizationParametersForOptimizationID(int
optimizationID)
{
airportCommand.Parameters[0].Value=optimizationID;
airportCommand.Parameters[1].Value=optimizationID;
airportCommand.Parameters[2].Value=optimizationID;
airportCommand.Parameters[3].Value=optimizationID;
airportCommand.Parameters[4].Value=optimizationID;

aircraftCommand.Parameters[0].Value=optimizationID;
aircraftCommand.Parameters[1].Value=optimizationID;

flightLegCommand.Parameters[0].Value=optimizationID;
try
{
airportDataAdapter.FillSchema(optimizationInputDataSet,SchemaType.Mapped,
"AIRPORTS");

airportDataAdapter.Fill(optimizationInputDataSet,"AIRPORTS");
aircraftDataAdapter.Fill(optimizationInputDataSet,"AIRCRAFT");
flightLegDataAdapter.Fill(optimizationInputDataSet,"FLIGHT_LEGS");

}
catch (Exception e)
{
MessageBox.Show(e.Message);
}

createAirportInfoFile();
}

public void createAirportInfoFile()
{
StreamWriter sw = new StreamWriter("airportInfo.txt");
DataTable airportTable=optimizationInputDataSet.Tables["AIRPORTS"];

for (int i = 0; i < airportTable.Rows.Count; i++)
{
DataRow airportRow=airportTable.Rows;

if (i != 0)
sw.WriteLine("");

sw.Write(i);
sw.Write(" ");
sw.Write(airportRow["airport_abbrev"]); //throws exception because null
reference
}
sw.Close();
}
Click to expand...
 
T

Tolga Erdogus

I figured it out! The SqlDataAdapter or BdpDataAdapter Fill methods do not
bring in the DataTable columns in the overloaded version of the Fill method:

public override int Fill(DataSet);

Actually it brings in the right number of columns for the DataTables in the
DataSet but they seem to be half initialized and in a not-so-stable form.

You have to call the DataTable version specifically if you want to take
advantage of a successfull automatic column generation:
public int Fill(DataTable);

This is of course depdendent on the MissingMappingAction property of the
SqlDataAdapter, but that is by default Passthrough which means "create the
columns".

I don't know if this is a bug, but it sure does not make sense. You would
think that the former Fill method would iteratively do what it does in the
latter Fill method, but for all DataSets... It does not.
 
K

Kathleen Dollard

Tolga,

That sounds like a bug. Can you reproduce it in a small generic sample? If
so, I'll be sure it gets reported
 
Top