How to Update Every Record in a Table

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

Can anyone assist me in how I would structure this code to update
every record in a table? I need to loop through each record and
increase the cost field by a certain percentage. How do I loop
through *and* update each record? Here is my code so far:

'Start of Example Code
Dim strConStand As String = "Provider=Microsoft.JET.OLEDB.4.0;Data
Source =c:\w\standards.mdb"
Dim strFreight As String = "Select [Cost] FROM [Freight];"
Dim recFreight As System.Data.OleDb.OleDbDataReader
Dim conFreight As New
System.Data.OleDb.OleDbConnection(strConStand)
Dim comFreight As New System.Data.OleDb.OleDbCommand(strFreight,
conFreight)

conFreight.Open()
recFreight = comFreight.ExecuteReader

Do While recFreight.Read
'increase every cost by 5%
Loop
recFreight.Close()
conFreight.Close()
'End of Example Code
 
That's the last thing you want to do is LOOP and UPDATE.

Here is a one hit (bulk update) statement.

Update [Freight] Set [Cost] = ([Cost] * 1.05)

Use .ExecuteNonQuery
 
That's the last thing you want to do is LOOP and UPDATE.

Here is a one hit (bulk update) statement.

Update [Freight] Set [Cost] = ([Cost] * 1.05)

Use .ExecuteNonQuery


Can anyone assist me in how I would structure this code to update
every record in a table? I need to loop through each record and
increase the cost field by a certain percentage. How do I loop
through *and* update each record? Here is my code so far:
Click to expand...
'Start of Example Code
Dim strConStand As String = "Provider=Microsoft.JET.OLEDB.4.0;Data
Source =c:\w\standards.mdb"
Dim strFreight As String = "Select [Cost] FROM [Freight];"
Dim recFreight As System.Data.OleDb.OleDbDataReader
Dim conFreight As New
System.Data.OleDb.OleDbConnection(strConStand)
Dim comFreight As New System.Data.OleDb.OleDbCommand(strFreight,
conFreight)
Click to expand...
conFreight.Open()
recFreight = comFreight.ExecuteReader
Click to expand...
Do While recFreight.Read
'increase every cost by 5%
Loop
recFreight.Close()
conFreight.Close()
'End of Example Code
Click to expand...
Click to expand...

Ahh, it's all coming back to me now. Thanks for the guidance to shake
my cobwebs loose; it worked great.
 
Back
Top