SQL (INSERT INTO) COMMAND DOES NOT WORK IN OLEDB IN VB.NET

Question

I want to update in the "CIU" column in the database "GSDTS" and retrieve the data or values from the database "IFGTS" in the column "PRSOBNET" based on the conditions of the "ITM" column and the "GDN" column or warehouse. Do I have to do sql update or insert to please recommend the solution because I sql command in the code does not work at all and how can the sql command solution work? thanks jack

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 Exception
            MessageBox.Show("Error: " & myerror.Message)
        End Try
    End Sub
    Private Sub SQL2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SQL2.Click
        InsertIntoGsdts()
        fillDataGridView1()
    End Sub

Are you able to simplify your problem, adding sample data and expected output? The "not work at all" is often easier to understand with the error message you are receiving. — James, May 12 '22 at 06:45
@James , I have updated the screenshot sample data with the desired output and for the error message does not exist and only the sql process does not work — roy, May 12 '22 at 07:05
"INSERT" adds a *new* record, use "UPDATE" to alter an *existing* record. Right now it looks like you are trying to insert a new record into GSDTS with only column CIU filled, but only when there is *no* record ("NOT EXISTS") with that specific ITM - and there *is* already one — Hans Keﬆing, May 12 '22 at 08:52
@HansKeﬆing , Yes, it's true that you said so the sql command solution what to do and how the sql command so that it can run perfectly — roy, May 12 '22 at 08:59
@HansKeﬆing , before I used ("NOT EXISTS") to avoid duplicate records from one database to another but now it's just to fill in one column "CIU" whether I should use such a way or there are other recommendations — roy, May 12 '22 at 09:20
On an eyeballing, to me looks like you've got a potential SQL syntax error in the insert sql. Maybe change Catch myerror As OleDbException to Catch myerror As Exception, perhaps there is some other error happening you not seeing — Hursey, May 12 '22 at 20:48
@Hursey , I have changed the code according to your recommendation but still do not cause errors — roy, May 13 '22 at 09:19

score 0 · Accepted Answer · answered May 14 '22 at 07:17

I found a solution according to the link below Here's a link!

Sub InsertIntoGsdts()
        Try
            Dim sql As String = "update GSDTS as t1 inner join IFGTS as t2 on t1.[ITM] = t2.[ITM] set t1.[CIU] = t2.[PRSOBNET] WHERE 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 Exception
            MessageBox.Show("Error: " & myerror.Message)
        End Try
    End Sub
    Private Sub SQL2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SQL2.Click
        InsertIntoGsdts()
        fillDataGridView1()
    End Sub

SQL (INSERT INTO) COMMAND DOES NOT WORK IN OLEDB IN VB.NET

1 Answers1