Updating database in a loop

F

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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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).Item("colA") = lrow --> this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
 
S

Scott M.

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

fniles

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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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. said:
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 said:
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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).Item("colA") = lrow --> this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
Click to expand...
Click to expand...
 
G

Guest

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

Why don't you write a SQL statement:

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

Scott M.

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.SqlClientConnection("connection string here")
Dim da As New SqlClient.SqlDataAdapter("select * from tblA where [colB] =
'abc'", con)
Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah, blah,
blah")
Dim ds As New DataSet

da.UpdateCommand = updateCommand

Try
daSQL.Fill(ds, "tblA")
Dim i As Integer
For i = 0 To ds.Tables(0).Rows().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).Rows(i).Columns("colB") = "abc" Then
ds.Tables(0).Rows(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 said:
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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. said:
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 said:
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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).Item("colA") = lrow --> this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
Click to expand...
Click to expand...
Click to expand...
 
A

aaron.kempf

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

****ing retard

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

-Aaron
 
B

Branco Medeiros

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.SqlCommand(SQL, Con)
Dim Count As Integer = Cmd.ExecuteNonQuery()
</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.SqlDataAdapter

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

'the UPDATE cmd
Cmd = New SqlClient.SqlCommand( _
"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.UpdateCommand = 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).Rows
Row("colA") = Index
Index += 1
Next

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

HTH.

Regards,

Branco.
 
C

Cor Ligthert [MVP]

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.Commandbuilder(m_daSQL)
(_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 said:
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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. said:
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 said:
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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).Item("colA") = lrow --> this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
Click to expand...
Click to expand...
Click to expand...
 
F

fniles

Thank you.
I have a few questions on your method:
Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah,
blah, blah")
Click to expand...
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()
Click to expand...
This requires a parameter, so should I set the parameter to ds (the DataSet)
?

Thanks.

Scott M. said:
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.SqlClientConnection("connection string here")
Dim da As New SqlClient.SqlDataAdapter("select * from tblA where [colB] =
'abc'", con)
Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah,
blah, blah")
Dim ds As New DataSet

da.UpdateCommand = updateCommand

Try
daSQL.Fill(ds, "tblA")
Dim i As Integer
For i = 0 To ds.Tables(0).Rows().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).Rows(i).Columns("colB") = "abc" Then
ds.Tables(0).Rows(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 said:
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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. said:
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.

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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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).Item("colA") = lrow --> this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
Click to expand...
Click to expand...
Click to expand...
 
F

fniles

Thank you.
In your more generic approach, is this the correct way to set the value for
tblAID ?
Cmd = New SqlClient.SqlCommand( "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").VALUE = MyID
Adapter.UpdateCommand = Cmd

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

Branco Medeiros said:
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.SqlCommand(SQL, Con)
Dim Count As Integer = Cmd.ExecuteNonQuery()
</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.SqlDataAdapter

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

'the UPDATE cmd
Cmd = New SqlClient.SqlCommand( _
"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.UpdateCommand = 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).Rows
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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).Item("colA") = lrow --> this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
Click to expand...
Click to expand...
 
B

Branco Medeiros

fniles said:
Thank you.
In your more generic approach, is this the correct way to set the value for
tblAID ?
Cmd = New SqlClient.SqlCommand( "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").VALUE = MyID
Adapter.UpdateCommand = Cmd

m_UpdateCmdSQL.Parameters.Add("@" & sParameterName, SqlDbType.VarChar,
sParameterSize).Value = sParameter
Click to expand...

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.SqlCommand( _
"update tblA set colA=@colA, tblAID=@tblAID " _
& "where tblAID=@OriginalID", _
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( _
"@OriginalID", SqlDbType.Int, 5, "tblAID" _
).SourceVersion = DataRowVersion.Original
</aircode>

HTH.

Regards,

Branco.
 
F

fniles

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.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = "select * from position where [masteraccount] <> ''"
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = cmdSQL
daSQL.Fill(dsSQL)
'Update Command
UpdateCmdSQL = New SqlClient.SqlCommand
UpdateCmdSQL.CommandText = sUpdateSQL
UpdateCmdSQL.Connection = adoCon
daSQL.UpdateCommand = UpdateCmdSQL
UpdateCmdSQL.Parameters.Add("@Cash", SqlDbType.Money, 8, "Cash")
UpdateCmdSQL.Parameters.Add("@MasterAccount", SqlDbType.VarChar, 50,
"MasterAccount")
UpdateCmdSQL.Parameters.Add("@Account", SqlDbType.VarChar, 10, "Account")
lRow = 0

For Each aRow In rsMaster.Rows
MasterAccount = dsSQL.Tables(0).Rows(lRow).Item("MasterAccount")
Account = dsSQL.Tables(0).Rows(lRow).Item("account")
'Read Cash and profit from MasterAccount
cmdSQLRead = New SqlClient.SqlCommand
With cmdSQLRead
.Connection = adoCon
.CommandText = "select * from position where ACCOUNT = '" &
MasterAccount & "'"
End With
drSQL = cmdSQLRead.ExecuteReader()
Do While drSQL.Read
dsSQL.Tables(0).Rows(lRow).Item("Cash") =
dsSQL.Tables(0).Rows(lRow).Item("Cash") + drSQL.Item("cash")
Loop
'----------------------------DO I need the next 2 LINE OF CODES HERE
? ------------------------------------
UpdateCmdSQL.Parameters("@MasterAccount").Value = MasterAccount
UpdateCmdSQL.Parameters("@Account").Value = Account
'-------------------------------------------------------------------------------------------------------------------
daSQL.Update(dsSQL) '-----> IS THIS CORRECT TO CALL the UPDATE method
INSIDE the FOR LOOP ?
lRow = lRow + 1
Next
 
B

Branco Medeiros

fniles wrote:
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.
Click to expand...
<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.SqlCommand(SQL, Con)
Dim Count As Integer = Cmd.ExecuteNonQuery()
</aircode>

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

HTH.

Regards,

Branco.
 
B

Branco Medeiros

Branco said:
As with the majority of the code posted in foruns, don't run this on
your production data =)
Click to expand...

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

B.
 
S

Scott M.

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 said:
Thank you.
I have a few questions on your method:
Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah,
blah, blah")
Click to expand...
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()
Click to expand...
This requires a parameter, so should I set the parameter to ds (the
DataSet) ?

Thanks.

Scott M. said:
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.SqlClientConnection("connection string here")
Dim da As New SqlClient.SqlDataAdapter("select * from tblA where [colB]
= 'abc'", con)
Dim updateCommand As New SqlClient.SqlClientCommand("UPDATE tblA blah,
blah, blah")
Dim ds As New DataSet

da.UpdateCommand = updateCommand

Try
daSQL.Fill(ds, "tblA")
Dim i As Integer
For i = 0 To ds.Tables(0).Rows().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).Rows(i).Columns("colB") = "abc" Then
ds.Tables(0).Rows(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 said:
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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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

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.

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.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = "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).Item("colA") = lrow --> this
updates the value in memory, but not in the database
lRow = lRow + 1
Next
Click to expand...
Click to expand...
Click to expand...
 
F

fniles

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.Parameters("@MasterAccount").Value = MasterAccount
UpdateCmdSQL.Parameters("@Account").Value = Account

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

Scott M.

I think you have a fundamental misunderstanding 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

RowSelect 1
View timed out every now and then 5
Stored Procedure and SQLCommand 9
SQL Server connection pooling 9
There is already an open DataReader associated with this Connection which must be closed first 20
vb.net last record in loop 2
Question on SQLCommand in a loop 2
Page_Load question in ASPX 2

Top