Howdy, Stranger!

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


Update database table from SQL Union

davew01davew01 Member Posts: 1
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()

If ds.HasChanges Then da.Update(ds.Tables("Employees"))
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.




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

    [code]select * from table1
    select * from table2

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

    select * from table3
    [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]
Sign In or Register to comment.