Moving records from one table to another

[bal1]

Hi, Guys i am trying to move a record from one table to another and i have got the record to move, but i am unable to delete the orginal record in the first table. could someone give me a hand with that part.

Here is my code so far:

Dim con As New OleDb.OleDbConnection
        Dim cmd As New OleDb.OleDbCommand
        Dim da As New OleDb.OleDbDataAdapter
        Dim cb As OleDb.OleDbCommandBuilder
        Dim dr As DataRow
        Dim dt As New DataTable

        ' a Connection object to locate  database
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= database source"

        ' a Command object to get the data
        cmd.Connection = con
        cmd.CommandText = "Select * from SoldUsedCars"

        ' a DataAdpter object to fill the data
        da.SelectCommand = cmd
        da.Fill(dt)

        'add a record
        dr = dt.NewRow()
        dr("SaleID") = 1
        dr("CarID") = TextBox1.Text
        dr("CarMake") = TextBox2.Text
        dr("CarModel") = TextBox3.Text
        dr("TypeOfCar") = TextBox6.Text
        dr("NumberOfDoors") = TextBox5.Text
        dr("Colour") = TextBox4.Text
        dr("Registration") = TextBox14.Text
        dr("RegisteredYear") = TextBox13.Text
        dr("Mileage") = TextBox12.Text
        dr("SatNav") = TextBox11.Text
        dr("InteriorTrim") = TextBox10.Text
        dr("Alloys") = TextBox9.Text
        dr("ACorCC") = TextBox8.Text
        dr("CDPlayer") = TextBox7.Text
        dr("Price") = TextBox16.Text
        dr("Salesman") = TextBox15.Text
        dt.Rows.Add(dr)

        cb = New OleDb.OleDbCommandBuilder(da)
        Try
            
            da.Update(dt)
                        MsgBox("Record Successfully Added", MsgBoxStyle.Information, "Information")
        Catch ex As Exception
            MsgBox("Cant Update Database" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error!")
            Exit Sub
        End Try
    End Sub

Now this code successfully moves the record, but i am just in need of the code to delete the orginial

 

[jshultz]

Should just need something like "Delete from SoldUsedCars where SaleID ='1'"

Of course, that is assuming SaleID is your primarykey (unique), and you obviously shouldn't hard code it ;-)

 

[bal1]

yeah i tried something similar to that, but it wouldnt work, it said it deleted it but the record was still there.
could someone show me how to insert it into my code?

 

[cjard]

delete it from the original data table, then Update() the original table..

I have to say, i dont really want to get into an in depth explanation because the code youre using isnt very good. For a good tutorial on how to work with database data in .net 1.1 read the DW1 link in my signature

 

[bal1]

its ok i have got it sorted now, but thanks for the help