RowSelect

[fniles]

I use Dataset and to select certain records in the Dataset I use the SELECT
method (m_dsSQL.Tables(0).Select("colB > 0") ).
I am looping until colB <= 0.
The problem is, after I fill the dataset and do SELECT, if inside the LOOP I
change the value of ColB, it is not being reflected in the Dataset SELECT.
Is there a way in the LOOP to reflect the changes that has been made in colB
?
For ex:

Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_cmdSQL As SqlClient.SqlCommand
Dim m_dsSQL As DataSet
Dim aRowHistTrades As DataRow()
dim bContinue as boolean
dim sSQL as string

sSQL = "select * from myTable where ColA = 'ABC'"
m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = m_cmdSQL
m_daSQL.Fill(m_dsSQL)
bContinue = True
'--->Here i want to loop thru myTable until colB is <= 0
Do While bContinue
'--->after the 1st iteration, colB value is changed, but the code below
does not reflect that.
'--->For ex: ColB starts with value = 2.5, after 1st iteration, in the
database colB = 1.5
'--->but the following code does not reflect that, colB stays at 2.5,
thus aRowHistTrades.Length is always > 0
aRowHistTrades = m_dsSQL.Tables(0).Select("colB > 0")
If aRowHistTrades.Length > 0 Then
: --> If ColB is still > 0 then, do some codes that changes/reduces the
value of ColB
colB = aRowHistTrades(0).Item("colB") '--> after 1st iteration,
this value is still the same with before 1st iteration
Else
bContinue = False
End If
Loop

Thank you.

 

[Cor Ligthert[MVP]]

fniles,

A dataset is not a recordset. There is not any equality in it. What looks
the most as a recordset is a datatable, with the main difference that a
recordset is always connected to the database and the datatable is (without
additions) forever disconnected.

This means that you fill a datatable, make mutation to it, and then update
it.

For updating you can set the Update, Delete and Insert properties in the
dbcommandmethod that you are using.

For the later you can use as well the dbcommandbuilder.

In your case I would first try this with a sample and the NorthWind sample
database.

Here a sample that is easy to make, don't use it in real life, it will eat
up your process as in fact the recordset can do (the recordset is from the
time that 100 concurrent users was in most cases much).

http://www.vb-tips.com/SQLServerUpdate.aspx

Cor