Why exception when going through the DataSet?

[Guest]

When executing the following code

ArrayList list = new ArrayList();
try
{
//Make sure connection is open
if (!connection.State.ToString().Equals("Open"))
connection.Open();

DataSet list = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select " + m_TableSerieColName + "
from " + m_TableSerie, connection);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[m_TableSerie].Rows) //<-----
list.Add((string) dr[m_TableSerieColName]);
}
catch (SqlException exc)
{
MessageBox.Show(exc.Message);
return;
}

I get the following exception:
---
An unhandled exception of type 'System.NullReferenceException' occurred in
SportsResult.exe

Additional information: Object reference not set to an instance of an object.
---
on the line

foreach (DataRow dr in ds.Tables[m_TableSerie].Rows) //<-----

Rows has a length of 0, so what I expect is the foreach loop to execute 0
times, but instead I then get the exception. Why?

 

[Guest]

It isn't exactly as I wrote. This is the code:

ArrayList list = new ArrayList();
DataSet ds = SelectQuery("select " + m_TableSerieColName + " from " +
m_TableSerie);
foreach (DataRow dr in ds.Tables[m_TableSerie].Rows)
list.Add((string) dr[m_TableSerieColName]);
return list;

public DataSet SelectQuery(string query)
{
try
{
//Make sure connection is open
if (!connection.State.ToString().Equals("Open"))
connection.Open();

SqlDataAdapter da = new SqlDataAdapter(query, connection);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
catch (SqlException exc)
{
MessageBox.Show(exc.Message);
return new DataSet();
}
}

Note that, although ds is local to the SelectQuery function, it has a
Count=1 when returned. What could be wrong?

 

[Ludwig Stuyck]

When executing the following code

ArrayList list = new ArrayList();
try
{
//Make sure connection is open
if (!connection.State.ToString().Equals("Open"))
connection.Open();

DataSet list = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select " + m_TableSerieColName + "
from " + m_TableSerie, connection);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[m_TableSerie].Rows) //<-----
list.Add((string) dr[m_TableSerieColName]);
}
catch (SqlException exc)
{
MessageBox.Show(exc.Message);
return;
}

I get the following exception:
---
An unhandled exception of type 'System.NullReferenceException' occurred in
SportsResult.exe

Additional information: Object reference not set to an instance of an object.
---
on the line

foreach (DataRow dr in ds.Tables[m_TableSerie].Rows) //<-----

Rows has a length of 0, so what I expect is the foreach loop to execute 0
times, but instead I then get the exception. Why?

Are you sure that ds.Tables[m_TableSerie] is not null?

 

[Guest]

Yes.

I managed to solve the problem. If I instead used DataTable instead of
DataSet , and made som additional changes, it worked. The problem seemed to
be that the DataSet didn't name the table properly (by default the table was
called "Table").

Thanks

When executing the following code

ArrayList list = new ArrayList();
try
{
//Make sure connection is open
if (!connection.State.ToString().Equals("Open"))
connection.Open();

DataSet list = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select " + m_TableSerieColName + "
from " + m_TableSerie, connection);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[m_TableSerie].Rows) //<-----
list.Add((string) dr[m_TableSerieColName]);
}
catch (SqlException exc)
{
MessageBox.Show(exc.Message);
return;
}

I get the following exception:
---
An unhandled exception of type 'System.NullReferenceException' occurred in
SportsResult.exe

Additional information: Object reference not set to an instance of an object.
---
on the line

foreach (DataRow dr in ds.Tables[m_TableSerie].Rows) //<-----

Rows has a length of 0, so what I expect is the foreach loop to execute 0
times, but instead I then get the exception. Why?

Are you sure that ds.Tables[m_TableSerie] is not null?

 

[James Curran]

I managed to solve the problem. If I instead used DataTable instead of
DataSet , and made som additional changes, it worked. The problem seemed to
be that the DataSet didn't name the table properly (by default the table was
called "Table").

True, it does not take the name from the underlying SQL statement.

Note that in your particular case, you are requesting only one column from
that table. To call that result set by the name of the database table would
be wrong, as it does not represent the table (which presumably has more
columns).

Further, in many case the result set is created by a join of two or more
different tables. If it were to base the DS TableName on such a query,
which table name should it use?

In the end, to have the DS TableName match the name of the table used in the
query would require the framework do a complex parsing the SQL statement,
only to determine that in most cases, it would have to use the default
anyway.

 

[Marc Scheuner [MVP ADSI]]

I managed to solve the problem. If I instead used DataTable instead of

DataSet , and made som additional changes, it worked. The problem seemed to
be that the DataSet didn't name the table properly (by default the table was
called "Table").

Yes, that's the documented and expected behaviour - if you want to
change it, you'll have to NAME the table inside the data set in the
".Fill" call:

DataSet ds = new DataSet();
da.Fill(ds, m_TableSerie); <<== ADD THE NAME FOR THE RESULTING TABLE !!

If you don't specify a name for the table(s), the first one will be
called "Table", the second one "Table1", the third one "Table2" and so
on.

Marc
================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch