Single element in a DataSet

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

OK, I have filled a DataSet with an SqlDataAdapter and I just want the data
in one column of one row. How can I do this since the following gives me
the infamous 'Object reference not set to an instance of an object' error:
strng = datset.Tables("Users").Rows(0).Item("Users").ToString()
My table is called Users and I want the first row, and the only column is
also called Users.
I broke it down by declaring a DataTable and a DataRow etc and the problem
comes up at the Rows stage, I can assign tbl=datset.Tables("Users") with no
problem, but cannot assign
rw=tbl.Rows(0) without getting the error above.
And you can't declare a New DataRow because the New constructor is Private

So can someone please give me example code on how to extract one element
from a DataSet into a variable so it can be used in code. MSDN only gives
examples on how to manipulate data while still in the DataSet or how to bind
it, rarely how to extract it in code.
The one example they do give uses
For Each tbl In datset.Tables
For Each rw In tbl.Rows
For Each col In tbl.Columns
Debug.Print(rw(col))
Next
Next
Next
But I still get the error when I try doing this, in the For Each rw In
tbl.Rows. Somehow I can't get any rows at all.
 
The only thing I can think of is weather you did a fill..??

OleDbDataAdapter1.Fill(DataSet1)

and is your DataSet or DataSet1?

Also if you add the OleDataAdapter to your form you can do a preview data to
see what should be coming in... so the SQLDataAdapter should be the same..
does your data look correct at that point?

Hope this helps..
Ronald Walker
 
Try

if datset.Tables.Contains("Users") then
strng = datset.Tables("Users").Rows(0).Item("Users").ToString()
end if

This will ensure that the table you want to use exists in the dataset.

Greetings
Daniel Walzenbach
 
It does not sound like the query you used returned a rowset to be passed
into a DataTable by the Fill method. Let's see some code.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks Ronald, Daniel and Bill for your reply.

Hi Stephen,

An 'Object reference not set to an instance of an object' Exception means
that in your code: datset.Tables("Users").Rows(0).Item("Users").ToString(),
some object reference doesn't point to a valid object. Try to set a break
point on this statement and check the following references to see if they
are pointed to a valid object:

datset
datset.Tables("Users")
datset.Tables("Users").Rows(0)
datset.Tables("Users").Rows(0).Item("Users")

In addition, if you're trying to return the item on the first row of the
first column from a SqlCommand, try to use the SqlCommand.ExecuteScalar()
method. Please see the following link for reference:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlcommandclassexecutescalartopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: "Stephen" <[email protected]>
| Subject: Single element in a DataSet
| Date: Wed, 8 Oct 2003 17:42:49 -0400
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.v
b,microsoft.public.dotnet.languages.vb.data
| NNTP-Posting-Host: c-66-177-177-97.se.client2.attbi.com 66.177.177.97
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:145143
microsoft.public.dotnet.languages.vb.data:2162
microsoft.public.dotnet.framework.adonet:63246
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| OK, I have filled a DataSet with an SqlDataAdapter and I just want the
data
| in one column of one row. How can I do this since the following gives me
| the infamous 'Object reference not set to an instance of an object' error:
| strng = datset.Tables("Users").Rows(0).Item("Users").ToString()
| My table is called Users and I want the first row, and the only column is
| also called Users.
| I broke it down by declaring a DataTable and a DataRow etc and the problem
| comes up at the Rows stage, I can assign tbl=datset.Tables("Users") with
no
| problem, but cannot assign
| rw=tbl.Rows(0) without getting the error above.
| And you can't declare a New DataRow because the New constructor is Private
|
| So can someone please give me example code on how to extract one element
| from a DataSet into a variable so it can be used in code. MSDN only gives
| examples on how to manipulate data while still in the DataSet or how to
bind
| it, rarely how to extract it in code.
| The one example they do give uses
| For Each tbl In datset.Tables
| For Each rw In tbl.Rows
| For Each col In tbl.Columns
| Debug.Print(rw(col))
| Next
| Next
| Next
| But I still get the error when I try doing this, in the For Each rw In
| tbl.Rows. Somehow I can't get any rows at all.
|
|
|
 
Thanks for the suggestions guys. Turns out when you target a specific table
with an SqlAdapter, when it puts it into the DataSet it doesn't use the
original table name (I assume b/c there is only one), but instead changes
the Table.TableName to "Table" I'm now having no trouble with that part of
my code (but still pulling my hair out over some strange LoadControl errors,
look in aspnet newsgroup)

Stephen
 
Stephen said:
Thanks for the suggestions guys. Turns out when you target a specific table
with an SqlAdapter, when it puts it into the DataSet it doesn't use the
original table name (I assume b/c there is only one), but instead changes
the Table.TableName to "Table" I'm now having no trouble with that part of
my code (but still pulling my hair out over some strange LoadControl errors,
look in aspnet newsgroup)
Click to expand...

You can change which table is used in the DataSet by using the TableMappings
property of the DataAdapter.
 
Is your Datatable really named "Users"? I bet it is named "Table", which is
the default. Look at TableMappings.
 
No, it REALLY IS called Users. I designed the table myself in the Server
Explorer. Trust me.
 
You might have missed the point. When you use Fill, you can change the name
of the DataTable(s) which are used to refer to the rowset(s) returned by the
query. These are not set to the name of the root table in the server
automatically. If you choose "Fred",
ds = da.Fill("Fred")
the first DataTable is named Fred and the second Fred1 etc. You use the
TableMappings collection to tie the logical tables to the physical root
database tables so the Update method knows how to address the right table.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top