save data from a *.sdf database to variables or an array [VB.NET] ?

[Thomas P.]

Hi,

I use the following VB.NET 2003 code to read some data from a *.sdf
database and write it into a DataGrid.

cn = New SqlCeConnection("Data Source = \My
Documents\Test.sdf;Password=")
cn.Open()
Dim cmd As SqlCeCommand = cn.CreateCommand
cmd.CommandText = "Select number From Test"
Dim da As New SqlCeDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.Enabled = False
DataGrid1.DataSource = ds.Tables(0)


This works fine but now I like to save the sorted output data in some
variables or an array. How can I do this? The DataGrid is not needed
any longer. I used it only for a test-output.
It wold be great if someone can give some detailed code for my
problem. Please try to explain it simple because I am a newbee.


Thanks.

Thomas Peterson

 

[Peter Foot [MVP]]

What you have to work with is a DataSet object which contains a DataTable
with your data, as it appears in your grid.
A DataTable has a Rows collection which allows you to access an individual
row of data, each row can be indexed by number or by field name to access an
individual field. Therefore say for arguments sake your query produced this
table:-
number
-------
1
3
8
2
5

You could access the first item using:-
ds.Tables(0).Rows(0)("number")
or
ds.Tables(0).Rows(0)(0)


You could iterate through the entire table using a For Each loop:-
For Each thisrow As System.Data.DataRow In ds.Tables(0)
'display each item in a message box
MessageBox.Show(thisrow("number"))
Next

It would probably be more suitable to query the DataTable when you need a
value, rather than copying the values to some other array or variable. What
you do next really depends what you want to do with the data, I'm sure we
can come up with a more useful sample and explanation if you detailed what
you want to do with this retrieved data next.

Regards,

Peter

 

[Thomas P.]

Hi,

thank you for your help. This is nearly what I was looking for.

But your loop doesn't work. It seams that there is a syntax error in
"...In ds.Tables(0)" because Visual Studio underlines this code.

I am also looking for some code that returns an integer value with the
total number of rows to me. I think that this code is also needed in
the loop which is not working until now.

I tried the follwoing code but it does not work. Your loop which the
MessageBox does not work, too. The error is always somewhere around
ds.Tables(0)


Dim i as Integer = 0

For Each thisrow As System.Data.DataRow In ds.Tables(0)

i = i + 1
Next


Regards,

Thomas

 

[Jonathan Wells [msft]]

Hi Thomas, try:
For Each thisrow As System.Data.DataRow In ds.Tables(0).Rows
'display each item in a message box
MessageBox.Show(thisrow("number"))
Next

Note the ".Rows"...

cheers jonathan
--
Jonathan Wells
Product Manager
..NET Compact Framework
Check out the .NET Compact Framework FAQ at:
http://msdn.microsoft.com/mobility/prodtechinfo/devtools/netcf/FAQ/default.aspx

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

 

[John Atkins]

You need something like:

For Each thisrow As System.Data.DataRow In ds.Tables(0).Rows

The count can be obtained from ds.Tables(0).Rows.Count

John.

 

[Thomas P.]

Thanks. this works fine now.

Regards,

Thomas