Using Insert Command to Upsert to MySQL DB

Question

I'm creating an update Sub class in VB and use the insert instead of Update in Query because i want to update a multiple data in one run.

this is my references in multiple update data: SQL - Update multiple records in one query

but when I run it there is an error message.

"Missing semicolon (;) at end of SQL statement."

but in the end of my Query there is a Semicolon.

Here is my Query:

Insert into IngredientStock(IngredientID,CategoryId,UnitID,IngredientName,Price) 
values(46,2,1,'Beans', 100) ON DUPLICATE KEY 
UPDATE 
   ingredientID= Values(IngredientID), 
   CategoryID = Values(CategoryID), 
   UnitID = Values(UnitID), 
   IngredientName = Values(IngredientName), 
   Price = values(Price);

Here is my update Sub class

Sub UpdateInfo(ByVal table As String, ByVal PrimaryKey As String, ByVal C1 As String, ByVal C2 As String, ByVal C3 As String, ByVal C4 As String, ByVal Datas As String)
    con.Close()
    con.Open()
    Query = "Insert into " & table & "(" & PrimaryKey & "," & C1 & "," & C2 & "," & C3 & "," & C4 & ") values (" & Datas & ") ON DUPLICATE KEY UPDATE " & PrimaryKey & "= Values(" & PrimaryKey & "), " & C1 & " = values(" & C1 & ")," & C2 & " = values(" & C2 & "), " & C3 & " = values(" & C3 & ")," & C4 & " = values(" & C4 & ");"
    cmd = New OleDbCommand(Query, con)
    DR = cmd.ExecuteScalar
    con.Close()
End Sub

Pick a database: SQL Server and MySQL are 2 different things. — Ňɏssa Pøngjǣrdenlarp, Dec 14 '16 at 14:42
SQL Server doesn't have 'ON DUPLICATE KEY'. If this is MySQL, please can you remove the sql-server tag. — cloudsafe, Dec 14 '16 at 14:42
Now, please describe what an "update Sub class" is. The literal values make this a bit odd, but `ingredientID=46, ... IngredientName = 'Beans`...` should work — Ňɏssa Pøngjǣrdenlarp, Dec 14 '16 at 14:44
Dont build SQL using string concat - use SQL Parameters. Always. Not everything in that table is string. — Ňɏssa Pøngjǣrdenlarp, Dec 14 '16 at 14:49
done researching parameter but is there wrong in my Query? why i get that error? — Arnold Barrozo, Dec 14 '16 at 14:55
What do you expect to be the value in _..." = VALUES(" & C1 & ")"..._ C1 is the name of the column not the value to set for the column C1. That value is buried inside the Datas parameter. — Steve, Dec 14 '16 at 15:01
What @Steve is saying is that you passed all the data values together in `Data`, but you need access to each piece of it for the UPDATE portion - `ingredientID= Values(IngredientID)` you are trying to assign the column name as a value. It wont work. — Ňɏssa Pøngjǣrdenlarp, Dec 14 '16 at 15:37

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-12-14T15:09:22.097

First, never ever concat strings to create a SQL query. If something like the ingredient contains special characters (e.g. Palmer's Condensed Milk) it will fail.

SQL Parameters also preserve the data type - according to the one snippet, only one item is string, but the code is forcing them all to text. Additionally (and most importantly) SQL Parameters avoid SQL injection.

You need something like this for a MySQL Upsert:

Dim sql = <sql>
            INSERT INTO SampleZ 
                    (Id, Name, Country, Animal, Color, Price, ItemDate, Active) 
            VALUES 
                    (@id, @n, @c, @a, @clr, @p, @dt, @act) 
            ON DUPLICATE KEY UPDATE
                    Name=@n, Country=@c, 
                    Animal=@a, Color=@clr, Price=@p, 
                    ItemDate=@dt, Active=@act
                WHERE id = @id
          </sql>.Value

Using dbcon As New MySqlConnection(connStr)
    Using cmd As New MySqlCommand(sql, dbcon)

        cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = thisID
        If thisID = -1 Then
            cmd.Parameters("@id").Value = DBNull.Value
        End If

        cmd.Parameters.Add("@n", MySqlDbType.String).Value = TextBox1.Text
        cmd.Parameters.Add("@c", MySqlDbType.String).Value = ComboBox1.Text
        cmd.Parameters.Add("@a", MySqlDbType.String).Value = TextBox2.Text
        cmd.Parameters.Add("@clr", MySqlDbType.String).Value = TextBox3.Text

        cmd.Parameters.Add("@p", MySqlDbType.Decimal).Value = NumericUpDown1.Value
        cmd.Parameters.Add("@dt", MySqlDbType.DateTime).Value = DateTimePicker1.Value
        cmd.Parameters.Add("@act", MySqlDbType.Bit).Value = chkActive.Checked

        dbcon.Open()
        Dim rows = cmd.ExecuteNonQuery()

    End Using
End Using

Important Notes:

The above uses an XML literal so the query can be formatted to make sense
Each value is a Parameter with the datatype specified (MySqlDbType.DateTime
The Using blocks assure that the DbCOnnection and DbCommand object are properly closed and disposed at the end so the app doesnt leak resources
The UPDATE portion gets the data by assigning the same parameters.

MySql ON DUPLICATE KEY UPDATE Syntax

i think i don't need a long code like that.. there is only an error in my Query.. — Arnold Barrozo, Dec 14 '16 at 15:09
@ArnoldBarrozo It's not a case of how long the code is. If it's safer to use, and works, then do it. Less code doesn't make it better, it's just lazier if there is a better, longer alternative — David, Dec 14 '16 at 15:14
strange to see, someone downvoted this answer. What's going on in SO!!! — Rahul, Dec 14 '16 at 15:15
@Rahul It mystified me too - I have been squinting at it trying to find a typo or something. — Ňɏssa Pøngjǣrdenlarp, Dec 14 '16 at 15:18
@ArnoldBarrozo you have several problems in the code, not just a flaky semi colon. — Ňɏssa Pøngjǣrdenlarp, Dec 14 '16 at 15:24

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

ingredientID= Values () <-- here

values clause is not required.

Liink --> On Duplicate Key Update same as insert

edited May 23 '17 at 12:30

Community

1
1

answered Dec 14 '16 at 15:01

Adarsh Gangadharan

653
4
10

So i don't need to put a values inside the parenthesis? – Arnold Barrozo Dec 14 '16 at 15:06
Error message still the same – Arnold Barrozo Dec 14 '16 at 15:07

Using Insert Command to Upsert to MySQL DB

2 Answers2