CODEWITHSUNDEEP
databasevb.netms-access
Bruce
Bruce

Reputation: 577

Can't not use "INSERT INTO table VALUES() ON DUPLICATE KEY UPDATE" in VB.NET

I just want to INSERT all of record to database if have duplicated primary, it will update primary. But I don't know why it alway say error "Missing semicolon (;) at end of SQL statement.". I know that in VB.NET there is no semicolor for sql statement, I think there has something wrong in my sql statement. when I remove part "ON DUPLICATE KEY UPDATE StudentID=@StudentID" It insert record OK. Please point to me whether I missed something

Here is my code

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\1_Project\Project_of_VB_Net\AccessDatabase\StudentDatabase.accdb"
    cmd.Connection = cnn
    For Each dr In dt.Rows
        cmd.CommandText = "INSERT INTO StudentData(ID,StudentName,StudentID,StudentClass) VALUES(@ID,@StudentName,@StudentID,@StudentClass) ON DUPLICATE KEY UPDATE StudentID=@StudentID"
        cmd.Parameters.AddWithValue("@ID", dr("ID"))
        cmd.Parameters.AddWithValue("@StudentName", dr("StudentName"))
        cmd.Parameters.AddWithValue("@StudentID", dr("StudentID"))
        cmd.Parameters.AddWithValue("@StudentClass", dr("StudentClass"))
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
    Next
End Sub

Upvotes: 0

Views: 914

Answers (1)

Steve
Steve

Reputation: 216273

You can't use that syntax with Access. It simply doesn't understand it.
If you need to check if the record exists then you need something like this

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
   Dim update = "UPDATE StudentData SET StudentName = @StudentName,
                    StudentID = @StudentID, StudentClass = @StudentClass
                    WHERE ID = @ID"
   Dim insert = "INSERT INTO StudentData
                    (StudentName,StudentID,StudentClass,ID) 
                    VALUES(@StudentName,@StudentID,@StudentClass,@ID)"
    cnn.ConnectionString = "....."
    cmd.Connection = cnn
    For Each dr In dt.Rows
        ' This is important, at each loop you need to add again
        ' the parameters and not let the previous one still be 
        ' in the first positions 
        cmd.Parameters.Clear()
        cmd.CommandText = update
        cmd.Parameters.AddWithValue("@StudentName", dr("StudentName"))
        cmd.Parameters.AddWithValue("@StudentID", dr("StudentID"))
        cmd.Parameters.AddWithValue("@StudentClass", dr("StudentClass"))
        cmd.Parameters.AddWithValue("@ID", dr("ID"))
        cnn.Open()
        Dim count = cmd.ExecuteNonQuery()
        if count = 0 Then
            cmd.CommandText = insert
            cmd.ExecuteNonQuery()
        End If
        cnn.Close()
    Next
End Sub

In other words, you try to update the record with the key = ID. If you are successful then the result of the ExecuteNonQuery will be the number or rows updated (probably 1). If you are unsuccessful then you can go with the insert.

Notice also that to avoid a complex logic I have moved the declaration and the insert of the parameter @ID to be the last one. This allows your query to share the same set of parameters both for the UPDATE and for the INSERT command.
In OleDb parameters are not recognized by their name but by their position, so the ID parameter should be the last one both for the insert and the update command in such a way that the values in the parameter collection have the same order of the parameter placeholders.

Upvotes: 1

Related Questions