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.

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




"fniles"  schreef in bericht 
news:%23wumLDS2HHA.464@TK2MSFTNGP02.phx.gbl...

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

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




"fniles"  schreef in bericht 
news:%23wumLDS2HHA.464@TK2MSFTNGP02.phx.gbl...

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