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
– Ňɏssa Pøngjǣrdenlarp, Commented Dec 14, 2016 at 14:42
SQL Server doesn't have 'ON DUPLICATE KEY'. If this is MySQL, please can you remove the sql-server tag. — cloudsafe
– cloudsafe, Commented Dec 14, 2016 at 14:42
Dont build SQL using string concat - use SQL Parameters. Always. Not everything in that table is string. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Dec 14, 2016 at 14:49
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
– Ňɏssa Pøngjǣrdenlarp, Commented Dec 14, 2016 at 15:37

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-12-14 15:09:22Z

3

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

edited Dec 14, 2016 at 15:09

answered Dec 14, 2016 at 14:57

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges65 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Arnold Barrozo Over a year ago

i think i don't need a long code like that.. there is only an error in my Query..

David Over a year ago

@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

Rahul Over a year ago

strange to see, someone downvoted this answer. What's going on in SO!!!

Ňɏssa Pøngjǣrdenlarp Over a year ago

@Rahul It mystified me too - I have been squinting at it trying to find a typo or something.

Ňɏssa Pøngjǣrdenlarp Over a year ago

@ArnoldBarrozo you have several problems in the code, not just a flaky semi colon.

|

Community · Accepted Answer · 2017-05-23 12:30:33Z

0

ingredientID= Values () <-- here

values clause is not required.

Liink --> On Duplicate Key Update same as insert

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Dec 14, 2016 at 15:01

Adarsh

7244 silver badges13 bronze badges

2 Comments

Arnold Barrozo Over a year ago

So i don't need to put a values inside the parenthesis?

Arnold Barrozo Over a year ago

Error message still the same

Collectives™ on Stack Overflow

Using Insert Command to Upsert to MySQL DB

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related