Update database table from SQL Union - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Update database table from SQL Union

davew01davew01 Posts: 1Member
I have trying without success to update a database table from my VB code which is creating a UNION statement. Could someone help me?

MY code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'--------------- FILL FROM Employess & New Employees Tables
'**------- FILL BY UNION
Dim conn As New SqlConnection("Server=(Local);" & _
"DataBase=Northwind; Integrated Security=SSPI")
Dim sqlstr As String = "SELECT * FROM Employees UNION Select * _ from NewEmployees"
da = New SqlDataAdapter(sqlstr, conn)
da.AcceptChangesDuringFill = False
da.Fill(ds, "Employees") ' The Fill method maps the result set _ a DATATABLE object in the given DATASET

' Display the results
DataGridView1.DataSource = ds.Tables("Employees") ' Fill the _ datagridview with with the Table

' Code to Update by to the original database.

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim cb As New SqlCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand()
da.DeleteCommand = cb.GetDeleteCommand()
da.UpdateCommand = cb.GetUpdateCommand()

Try
If ds.HasChanges Then da.Update(ds.Tables("Employees"))
ds.AcceptChanges()
MessageBox.Show("Database Updated")
Catch ex As Exception
MessageBox.Show("Database Error or No Updates To Processed")
End Try

I would be happy to either save the results of the UNION datatable back to either of the original tables (Employees or New Employees) or back to a new table called "AllEmployees"

My goal is just to merge the the two SQL database tables.

Thanks

Dave


Comments

  • HackmanCHackmanC Posts: 441Member
    Use only 1 SQL statement...

    [code]select * from table1
    select * from table2
    go

    insert into table3 select * from table1 union select * from table2
    go

    select * from table3
    go[/code]
    [code]id name
    ----------- --------------------------------------------------
    1 NAME 1
    2 NAME 2

    (2 row(s) affected)

    id name
    ----------- --------------------------------------------------
    3 NAME 3
    4 NAME 4

    (2 row(s) affected)


    (4 row(s) affected)

    id name
    ----------- --------------------------------------------------
    1 NAME 1
    2 NAME 2
    3 NAME 3
    4 NAME 4

    (4 row(s) affected)[/code]

    After that you can "Select" from table3 and fill the datagrid,
    make changes, and update again with "SqlDataAdapter.Update()".

    [red]Good luck![/red]
    [blue]Hackman[/blue]
Sign In or Register to comment.