Updating database in a loop

[fniles]

I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
For every record in tblA where colB = 'abc', I want to update the value in
colA.
In VB6, using ADO I can loop thru the recordset,set the values of colA and
call the Update method.
How can I do this in VB.NET and SqlDataAdapter ? Thank you.

m_cmdSQL = New SqlClient.SqlCo mmand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDa taAdapter
m_dsSQL = New DataSet
m_daSQL.SelectC ommand = "select * from tblA where [colB] = 'abc'"
m_daSQL.Fill(m_ dsSQL)
lRow = 0
For Each aRow In m_dsSQL.Tables( 0).Rows()
m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
updates the value in memory, but not in the database
lRow = lRow + 1
Next

[Scott M.]

Re: Updating database in a loop

You'll need to create/configure the dataadapter's InsertCommand and its
CommandText property with your own update logic. Once you've done that you
simply add:

m_daSQL.Update

to your code.

"fniles" <fniles@pfmail. comwrote in message
news:%23ZJe9TwH HHA.2112@TK2MSF TNGP03.phx.gbl. ..

>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
For every record in tblA where colB = 'abc', I want to update the value in
colA.
In VB6, using ADO I can loop thru the recordset,set the values of colA and
call the Update method.
How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>
m_cmdSQL = New SqlClient.SqlCo mmand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDa taAdapter
m_dsSQL = New DataSet
m_daSQL.SelectC ommand = "select * from tblA where [colB] = 'abc'"
m_daSQL.Fill(m_ dsSQL)
lRow = 0
For Each aRow In m_dsSQL.Tables( 0).Rows()
m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
>
>

[fniles]

Re: Updating database in a loop

Thank you.
I am sorry, I am still confused.
In my example, I Fill the dataset using CommandText "select * from tblA
where [colB] = 'abc'", before I update it.
If I need to use InsertCommand, can I use the same DataAdapter/DataSet ?
Thanks

m_cmdSQL = New SqlClient.SqlCo mmand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDa taAdapter
m_dsSQL = New DataSet
m_daSQL.SelectC ommand = "select * from tblA where [colB] = 'abc'"
m_daSQL.Fill(m_ dsSQL)
lRow = 0
For Each aRow In m_dsSQL.Tables( 0).Rows()
sSQL = "update tblA set"
sSQL = sSQL & (" colA = " & lrow)
sSQL = sSQL & (" where [colB] = 'abc')
m_daSQL.Update ---how can I use the same m_daSQL that is already filled
with the "select * from tblA where [colB] = 'abc'" ?
lRow = lRow + 1
Next

"Scott M." <s-mar@nospam.nosp amwrote in message
news:%23pT%23oZ wHHHA.1248@TK2M SFTNGP03.phx.gb l...

You'll need to create/configure the dataadapter's InsertCommand and its
CommandText property with your own update logic. Once you've done that
you simply add:
>
m_daSQL.Update
>
to your code.
>
"fniles" <fniles@pfmail. comwrote in message
news:%23ZJe9TwH HHA.2112@TK2MSF TNGP03.phx.gbl. ..

>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
>For every record in tblA where colB = 'abc', I want to update the value
>in colA.
>In VB6, using ADO I can loop thru the recordset,set the values of colA
>and call the Update method.
>How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>>
>m_cmdSQL = New SqlClient.SqlCo mmand
>With m_cmdSQL
>.Connection = adoCon
>.CommandText = sSQL
>End With
>m_daSQL = New SqlClient.SqlDa taAdapter
>m_dsSQL = New DataSet
>m_daSQL.Select Command = "select * from tblA where [colB] = 'abc'"
>m_daSQL.Fill(m _dsSQL)
>lRow = 0
>For Each aRow In m_dsSQL.Tables( 0).Rows()
> m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
>updates the value in memory, but not in the database
> lRow = lRow + 1
>Next
>>
>>

>
>

[Spam Catcher]

Re: Updating database in a loop

"fniles" <fniles@pfmail. comwrote in
news:#ZJe9TwHHH A.2112@TK2MSFTN GP03.phx.gbl:

For every record in tblA where colB = 'abc', I want to update the
value in colA.

Why don't you write a SQL statement:

Update tblA set colA = 'SomeValue' where colB = 'abc'

[Scott M.]

Re: Updating database in a loop

Your DataAdapter goes and gets a copy of the data you want (based on the
Select statement you set up in the DataAdapter's select command). The copy
is placed in your dataset. You make changes to your dataset and then you
call the DataAdapter's update method. But you must set up the UpdateCommand
so that it does the update you want it to.

By the way, the m_ prefix you are using is not a recommened naming
convention and will cause more confusion than anything else. Also, don't
use the prefix of "ado" for your connection name since you aren't using ado
anyway, you're using ADO.NET.

Here's your code again (modified for better naming conventions and correct
coding):

Diim con As New SqlClient.SqlCl ientConnection( "connection string here")
Dim da As New SqlClient.SqlDa taAdapter("sele ct * from tblA where [colB] =
'abc'", con)
Dim updateCommand As New SqlClient.SqlCl ientCommand("UP DATE tblA blah, blah,
blah")
Dim ds As New DataSet

da.UpdateComman d = updateCommand

Try
daSQL.Fill(ds, "tblA")
Dim i As Integer
For i = 0 To ds.Tables(0).Ro ws().Count -1
'Forget about update statements here, you are working with
'a disconnected DataSet now, so just make whatever changes
'you need to the DataRows that this loop iterates over
If ds.Tables(0).Ro ws(i).Columns(" colB") = "abc" Then
ds.Tables(0).Ro ws(i).Columns(" colA") = i
End If
Next

da.Update()

Catch ex As Exception
'handle exceptions here
Finally
con.close() 'not needed if the connection was closed to begin with
End Try









"fniles" <fniles@pfmail. comwrote in message
news:uHVbakwHHH A.3312@TK2MSFTN GP03.phx.gbl...

Thank you.
I am sorry, I am still confused.
In my example, I Fill the dataset using CommandText "select * from tblA
where [colB] = 'abc'", before I update it.
If I need to use InsertCommand, can I use the same DataAdapter/DataSet ?
Thanks
>
m_cmdSQL = New SqlClient.SqlCo mmand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDa taAdapter
m_dsSQL = New DataSet
m_daSQL.SelectC ommand = "select * from tblA where [colB] = 'abc'"
m_daSQL.Fill(m_ dsSQL)
lRow = 0
For Each aRow In m_dsSQL.Tables( 0).Rows()
sSQL = "update tblA set"
sSQL = sSQL & (" colA = " & lrow)
sSQL = sSQL & (" where [colB] = 'abc')
m_daSQL.Update ---how can I use the same m_daSQL that is already filled
with the "select * from tblA where [colB] = 'abc'" ?
lRow = lRow + 1
Next
>
"Scott M." <s-mar@nospam.nosp amwrote in message
news:%23pT%23oZ wHHHA.1248@TK2M SFTNGP03.phx.gb l...

>You'll need to create/configure the dataadapter's InsertCommand and its
>CommandText property with your own update logic. Once you've done that
>you simply add:
>>
>m_daSQL.Upda te
>>
>to your code.
>>
>"fniles" <fniles@pfmail. comwrote in message
>news:%23ZJe9Tw HHHA.2112@TK2MS FTNGP03.phx.gbl ...

>>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
>>For every record in tblA where colB = 'abc', I want to update the value
>>in colA.
>>In VB6, using ADO I can loop thru the recordset,set the values of colA
>>and call the Update method.
>>How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>>>
>>m_cmdSQL = New SqlClient.SqlCo mmand
>>With m_cmdSQL
>>.Connection = adoCon
>>.CommandTex t = sSQL
>>End With
>>m_daSQL = New SqlClient.SqlDa taAdapter
>>m_dsSQL = New DataSet
>>m_daSQL.Selec tCommand = "select * from tblA where [colB] = 'abc'"
>>m_daSQL.Fill( m_dsSQL)
>>lRow = 0
>>For Each aRow In m_dsSQL.Tables( 0).Rows()
>> m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
>>updates the value in memory, but not in the database
>> lRow = lRow + 1
>>Next
>>>
>>>

>>
>>

>
>

[aaron.kempf@gmail.com]

Re: Updating database in a loop

lose the training wheels; learn how to WRITE SQL and don't rely on
recordsets to update data

fucking retard

this is a simple command; if you can't write subqueries and shit then
you should STFU and go work at mcDonalds

-Aaron


fniles wrote:

I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
For every record in tblA where colB = 'abc', I want to update the value in
colA.
In VB6, using ADO I can loop thru the recordset,set the values of colA and
call the Update method.
How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>
m_cmdSQL = New SqlClient.SqlCo mmand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDa taAdapter
m_dsSQL = New DataSet
m_daSQL.SelectC ommand = "select * from tblA where [colB] = 'abc'"
m_daSQL.Fill(m_ dsSQL)
lRow = 0
For Each aRow In m_dsSQL.Tables( 0).Rows()
m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
updates the value in memory, but not in the database
lRow = lRow + 1
Next

[Branco Medeiros]

Re: Updating database in a loop

fniles wrote:
<back posted/>

For the specific update you want to perform (setting each colA to a
distinct, zero-based index for all rows where colB = 'abc') , you don't
even need to fetch the records from the DB; the following would
suffice:

<aircode>
Dim SQL As String = "declare @val int; " _
& "set @val = -1; " _
& "update tblA Set @val = colA = @val + 1 where colB = 'abc'"

'Assuming a connection Con exists and is already open
Dim Cmd As New SqlClient.SqlCo mmand(SQL, Con)
Dim Count As Integer = Cmd.ExecuteNonQ uery()
</aircode>

Now, if you want to use a more generic approach, then you must set up
an update command, as pointed out by Scott.

The update command is a SQL string that would update a specific row
given a set of new values, passed as parameters. For instance, it could
be:

"Update tblA set colA = @colA where tblAID = @tblAID"

As you can see, you'd need a way to inequivocally specify the row you
need to update, probably using a primary key (named tblAID, here).

I'm not sure if you can use a different set of columns than the ones
used by the select query, I guess you'll have to do some testings.

Just to give you a headstart in the matter, the following would perform
the update using this approach:

<aircode>
'Assuming an open connection Con

Dim Adapter As New SqlClient.SqlDa taAdapter

'the SELECT cmd
Dim Cmd As New SqlClient.SqlCo mmand( _
"select tblAID, colA from tblA where colB = 'abc'", _
Con)
Adapter.SelectC ommand = Cmd

'the UPDATE cmd
Cmd = New SqlClient.SqlCo mmand( _
"update tblA set colA=@colA where tblAID=@tblAID" , _
Con)
Cmd.Parameters. Add("@colA", SqlDbType.Int, 5, "colA")
Cmd.Parameters. Add("@tblAID", SqlDbType.Int, 5, "tblAID")
Adapter.UpdateC ommand = Cmd

'retrieves the data
Dim Ds As New DataSet
Adapter.Fill(Ds )

'modify rows
Dim Index As Integer = 0
For Each Row As DataRow In Ds.Tables(0).Ro ws
Row("colA") = Index
Index += 1
Next

'update the source table
Adapter.Update( Ds)
</aircode>

HTH.

Regards,

Branco.

I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
For every record in tblA where colB = 'abc', I want to update the value in
colA.
In VB6, using ADO I can loop thru the recordset,set the values of colA and
call the Update method.
How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>
m_cmdSQL = New SqlClient.SqlCo mmand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDa taAdapter
m_dsSQL = New DataSet
m_daSQL.SelectC ommand = "select * from tblA where [colB] = 'abc'"
m_daSQL.Fill(m_ dsSQL)
lRow = 0
For Each aRow In m_dsSQL.Tables( 0).Rows()
m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
updates the value in memory, but not in the database
lRow = lRow + 1
Next

[Cor Ligthert [MVP]]

Re: Updating database in a loop

Fniles,

In addition to Scott,

You can in simple situations like this as well use the commandbuilder, that
makes dynamicly the Insert, the Update and Delete commands for you.

dim cmd as New SqlClient.Comma ndbuilder(m_daS QL)
(_daSQL.Update( m_dsSQL)


(Like Scott I don't see the use for the m_. This has only sense to distinct
a variable private member where that would have the same name as a property)

I hope this helps,

Cor

"fniles" <fniles@pfmail. comschreef
in bericht news:uHVbakwHHH A.3312@TK2MSFTN GP03.phx.gbl...

Thank you.
I am sorry, I am still confused.
In my example, I Fill the dataset using CommandText "select * from tblA
where [colB] = 'abc'", before I update it.
If I need to use InsertCommand, can I use the same DataAdapter/DataSet ?
Thanks
>
m_cmdSQL = New SqlClient.SqlCo mmand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDa taAdapter
m_dsSQL = New DataSet
m_daSQL.SelectC ommand = "select * from tblA where [colB] = 'abc'"
m_daSQL.Fill(m_ dsSQL)
lRow = 0
For Each aRow In m_dsSQL.Tables( 0).Rows()
sSQL = "update tblA set"
sSQL = sSQL & (" colA = " & lrow)
sSQL = sSQL & (" where [colB] = 'abc')
m_daSQL.Update ---how can I use the same m_daSQL that is already filled
with the "select * from tblA where [colB] = 'abc'" ?
lRow = lRow + 1
Next
>
"Scott M." <s-mar@nospam.nosp amwrote in message
news:%23pT%23oZ wHHHA.1248@TK2M SFTNGP03.phx.gb l...

>You'll need to create/configure the dataadapter's InsertCommand and its
>CommandText property with your own update logic. Once you've done that
>you simply add:
>>
>m_daSQL.Upda te
>>
>to your code.
>>
>"fniles" <fniles@pfmail. comwrote in message
>news:%23ZJe9Tw HHHA.2112@TK2MS FTNGP03.phx.gbl ...

>>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
>>For every record in tblA where colB = 'abc', I want to update the value
>>in colA.
>>In VB6, using ADO I can loop thru the recordset,set the values of colA
>>and call the Update method.
>>How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>>>
>>m_cmdSQL = New SqlClient.SqlCo mmand
>>With m_cmdSQL
>>.Connection = adoCon
>>.CommandTex t = sSQL
>>End With
>>m_daSQL = New SqlClient.SqlDa taAdapter
>>m_dsSQL = New DataSet
>>m_daSQL.Selec tCommand = "select * from tblA where [colB] = 'abc'"
>>m_daSQL.Fill( m_dsSQL)
>>lRow = 0
>>For Each aRow In m_dsSQL.Tables( 0).Rows()
>> m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
>>updates the value in memory, but not in the database
>> lRow = lRow + 1
>>Next
>>>
>>>

>>
>>

>
>

[fniles]

Re: Updating database in a loop

Thank you.
I have a few questions on your method:

Dim updateCommand As New SqlClient.SqlCl ientCommand("UP DATE tblA blah,
blah, blah")

Is the blah blah blah something like
"UPDATE tblA set ColA = " & lROW ?
If that is the case, at the time I assigned it (before the For loop), I do
not know the lRow yet.

>da.Update()

This requires a parameter, so should I set the parameter to ds (the DataSet)
?

Thanks.

"Scott M." <s-mar@nospam.nosp amwrote in message
news:u84QJrxHHH A.1064@TK2MSFTN GP04.phx.gbl...

Your DataAdapter goes and gets a copy of the data you want (based on the
Select statement you set up in the DataAdapter's select command). The
copy is placed in your dataset. You make changes to your dataset and then
you call the DataAdapter's update method. But you must set up the
UpdateCommand so that it does the update you want it to.
>
By the way, the m_ prefix you are using is not a recommened naming
convention and will cause more confusion than anything else. Also, don't
use the prefix of "ado" for your connection name since you aren't using
ado anyway, you're using ADO.NET.
>
Here's your code again (modified for better naming conventions and correct
coding):
>
Diim con As New SqlClient.SqlCl ientConnection( "connection string here")
Dim da As New SqlClient.SqlDa taAdapter("sele ct * from tblA where [colB] =
'abc'", con)
Dim updateCommand As New SqlClient.SqlCl ientCommand("UP DATE tblA blah,
blah, blah")
Dim ds As New DataSet
>
da.UpdateComman d = updateCommand
>
Try
daSQL.Fill(ds, "tblA")
Dim i As Integer
For i = 0 To ds.Tables(0).Ro ws().Count -1
'Forget about update statements here, you are working with
'a disconnected DataSet now, so just make whatever changes
'you need to the DataRows that this loop iterates over
If ds.Tables(0).Ro ws(i).Columns(" colB") = "abc" Then
ds.Tables(0).Ro ws(i).Columns(" colA") = i
End If
Next
>
da.Update()
>
Catch ex As Exception
'handle exceptions here
Finally
con.close() 'not needed if the connection was closed to begin with
End Try
>
>
>
>
>
>
>
>
>
"fniles" <fniles@pfmail. comwrote in message
news:uHVbakwHHH A.3312@TK2MSFTN GP03.phx.gbl...

>Thank you.
>I am sorry, I am still confused.
>In my example, I Fill the dataset using CommandText "select * from tblA
>where [colB] = 'abc'", before I update it.
>If I need to use InsertCommand, can I use the same DataAdapter/DataSet ?
>Thanks
>>
>m_cmdSQL = New SqlClient.SqlCo mmand
>With m_cmdSQL
>.Connection = adoCon
>.CommandText = sSQL
>End With
>m_daSQL = New SqlClient.SqlDa taAdapter
>m_dsSQL = New DataSet
>m_daSQL.Select Command = "select * from tblA where [colB] = 'abc'"
>m_daSQL.Fill(m _dsSQL)
>lRow = 0
>For Each aRow In m_dsSQL.Tables( 0).Rows()
>sSQL = "update tblA set"
>sSQL = sSQL & (" colA = " & lrow)
>sSQL = sSQL & (" where [colB] = 'abc')
>m_daSQL.Upda te ---how can I use the same m_daSQL that is already filled
>with the "select * from tblA where [colB] = 'abc'" ?
> lRow = lRow + 1
>Next
>>
>"Scott M." <s-mar@nospam.nosp amwrote in message
>news:%23pT%23o ZwHHHA.1248@TK2 MSFTNGP03.phx.g bl...

>>You'll need to create/configure the dataadapter's InsertCommand and its
>>CommandText property with your own update logic. Once you've done that
>>you simply add:
>>>
>>m_daSQL.Updat e
>>>
>>to your code.
>>>
>>"fniles" <fniles@pfmail. comwrote in message
>>news:%23ZJe9T wHHHA.2112@TK2M SFTNGP03.phx.gb l...
>>>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
>>>For every record in tblA where colB = 'abc', I want to update the value
>>>in colA.
>>>In VB6, using ADO I can loop thru the recordset,set the values of colA
>>>and call the Update method.
>>>How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>>>>
>>>m_cmdSQL = New SqlClient.SqlCo mmand
>>>With m_cmdSQL
>>>.Connectio n = adoCon
>>>.CommandTe xt = sSQL
>>>End With
>>>m_daSQL = New SqlClient.SqlDa taAdapter
>>>m_dsSQL = New DataSet
>>>m_daSQL.Sele ctCommand = "select * from tblA where [colB] = 'abc'"
>>>m_daSQL.Fill (m_dsSQL)
>>>lRow = 0
>>>For Each aRow In m_dsSQL.Tables( 0).Rows()
>>> m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
>>>updates the value in memory, but not in the database
>>> lRow = lRow + 1
>>>Next
>>>>
>>>>
>>>
>>>

>>
>>

>
>

[fniles]

Re: Updating database in a loop

Thank you.
In your more generic approach, is this the correct way to set the value for
tblAID ?
Cmd = New SqlClient.SqlCo mmand( "update tblA set colA=@colA where
tblAID=@tblAID" , Con)
Cmd.Parameters. Add("@colA", SqlDbType.Int, 5, "colA")
Cmd.Parameters. Add("@tblAID", SqlDbType.Int, 5, "tblAID").V ALUE = MyID
Adapter.UpdateC ommand = Cmd

m_UpdateCmdSQL. Parameters.Add( "@" & sParameterName, SqlDbType.VarCh ar,
sParameterSize) .Value = sParameter

"Branco Medeiros" <branco.medeiro s@gmail.comwrot e in message
news:1166066293 .448168.176570@ t46g2000cwa.goo glegroups.com.. .

fniles wrote:
<back posted/>
>
For the specific update you want to perform (setting each colA to a
distinct, zero-based index for all rows where colB = 'abc') , you don't
even need to fetch the records from the DB; the following would
suffice:
>
<aircode>
Dim SQL As String = "declare @val int; " _
& "set @val = -1; " _
& "update tblA Set @val = colA = @val + 1 where colB = 'abc'"
>
'Assuming a connection Con exists and is already open
Dim Cmd As New SqlClient.SqlCo mmand(SQL, Con)
Dim Count As Integer = Cmd.ExecuteNonQ uery()
</aircode>
>
Now, if you want to use a more generic approach, then you must set up
an update command, as pointed out by Scott.
>
The update command is a SQL string that would update a specific row
given a set of new values, passed as parameters. For instance, it could
be:
>
"Update tblA set colA = @colA where tblAID = @tblAID"
>
As you can see, you'd need a way to inequivocally specify the row you
need to update, probably using a primary key (named tblAID, here).
>
I'm not sure if you can use a different set of columns than the ones
used by the select query, I guess you'll have to do some testings.
>
Just to give you a headstart in the matter, the following would perform
the update using this approach:
>
<aircode>
'Assuming an open connection Con
>
Dim Adapter As New SqlClient.SqlDa taAdapter
>
'the SELECT cmd
Dim Cmd As New SqlClient.SqlCo mmand( _
"select tblAID, colA from tblA where colB = 'abc'", _
Con)
Adapter.SelectC ommand = Cmd
>
'the UPDATE cmd
Cmd = New SqlClient.SqlCo mmand( _
"update tblA set colA=@colA where tblAID=@tblAID" , _
Con)
Cmd.Parameters. Add("@colA", SqlDbType.Int, 5, "colA")
Cmd.Parameters. Add("@tblAID", SqlDbType.Int, 5, "tblAID")
Adapter.UpdateC ommand = Cmd
>
'retrieves the data
Dim Ds As New DataSet
Adapter.Fill(Ds )
>
'modify rows
Dim Index As Integer = 0
For Each Row As DataRow In Ds.Tables(0).Ro ws
Row("colA") = Index
Index += 1
Next
>
'update the source table
Adapter.Update( Ds)
</aircode>
>
HTH.
>
Regards,
>
Branco.
>
>

>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
>For every record in tblA where colB = 'abc', I want to update the value
>in
>colA.
>In VB6, using ADO I can loop thru the recordset,set the values of colA
>and
>call the Update method.
>How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>>
>m_cmdSQL = New SqlClient.SqlCo mmand
>With m_cmdSQL
> .Connection = adoCon
> .CommandText = sSQL
>End With
>m_daSQL = New SqlClient.SqlDa taAdapter
>m_dsSQL = New DataSet
>m_daSQL.Select Command = "select * from tblA where [colB] = 'abc'"
>m_daSQL.Fill(m _dsSQL)
>lRow = 0
>For Each aRow In m_dsSQL.Tables( 0).Rows()
> m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
>updates the value in memory, but not in the database
> lRow = lRow + 1
>Next

>

[Branco Medeiros]

Re: Updating database in a loop

fniles wrote:

Thank you.
In your more generic approach, is this the correct way to set the value for
tblAID ?
Cmd = New SqlClient.SqlCo mmand( "update tblA set colA=@colA where
tblAID=@tblAID" , Con)
Cmd.Parameters. Add("@colA", SqlDbType.Int, 5, "colA")
Cmd.Parameters. Add("@tblAID", SqlDbType.Int, 5, "tblAID").V ALUE = MyID
Adapter.UpdateC ommand = Cmd
>
m_UpdateCmdSQL. Parameters.Add( "@" & sParameterName, SqlDbType.VarCh ar,
sParameterSize) .Value = sParameter

Nope. You don't need to set the parameters values, the DataAdapter will
do that for you for each modified row when you call the Update()
method.

I'm not sure if I understand you correctly. It seems you want to update
also the field used as ID. If this is the case, then you must indicate
so in the update string. Notice, however, that then you must request
the filter (the "where" part) to use *the original ID value* when
looking for the row:

<aircode>
'Specify the original field value as key
Cmd = New SqlClient.SqlCo mmand( _
"update tblA set colA=@colA, tblAID=@tblAID " _
& "where tblAID=@Origina lID", _
Con)

Cmd.Parameters. Add("@colA", SqlDbType.Int, 5, "colA")
Cmd.Parameters. Add("@tblAID", SqlDbType.Int, 5, "tblAID")

'uses the original value for the field
Cmd.Parameters. Add( _
"@OriginalI D", SqlDbType.Int, 5, "tblAID" _
).SourceVersion = DataRowVersion. Original
</aircode>

HTH.

Regards,

Branco.

[fniles]

Re: Updating database in a loop

Thank you.
Actually, this is what I am trying to do:
Say I have a table Position like the following:
Account MasterAccount Cash Profit
123 999 1000 10
345 999 2000 20
999
I am looping thru this table looking for MasterAccount that is not blank (in
this case Account 123 and 345). For each of those records, I want to update
cash for MasterAccount with cash from this Account. In the example, I want
to update cash in Account 999 with cash from Account 123 and Account 345.
The result will be for Account 999, cash = 3000.
Because of this, I need to supply the Parameter value for MasterAccount and
Account inside the loop. Is this correct or I don't have to set this
Parameter values ?
Also, is it correct in that my case I want to call the Update method inside
the loop ?
Thank you.

sSQL = "update Position set cash = @Cash where masteraccount =
@MasterAccount and Account = @Account"
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = "select * from position where [masteraccount] <''"
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectCom mand = cmdSQL
daSQL.Fill(dsSQ L)
'Update Command
UpdateCmdSQL = New SqlClient.SqlCo mmand
UpdateCmdSQL.Co mmandText = sUpdateSQL
UpdateCmdSQL.Co nnection = adoCon
daSQL.UpdateCom mand = UpdateCmdSQL
UpdateCmdSQL.Pa rameters.Add("@ Cash", SqlDbType.Money , 8, "Cash")
UpdateCmdSQL.Pa rameters.Add("@ MasterAccount", SqlDbType.VarCh ar, 50,
"MasterAccount" )
UpdateCmdSQL.Pa rameters.Add("@ Account", SqlDbType.VarCh ar, 10, "Account")
lRow = 0

For Each aRow In rsMaster.Rows
MasterAccount = dsSQL.Tables(0) .Rows(lRow).Ite m("MasterAccoun t")
Account = dsSQL.Tables(0) .Rows(lRow).Ite m("account")
'Read Cash and profit from MasterAccount
cmdSQLRead = New SqlClient.SqlCo mmand
With cmdSQLRead
.Connection = adoCon
.CommandText = "select * from position where ACCOUNT = '" &
MasterAccount & "'"
End With
drSQL = cmdSQLRead.Exec uteReader()
Do While drSQL.Read
dsSQL.Tables(0) .Rows(lRow).Ite m("Cash") =
dsSQL.Tables(0) .Rows(lRow).Ite m("Cash") + drSQL.Item("cas h")
Loop
'----------------------------DO I need the next 2 LINE OF CODES HERE
? ------------------------------------
UpdateCmdSQL.Pa rameters("@Mast erAccount").Val ue = MasterAccount
UpdateCmdSQL.Pa rameters("@Acco unt").Value = Account
'-------------------------------------------------------------------------------------------------------------------
daSQL.Update(ds SQL) '-----IS THIS CORRECT TO CALL the UPDATE method
INSIDE the FOR LOOP ?
lRow = lRow + 1
Next



"Branco Medeiros" <branco.medeiro s@gmail.comwrot e in message
news:1166115202 .619696.297280@ f1g2000cwa.goog legroups.com...

fniles wrote:

>Thank you.
>In your more generic approach, is this the correct way to set the value
>for
>tblAID ?
>Cmd = New SqlClient.SqlCo mmand( "update tblA set colA=@colA where
>tblAID=@tblAID ", Con)
>Cmd.Parameters .Add("@colA", SqlDbType.Int, 5, "colA")
>Cmd.Parameters .Add("@tblAID", SqlDbType.Int, 5, "tblAID").V ALUE = MyID
>Adapter.Update Command = Cmd
>>
>m_UpdateCmdSQL .Parameters.Add ("@" & sParameterName, SqlDbType.VarCh ar,
>sParameterSize ).Value = sParameter

>
Nope. You don't need to set the parameters values, the DataAdapter will
do that for you for each modified row when you call the Update()
method.
>
I'm not sure if I understand you correctly. It seems you want to update
also the field used as ID. If this is the case, then you must indicate
so in the update string. Notice, however, that then you must request
the filter (the "where" part) to use *the original ID value* when
looking for the row:
>
<aircode>
'Specify the original field value as key
Cmd = New SqlClient.SqlCo mmand( _
"update tblA set colA=@colA, tblAID=@tblAID " _
& "where tblAID=@Origina lID", _
Con)
>
Cmd.Parameters. Add("@colA", SqlDbType.Int, 5, "colA")
Cmd.Parameters. Add("@tblAID", SqlDbType.Int, 5, "tblAID")
>
'uses the original value for the field
Cmd.Parameters. Add( _
"@OriginalI D", SqlDbType.Int, 5, "tblAID" _
).SourceVersion = DataRowVersion. Original
</aircode>
>
HTH.
>
Regards,
>
Branco.
>

[Branco Medeiros]

Re: Updating database in a loop

fniles wrote:
<snip>

Actually, this is what I am trying to do:
Say I have a table Position like the following:
Account MasterAccount Cash Profit
123 999 1000 10
345 999 2000 20
999
I am looping thru this table looking for MasterAccount that is not blank (in
this case Account 123 and 345). For each of those records, I want to update
cash for MasterAccount with cash from this Account. In the example, I want
to update cash in Account 999 with cash from Account 123 and Account 345.
The result will be for Account 999, cash = 3000.

<snip>

If this is the case, SQL is your friend (err... mostly): the following
commands will update the master accounts for you (no need looping):

<aircode>
Dim SQL As String = "update Position " _
& "set Cash = Cash + b.Total " _
& "from Position as a " _
& "join ( " _
& "select MasterAccount, Total = Sum(Cash) " _
& " from Position where MasterAccount is not null " _
& " group by MasterAccount) as b " _
& "on a.Account = b.MasterAccount "

'Assuming an open connection in Con
Dim Cmd As New SqlClient.SqlCo mmand(SQL, Con)
Dim Count As Integer = Cmd.ExecuteNonQ uery()
</aircode>

As with the majority of the code posted in foruns, don't run this on
your production data =)

HTH.

Regards,

Branco.

[Branco Medeiros]

Re: Updating database in a loop


Branco Medeiros wrote:

As with the majority of the code posted in foruns, don't run this on
your production data =)

I mean, not before you test it first, of course.

B.

[Scott M.]

Re: Updating database in a loop

No, the update statement wouldn't be referring to rows of the DataSet, it
would be referring to column names in the DataSet and the fields those
columns map to in your database.

The update method should be passed the dataset as its argument.


"fniles" <fniles@pfmail. comwrote in message
news:%233o$gC5H HHA.1280@TK2MSF TNGP04.phx.gbl. ..

Thank you.
I have a few questions on your method:

>Dim updateCommand As New SqlClient.SqlCl ientCommand("UP DATE tblA blah,
>blah, blah")

Is the blah blah blah something like
"UPDATE tblA set ColA = " & lROW ?
If that is the case, at the time I assigned it (before the For loop), I do
not know the lRow yet.
>

>>da.Update()

This requires a parameter, so should I set the parameter to ds (the
DataSet) ?
>
Thanks.
>
"Scott M." <s-mar@nospam.nosp amwrote in message
news:u84QJrxHHH A.1064@TK2MSFTN GP04.phx.gbl...

>Your DataAdapter goes and gets a copy of the data you want (based on the
>Select statement you set up in the DataAdapter's select command). The
>copy is placed in your dataset. You make changes to your dataset and
>then you call the DataAdapter's update method. But you must set up the
>UpdateComman d so that it does the update you want it to.
>>
>By the way, the m_ prefix you are using is not a recommened naming
>convention and will cause more confusion than anything else. Also, don't
>use the prefix of "ado" for your connection name since you aren't using
>ado anyway, you're using ADO.NET.
>>
>Here's your code again (modified for better naming conventions and
>correct coding):
>>
>Diim con As New SqlClient.SqlCl ientConnection( "connection string here")
>Dim da As New SqlClient.SqlDa taAdapter("sele ct * from tblA where [colB]
>= 'abc'", con)
>Dim updateCommand As New SqlClient.SqlCl ientCommand("UP DATE tblA blah,
>blah, blah")
>Dim ds As New DataSet
>>
>da.UpdateComma nd = updateCommand
>>
>Try
> daSQL.Fill(ds, "tblA")
> Dim i As Integer
> For i = 0 To ds.Tables(0).Ro ws().Count -1
> 'Forget about update statements here, you are working with
> 'a disconnected DataSet now, so just make whatever changes
> 'you need to the DataRows that this loop iterates over
> If ds.Tables(0).Ro ws(i).Columns(" colB") = "abc" Then
> ds.Tables(0).Ro ws(i).Columns(" colA") = i
> End If
> Next
>>
> da.Update()
>>
>Catch ex As Exception
> 'handle exceptions here
>Finally
> con.close() 'not needed if the connection was closed to begin with
>End Try
>>
>>
>>
>>
>>
>>
>>
>>
>>
>"fniles" <fniles@pfmail. comwrote in message
>news:uHVbakwHH HA.3312@TK2MSFT NGP03.phx.gbl.. .

>>Thank you.
>>I am sorry, I am still confused.
>>In my example, I Fill the dataset using CommandText "select * from tblA
>>where [colB] = 'abc'", before I update it.
>>If I need to use InsertCommand, can I use the same DataAdapter/DataSet ?
>>Thanks
>>>
>>m_cmdSQL = New SqlClient.SqlCo mmand
>>With m_cmdSQL
>>.Connection = adoCon
>>.CommandTex t = sSQL
>>End With
>>m_daSQL = New SqlClient.SqlDa taAdapter
>>m_dsSQL = New DataSet
>>m_daSQL.Selec tCommand = "select * from tblA where [colB] = 'abc'"
>>m_daSQL.Fill( m_dsSQL)
>>lRow = 0
>>For Each aRow In m_dsSQL.Tables( 0).Rows()
>>sSQL = "update tblA set"
>>sSQL = sSQL & (" colA = " & lrow)
>>sSQL = sSQL & (" where [colB] = 'abc')
>>m_daSQL.Updat e ---how can I use the same m_daSQL that is already
>>filled with the "select * from tblA where [colB] = 'abc'" ?
>> lRow = lRow + 1
>>Next
>>>
>>"Scott M." <s-mar@nospam.nosp amwrote in message
>>news:%23pT%23 oZwHHHA.1248@TK 2MSFTNGP03.phx. gbl...
>>>You'll need to create/configure the dataadapter's InsertCommand and its
>>>CommandTex t property with your own update logic. Once you've done that
>>>you simply add:
>>>>
>>>m_daSQL.Upda te
>>>>
>>>to your code.
>>>>
>>>"fniles" <fniles@pfmail. comwrote in message
>>>news:%23ZJe9 TwHHHA.2112@TK2 MSFTNGP03.phx.g bl...
>>>>>I am using VB.NET 2003, SQL 2000, and SqlDataAdapter.
>>>>For every record in tblA where colB = 'abc', I want to update the
>>>>value in colA.
>>>>In VB6, using ADO I can loop thru the recordset,set the values of colA
>>>>and call the Update method.
>>>>How can I do this in VB.NET and SqlDataAdapter ? Thank you.
>>>>>
>>>>m_cmdSQL = New SqlClient.SqlCo mmand
>>>>With m_cmdSQL
>>>>.Connecti on = adoCon
>>>>.CommandTex t = sSQL
>>>>End With
>>>>m_daSQL = New SqlClient.SqlDa taAdapter
>>>>m_dsSQL = New DataSet
>>>>m_daSQL.Sel ectCommand = "select * from tblA where [colB] = 'abc'"
>>>>m_daSQL.Fil l(m_dsSQL)
>>>>lRow = 0
>>>>For Each aRow In m_dsSQL.Tables( 0).Rows()
>>>> m_dsSQL.Tables( 0).Rows(lRow).I tem("colA") = lrow --this
>>>>updates the value in memory, but not in the database
>>>> lRow = lRow + 1
>>>>Next
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>