If not exists query results in "error in your SQL syntax"

Question

I would like to insert a row with SQL (in Visual Basic) and receive something like: "Error: You have an errin in your SQL syntax near IF NOT EXISTS ... at line 1"

Code looks like:

...
Try

    query = "IF NOT EXISTS (Select Movie_Name from MovieDB where Movie_Name=" & placeholder & ") INSERT INTO MovieDB (Movie_Name) VALUES ('" & placeholder & "')"

    'PREPARE CONNECTION
    MySQLCmd = New MySqlCommand(query, dbConnection)

    'OPEN DB 
    dbConnection.Open()

    'EXECUTE QUERY
    MySQLCmd.ExecuteNonQuery()

    'CLOSE DB
    dbConnection.Close()

Catch ex As Exception
    ...

I really dont get it anymore. Some ideas?

Where are you getting placeholder from, and what does it contain? — Dour High Arch
– Dour High Arch, Commented Oct 23, 2014 at 19:39
placeholder is a value from checkbox. When I check from a checkbox-list some movies, in placeholder will be the name of the checkbox (moviename) like "ABC.mkv". — user4175292
– user4175292, Commented Oct 23, 2014 at 20:17

D Stanley · Accepted Answer · 2014-10-23 19:46:47Z

1

At a minimum you need single quotes around the first string as well:

                                                                      --V                 --V
query = "IF NOT EXISTS (Select Movie_Name from MovieDB where Movie_Name='" & placeholder & "'); INSERT INTO MovieDB (Movie_Name) VALUES ('" & placeholder & "')"

As @Cameron astutely noticed, you also need a semicolon between the EXISTS function and the INSERT command

But you should look into using parameters instead of concatenating SQL - not only does it remove the need to delimit strings, but it prevents SQL injection and avoids syntax error if the string contains an apostrophe.

edited Oct 23, 2014 at 19:46

answered Oct 23, 2014 at 19:39

D Stanley

153k12 gold badges190 silver badges257 bronze badges

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

1 Comment

user4175292 Over a year ago

with the code above i receive this: Error: writeToDB You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (Select Movie_Name from MovieDB where Movie_Name='Enemy 2.mkv'' at line 1. I think there is now a ' to much...?

Thorsten Kettner · Accepted Answer · 2014-10-23 20:13:02Z

0

As far as I know "IF NOT EXISTS INSERT" is no valid MySQL syntax. This should work:

query = "INSERT INTO MovieDB (Movie_Name) VALUES ('" & placeholder & "') ON DUPLICATE KEY UPDATE Movie_Name = Movie_Name";

This tries to insert the new movie name and if this fails it does virtually nothing. (For this to work there must be a unique index on Movie_Name.)

answered Oct 23, 2014 at 20:13

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

If not exists query results in "error in your SQL syntax"

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related