SQL UPDATE From Other Database Sources In VB.NET-CodePudding

I want to update by taking the value from another table (IFGTS) in the column (PRSOBNET) and then adding that value with the one in the textbox in the sql2 button in the table (GSDTS) in the column (CIU) is it possible to do like screenshot result which I marked yellow in the "ciu" column and what if I want to unite the sql1 button and the sql2 button into one button command. for sql2 command has not worked or has not succeeded. Please best solution.

Thanks jack

  Private Sub SQL1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SQL1.Click
        Try
            connectionString = cn
            con = New OleDbConnection(connectionString)
            con.Open()
            Dim sql As String = "UPDATE GSDTS SET CIUB = CIU,CIU = NULL,DPRB = DPR,DPR = NULL WHERE QTY > 0"
            cmd = New OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
            con.Close()
            Me.fillDataGridView1()
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub
    Private Sub SQL2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SQL2.Click
        Try
            connectionString = cn
            con = New OleDbConnection(connectionString)
            con.Open()
            Dim sql As String = "INSERT INTO GSDTS (CIU) SELECT t1.PRSOBNET FROM IFGTS t1 WHERE NOT EXISTS(SELECT ITM FROM GSDTS t2 WHERE t2.ITM = t1.ITM) AND GDN = 'A.04.01.002.001'"
            cmd = New OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
            con.Close()
            Me.fillDataGridView1()
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub

CodePudding user response：

You could extract the database code from the button handlers, and then if you want to do both actions, call both extracted methods, something like this:

Sub UpdateGsdts()
    Try
        Dim sql As String = "UPDATE GSDTS SET CIUB = CIU, CIU = NULL, DPRB = DPR, DPR = NULL WHERE QTY > 0"
        Using conn As New OleDbConnection(cn),
               cmd As New OleDbCommand(sql, conn)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using

    Catch myerror As OleDbException
        MessageBox.Show("Error: " & myerror.Message)
    End Try

End Sub

Sub InsertIntoGsdts()
    Try
        Dim sql As String = "INSERT INTO GSDTS (CIU) SELECT t1.PRSOBNET FROM IFGTS t1 WHERE NOT EXISTS(SELECT ITM FROM GSDTS t2 WHERE t2.ITM = t1.ITM) AND GDN = 'A.04.01.002.001'"
        Using conn As New OleDbConnection(cn),
               cmd As New OleDbCommand(sql, conn)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using

    Catch myerror As OleDbException
        MessageBox.Show("Error: " & myerror.Message)
    End Try

End Sub

Sub InsertIntoAndUpdateGsdts()
    InsertIntoGsdts()
    UpdateGsdts()
    fillDataGridView1()

End Sub

Private Sub SQL1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SQL1.Click
    UpdateGsdts()
    fillDataGridView1()

End Sub

Private Sub SQL2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SQL2.Click
    InsertIntoGsdts()
    fillDataGridView1()

End Sub

The Using statement makes sure that the unmanaged resources used by a database connection and command are disposed of after they've been used.