code required to update a table form an existing table

[vivekanandaan]

hi,

I am having two tables with a similar structure. I want a code to select data from one table and update the same in second table and then delete the records in the first table.

Kindly help me out.

Thanks & Regards

Vivek

[Reverend Jim]

Let's take two tables with the same structure. Two fields, last_name and first_name where both are varchar(50). To select some data from table 1 and insert it into table 2 you can do

insert into Table2 select * from Table1 where last_name = 'Jones'

If the tables have similar fields but different field names then you can name the fields as in

insert into Table2 (last_name,first_name) select lname,fname from Table1 where lname = 'Jones'

If you need to move fields from one table to another then follow the insert query with a query to delete from the first table as in

insert into Table2 select * from Table1 where last_name = 'Jones'
delete from Table1 where last_name = 'Jones'

But (big but), enclose the two statements as a transaction in a Try/Catch. That way if the delete fails then the insert will be rolled back.

[vivekanandaan]

Sorry to mention i need the VB.net code for this situation ...

[Reverend Jim]

I'm having a problem with this at the moment and I don't understand why.
In vbScript I would write

set con = CreateObject("ADODB.Connection")
con.Open "Driver={SQL Server};Server=.\sqlexpress;Database=mydb;Trusted_Connection=yes;"
con.Errors.Clear

on error resume next

con.BeginTrans
con.Execute "insert into Table2 select * from Table1 where  last_name = 'Andrews'"
con.execute "delete from Table1 where last_name = 'Andrews'"

if err.Number = 0 then
	con.CommitTrans
	wscript.echo "commit",err.Description
else
	con.RollBackTrans
	wscript.echo "rollback",err.Description
end if

con.Close

However, when I try to wrap the queries in BeginTrans, etc under vb.Net I get {"Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets." I'll have to look into this and get back to you.

[Reverend Jim]

Got it. I neglected to set the cursor type to Client. Here is th vb.Net code sample

Imports ADODB

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim conn As New Connection
        conn.CursorLocation = CursorLocationEnum.adUseClient


        conn.Open("Driver={SQL Server};Server=.\sqlexpress;Database=mydb;Trusted_Connection=yes;")

        If conn.State = ADODB.ObjectStateEnum.adStateOpen Then

            conn.BeginTrans()

            Try
                conn.Execute("insert into Table2 select * from Table1 where ast_name = 'Andrews'")
                conn.Execute("delete from Table1 where last_name = 'Andrews'")
                conn.CommitTrans()
                MsgBox("ok")
            Catch ex As Exception
                conn.RollbackTrans()
                MsgBox("failed")
            End Try

        End If

    End Sub

End Class

I've never had the need to move past the ADO access model. There are other people here who are familiar with OLEDB. Perhaps someone else could jump in if you need further help.

[vivekanandaan]

Can somebody give me VB.net with ms-access code ...

[Reverend Jim]

The code should be the same. Only the connection string should vary.