Updating database in a loop

[fniles]

Re: Updating database in a loop

Thank you again.
For future reference, if I want to do a similar thing from a program (not
SQL),
do I need the following 2 statements inside the FOR loop ?
UpdateCmdSQL.Pa rameters("@Mast erAccount").Val ue = MasterAccount
UpdateCmdSQL.Pa rameters("@Acco unt").Value = Account

Also, I should call the Update method from outside the loop, shouldn't I ?

"Branco Medeiros" <branco.medeiro s@gmail.comwrot e in message
news:1166128340 .322665.24630@7 3g2000cwn.googl egroups.com...

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

[Scott M.]

Re: Updating database in a loop

I think you have a fundamental misunderstandin g of what's going on here.

You would only use a loop if you wanted to iterate over the rows in a
DataSet. But, as you are iterating over the rows of a DataSet, you aren't
concerned with update statements or command objects or parameters, you
simply make manual changes to the columns of the row in the dataset that
need new values.

The DataAdapter does the "actual" database updating for you, but it must
know how you want the update done, so you build an UpdateCommand and
configure the command (with or without parameters as needed), so that when
you call the Update() method of the DataAdapter, it knows how to take the
manual changes you made to your DataSet and update the actual data in the
database.




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

Thank you again.
For future reference, if I want to do a similar thing from a program (not
SQL),
do I need the following 2 statements inside the FOR loop ?
UpdateCmdSQL.Pa rameters("@Mast erAccount").Val ue = MasterAccount
UpdateCmdSQL.Pa rameters("@Acco unt").Value = Account
>
Also, I should call the Update method from outside the loop, shouldn't I ?
>
"Branco Medeiros" <branco.medeiro s@gmail.comwrot e in message
news:1166128340 .322665.24630@7 3g2000cwn.googl egroups.com...

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

>
>