Why exception when going through the DataSet?

28 Mar 2005 11:57 AM

Joachim

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?

28 Mar 2005 12:05 PM

Joachim

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?

Show quoteHide quote

"Joachim" wrote:

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

28 Mar 2005 12:26 PM

Ludwig Stuyck

On Mon, 28 Mar 2005 03:57:02 -0800, "Joachim"
<Joac***@discussions.microsoft.com> wrote:

Show quoteHide quote

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

--
Ludwig Stuyck
http://www.coders-lab.net

28 Mar 2005 1:23 PM

Joachim

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

Show quoteHide quote

"Ludwig Stuyck" wrote:

> On Mon, 28 Mar 2005 03:57:02 -0800, "Joachim"
> <Joac***@discussions.microsoft.com> wrote:
>
> >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?
>
> --
> Ludwig Stuyck
> http://www.coders-lab.net
>

28 Mar 2005 9:05 PM

James Curran

"Joachim" <Joac***@discussions.microsoft.com> wrote in message
news:34DAAE05-A539-4E7B-BDB1-DF6318923505@microsoft.com...
> 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.

29 Mar 2005 5:49 AM

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