none
update the databases after use the union RRS feed

  • Question

  • i want to update row in database after use union

    union conection

     Dim cmd As OleDbCommand = New OleDbCommand("SELECT ID_TB1,TYPE_TB1 ,COUN_TB1,LOSS_TB1,RETURN_TB1 FROM tb1 UNION SELECT ID_TB2,TYPE_TB2 ,COUN_TB2,LOSS_TB2,RETURN_TB2 FROM tb2 UNION SELECT ID_TB3,TYPE_TB3 ,COUN_TB3,LOSS_TB3,RETURN_TB3 FROM tb3", con)
            con.Open()
            DataAdapter1 = New OleDbDataAdapter(cmd)
            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter1)
            DataSet1 = New DataSet()
            DataAdapter1.Fill(DataSet1, "tb1,tb2,tb3")
            DataGridView1.DataSource = DataSet1
            DataGridView1.DataMember = "tb1,tb2,tb3"
            con.Close()

    update not work

     con.Open()
               DataSet1.Clear()
            BindingSource1.EndEdit()
            DataAdapter1.Fill(DataSet1, "TB1,TB2,TB3")
            DataGridView1.Refresh()
            conn()
            count()
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            MsgBox("update ok")

    another way 2 not work

       Sub UPDAT()
            On Error Resume Next
            con.Open()
            Dim Sav As New OleDb.OleDbCommand
            Sav.Connection = con
            Sav.CommandType = CommandType.Text
            Sav.CommandText = "UPDATE TB1 SET  TYPE_TB1 = '" & _
            stock_add.TextBox1.Text & "' ,COUN_TB1 = '" & _
            stock_add.TextBox2.Text & "' where ID_TB1 LIKE '" & _
            stock_add.Label6.Text & "'"
            Sav.ExecuteNonQuery()
    
            '================
            Dim Sav1 As New OleDb.OleDbCommand
            Sav1.Connection = con
            Sav1.CommandType = CommandType.Text
            Sav1.CommandText = "UPDATE TB2 SET  TYPE_TB2 = '" & _
            stock_add.TextBox1.Text & "' ,COUN_TB2 = '" & _
            stock_add.TextBox2.Text & "' where ID_TB2 LIKE '" & _
            stock_add.Label6.Text & "'"
            Sav1.ExecuteNonQuery()
            '================
            Dim Sav2 As New OleDb.OleDbCommand
            Sav2.Connection = con
            Sav2.CommandType = CommandType.Text
            Sav2.CommandText = "UPDATE TB3 SET  TYPE_TB3 = '" & _
            stock_add.TextBox1.Text & "' ,COUN_TB3 = '" & _
            stock_add.TextBox2.Text & "' where ID_TB3 LIKE '" & _
            stock_add.Label6.Text & "'"
            Sav2.ExecuteNonQuery()
            con.Open()
              DataSet1.Clear()
            BindingSource1.EndEdit()
            DataAdapter1.Fill(DataSet1, "TB1,TB2,TB3")
            DataGridView1.Refresh()
            conn()
            count()
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            MsgBox("update ok")
        End Sub

    what is the wrong

    Saturday, February 3, 2018 6:47 PM

All replies

  • First off get rid of the "In Error Resume Next"

    Next, assign ExecuteNonQuery to a variable, what does it return?

    Do all the tables have primary keys?

    When all else fails try running the update command in the database, of course it will not work as is so there is some work to be done so you can test it out, if it does not work then modify the query until it does work.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 3, 2018 8:13 PM
    Moderator
  • First off get rid of the "In Error Resume Next"

    Next, assign ExecuteNonQuery to a variable, what does it return?

    Do all the tables have primary keys?

    When all else fails try running the update command in the database, of course it will not work as is so there is some work to be done so you can test it out, if it does not work then modify the query until it does work.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    First off get rid of the "In Error Resume Next"

    - You have changed to try catch

    Do all the tables have primary keys?

    -yes

    Saturday, February 3, 2018 8:36 PM
  • So what does ExecuteNonQuery return? And are the primary keys auto incrementing ?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 3, 2018 8:44 PM
    Moderator
  • So what does ExecuteNonQuery return? And are the primary keys auto incrementing ?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    are the primary keys auto incrementing ?yes

    So what does ExecuteNonQuery return

    Back to the table to update the evidence but do not know here how to use it to modify the three tables
      So I asked the question

    Saturday, February 3, 2018 8:50 PM
  • So what does ExecuteNonQuery return? And are the primary keys auto incrementing ?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    are the primary keys auto incrementing ?yes

    So what does ExecuteNonQuery return

    Back to the table to update the evidence but do not know here how to use it to modify the three tables
      So I asked the question

    If you search the web you would learn what ExecuteNonQuery returns.

    Back to the question, why doesn't it work? well I would have to replicate your database and code to figure that out which I or others here are not going to do as we are simply people trying to help, zero pay, do not work for Microsoft.

    If I had this issue I would (and I suggested this already) reproduce the query in the database and see if it works, go from there.

    You are not making an effort at this point so there is nothing else I can do for you.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 3, 2018 11:04 PM
    Moderator
  • You cannot update a database using a union. 

    You can only update one database table at a time

    Your fill seems to be crazy, there is no table in your code with the name "TB1,TB2,TB3"

    First do things which work and not 10 things in a row which you have declared as self invented code yourself. 


    Success Cor

    Sunday, February 4, 2018 8:03 AM