How to Update Every Record in a Table

Discussion in 'ASP .Net' started by Mel, Aug 18, 2008.

  1. Mel

    Mel Guest

    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
    Mel, Aug 18, 2008
    #1
    1. Advertising

  2. Mel

    sloan Guest

    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



    "Mel" <> wrote in message
    news:...
    > 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
    sloan, Aug 18, 2008
    #2
    1. Advertising

  3. Mel

    Mel Guest

    On Aug 18, 10:46 am, "sloan" <> wrote:
    > 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
    >
    > "Mel" <> wrote in message
    >
    > news:...
    >
    > > 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


    Ahh, it's all coming back to me now. Thanks for the guidance to shake
    my cobwebs loose; it worked great.
    Mel, Aug 18, 2008
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. André
    Replies:
    0
    Views:
    3,730
    André
    Jun 25, 2006
  2. =?Utf-8?B?SXJ3YW5zeWFo?=
    Replies:
    4
    Views:
    2,437
    =?Utf-8?B?SXJ3YW5zeWFo?=
    Oct 30, 2007
  3. André
    Replies:
    0
    Views:
    433
    André
    Jun 25, 2006
  4. Jimmy Tran
    Replies:
    1
    Views:
    226
    Dan Brussee
    Oct 3, 2003
  5. Jen

    New record every week?

    Jen, Jul 28, 2006, in forum: ASP General
    Replies:
    16
    Views:
    183
    Mike Brind
    Jul 31, 2006
Loading...

Share This Page