Newbie question on Datatable

[Joe Soap]

Hello all.

I am starting to get the hang of this all now (VB 2008 from VB6). I must
admit, I like it, but the learnig curve is steep.

A couple of questions:

1. I return a datatable to load up my combobox.
In VB6, I would check that the recordset pointer is at the top..ie the
recordset pointer is not, for some reason, pointing at a record halfway down
the recordset..can happen. So I would use something like this:

....................
.........
If rsgeneric.RecordCount > 0 Then
rsgeneric.MoveLast
rsgeneric.MoveFirst <'*I can also get an
accurate recordset count at the same time

..........

Do I have to do the same with a datatable, or is the returned dataset
pointer always positioned at row 0? If not, what is the syntax please?
At the moment I am stuck with:
If m_DataTable.Rows.Count > 0 Then
m_DataTable.(???).(???)

and I want the dataset pointer to be positioned at the first record ,
as with my old recordset.

2. The second question is not related to this, but it is something that I
have wanted to do for a long time, and I am quite excited about the
possiblities:

In VB6, if I get a recordset that I want to manipulate further, ie, get a
subset of the recordset, I have to dump the recordset into a temp table and
then work on that, writing back my computations or whatever to another
recordset for insertion into another db table if I want to keep the results.

With a datatable, can I further manipulate the data without dumping to a
temp table? For example..I have a recordset of all people who used a
particular toothpaste. At a later stage..in a function, I may want to refer
to that particular recordset and get a sub group of those who..for
example..are married. I could write another querty, but as my data is
already contained in the recordset, why should I? In VB6, the only way to
get a sub-group of the recordset is to dump the recordset into a temp table,
and then re-query it for my sub group. So, can I get a sub group directly
from a datatable?

Thank you for your time and any help that you can off.

Best regards

David

 

[Armin Zingler]

Do I have to do the same with a datatable, or is the returned dataset
pointer always positioned at row 0? If not, what is the syntax
please? At the moment I am stuck with:
If m_DataTable.Rows.Count > 0 Then
m_DataTable.(???).(???)

and I want the dataset pointer to be positioned at the first
record , as with my old recordset.

There is no "dataset pointer". You can access any row in the datatable at
any time.

m_DataTable.rows(17)

returns the 18th row.


Armin

 

[Cor Ligthert[MVP]]

David,

It is a long time ago there were a lot of questions about this. However,
therefore I don't search for the old message but write it again.

You have much more ways to go trough a datatable then through a recordset.

The most basic one is unordered:
dt.Rows(0) = the first row
dt.Rows(rows.count-1) = the last row
dt.Rows(1) = the sectond row etc.

But you can also set an order
dim dv as dataview = dt.DefaultView
dv.Sort = "ID"
dv(0) = lowest ID etc

You can as well use Linq with all its posibilities

Most important thing for persons comming from VB6, VB.Net is an object
oriented language, which means that everything is created from a class.
The DataTable is an objects which exist somewhere (managed heap) and is
often a part of a dataset but only the reference to that object is in the
dataset.

The DataTable itself has as well objects from which is the datarow one of
the most important. That datarow does not exist inside the datatable
(although is often written like that to make it simple). The dataset holds
only collecions of references to those datarows.

Cor

etc etc

Cor






But you have as well the defaultiew

 

[Joe Soap]

Thank you for those good answers.

Regards

David

 

[Michel Posseth [MCP]]

Hello Joe

I am starting to get the hang of this all now (VB 2008 from VB6). I must
admit, I like it, but the learnig curve is steep.

As a VB6 coder you probably own or know of "Programming Microsoft Visual
basic 6" from Francesco Balena
well he also wrote "Programming Microsoft Visual basic .Net", "Programming
Microsoft Visual basic 2003" and "Programming Microsoft Visual basic 2005"
wich the last is still perfectly valid for 2008 also.

Fine thing from balena is that he gives special attention to VB6 coders in
his explanations

http://astore.amazon.com/vbdotnetcoder-20


about your questions


Dataset is a container around multiple datatables and / or data adapters (
note that you can have a datatable without a data adapter and vice versa )

Datatable is the equivalant of a recordset object in .Net

DataRow is the equivalant of one single row in a recordset object

As .Net standard uses a disconnected aproach ( all data is loaded in
memory ) this is not necesary , the "problem" you describe was in VB6 only
necesary
with a server side cursor as the RS only fetches data that is needed at the
time of operation

Note that .Net does not support a editable server side aproach as you could
in VB6 , we are now dealing with a read write aproach the same as you had in
VB6 with a client side connection

if this looks familiar to you in VB6

Dim rsm As ADODB.Recordset
Set rsm = New ADODB.Recordset
With rsm
.CursorLocation = adUseClient
.Open SQLM, cnTbdb, adOpenStatic, adLockReadOnly
Set .ActiveConnection = Nothing
If Not .EOF Then
''''''''code omited
''''''''...........
End If
If .State <> adStateClosed Then .Close
End With
Set rsm = Nothing

Well in that case the learning curve is less steep as you might think , it
is more the same beast with a different name and some less typing as in VB6

Do I have to do the same with a datatable, or is the returned dataset
pointer always positioned at row 0? If not, what is the syntax please?
At the moment I am stuck with:
If m_DataTable.Rows.Count > 0 Then
m_DataTable.(???).(???)

and I want the dataset pointer to be positioned at the first record ,
as with my old recordset.

I personally use the for Each aproach ( hey that sounds familiair again )


For Each DR in Dataset.Datatable
'code that uses / manipulates the rows
'you now loop from row 0 to the end

Next

If m_DataTable.Rows.Count > 0 Then
m_DataTable.(???).(???)

if you use the for each aproach this is not necesary as you would with a
RowCount of 0 never enter the for each construct
however if you know the count you could indeed do this

For i = 0 to m_DataTable.Count -1

with m_DataTable.rows(i)

end with

Next

But in my homble opinion you are then making it yourself only more
complicated but possible it is .

2. The second question is not related to this, but it is something that I
have wanted to do for a long time, and I am quite excited about the
possiblities:

In VB6, if I get a recordset that I want to manipulate further, ie, get a
subset of the recordset, I have to dump the recordset into a temp table
and then work on that, writing back my computations or whatever to another
recordset for insertion into another db table if I want to keep the
results.

With a datatable, can I further manipulate the data without dumping to a
temp table? For example..I have a recordset of all people who used a
particular toothpaste. At a later stage..in a function, I may want to
refer to that particular recordset and get a sub group of those who..for
example..are married. I could write another querty, but as my data is
already contained in the recordset, why should I? In VB6, the only way to
get a sub-group of the recordset is to dump the recordset into a temp
table, and then re-query it for my sub group. So, can I get a sub group
directly from a datatable?

Well you step on my soul with this question cause this was already possible
in VB6
it was / is called "Optimistic client batch Updates"

In a nuttshell ADO in VB6 lets you create Recordsets on wich you can perform
all the commands you want to including deletes , inserts and updates without
affecting the original database .

But indeed .Net datasets addopted this aproach as the standard the main
differnce with the "client batch updates" in VB6 is that it now only takes a
few lines of code while in VB6 it took a few more .

In my opinion if you are a VB6 who was is already familiar with ADODB and
especially with a ADODB disconnected aproach well then ADO.Net is peanuts
for you and you learn it in hours .

But you need a good reference guide and i can sure recomend the bookst that
are in the provided link

regards

Michel Posseth

 

[Joe Soap]

Michel, thank you very much for that lovely reply and explanation. I shall
indeed look out for the book as you suggest. I am getting along quite
alright with VB 2008..1.5 days and counting..I already have built a small
app that uses SQL Server 2008, combo boxes and the like..it is just getting
use to the different objects and methods and events that is a bit of a
frustrating slog.

Concerning your soul:
Maybe I missed something in VB6...if I get a recordset such as

strSQL = "SELECT Name, Age, SexID, Marital_Status, " _
& "Toothpaste_BrandID, Hair_ColourID FROM " _ &"SomeTable " _
& "WHERE Toothpaste_BrandID = 4 Marital_Status = 3"
Set rsgeneric = cnFOne.Execute(strSQL, , adCmdText)
Set rsgeneric.ActiveConnection = Nothing
strSQL = ""

OK, now I have a (rather silly) recordset to play with.

I can assign variables etc from the recordset, and if I have the right
cursor type, I can delete insert etc.
But what if I wanted to re-order the same very specific recordset into
another very recordset..such as:

strSQL = "SELECT SexID, " _
& "Toothpaste_BrandID, Hair_ColourID " _
& "FROM rsgeneric " _
& "WHERE Hair_ColourID = 6"

Set rsNewgeneric = cnFOne.Execute(strSQL, , adCmdText)
Set rsNewgeneric .ActiveConnection = Nothing
strSQL = ""

OK, it is a silly example, but I have many other instances where comparisons
are made on a temp table that was derived from a recordset.

What I am thinking of here is a derived recordset in a similar vein to an
new Access recordset that is derived from an Access Query. My understand is
that you can't do this with an ADO recordset. Am I wrong?

Best regard

David

 

[Michel Posseth [MCP]]

Hello Joe ,

What I am thinking of here is a derived recordset in a similar vein to an
new Access recordset that is derived from an Access Query. My understand
is that you can't do this with an ADO recordset. Am I wrong?

No AFAIK you can`t do that in VB6 and even in .Net this is only possible
with LINQ
and YODA wich is actually SQL in reverse order named after the litle green
Yedi master Yoda

Famous Yoda lines :
"Size matters not. Look at me. Judge me by my size, do you? Hmm? Hmm. And
well you should not. For my ally is the Force, and a powerful ally it is.
Life creates it, makes it grow. Its energy surrounds us and binds us.
Luminous beings are we, not this crude matter. You must feel the Force
around you; here, between you, me, the tree, the rock, everywhere, yes. Even
between the land and the ship."

Copy paste from a real life project of mine

'fill the datatable through my IO routine with the data from SQL server

Using VmeterErr As New VMeterErrCode.VMeterErrCode
VmeterErr.Ta.Fill(Me.DsVMeterErrCode.VMeterErrCode)
End Using


'now select only the meters with this articlenumber using LINQ
Dim ErrMeterVals = From e In DsVMeterErrCode.VMeterErrCode _
Select e Where e.ArticleNumber = ArticleNumber

If ErrMeterVals.Count > 0 Then
' code omitted

End If

ErrMeterVals now contains a subset of my original datatable and although it
is now intact you could also change the returned data structure ( remove
columns , change order etc etc ) see the examples in the below link


You wil for sure love LINQ as it makes data operations so much simpler
so as you see watching Star Wars can actually improve your coding skills
http://msdn.microsoft.com/en-us/vbasic/bb688086.aspx?ppud=4

regards

Michel

 

[Joe Soap]

Thank you Michel

I will leave LINQ and YODA alone for a while..bad enough catching up with
all of this new VB 2008 core stuff.

Best regards, and once again, thank you for your time

David