0

Sup, guys. I have a table named 'mains' and 4 columns in it named 'ID', 'Date', 'Adresse' (yea-yea, I know there is mistakes in it) and 'Refills'. I need to INSERT new data if it not exists in table (checking it by ID), or if it exist UPDATE it. Problem is that I getting 'IF syntax error' and I really don't know why. Also, I have some qualm about 'UPDATE' part. Here is my code:

string CommandText = "IF (SELECT '" + Convert.ToInt64(dataGridView2[0, 0].Value) + "' FROM mains IS NULL) INSERT INTO mains (ID,Date,Adresse, Refills) values (" + Convert.ToInt64(dataGridView2[0, 0].Value) + ",'" + Date + "','" + Adresse + "'," + Convert.ToInt64(dataGridView2[3, 0].Value) + ") ELSE UPDATE mains SET Date='" + Date + "', Adresse = '" + Adresse + "', Refills = (Refills + 1) WHERE ID = '" + dataGridView2[0, 0].Value.ToString() + "' END IF";

Without C# (in SQL) it looks like:

IF (SELECT 'numerical id' FROM mains IS NULL) INSERT INTO mains (ID, Date, Adresse, Refills) values (numerical id, 'date converted to string (text)', 'text', number) ELSE UPDATE mains SET Date = 'date converted to string', Adresse = 'text', Refils = (Refils + 1) WHERE ID = 'numerical ID converted to string' END IF
baRUSHek
  • 73
  • 1
  • 1
  • 4
  • 2
    You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. – Soner Gönül Jun 19 '14 at 12:07
  • Its offline stuff so I dont really think I need parameterized queries. – baRUSHek Jun 19 '14 at 12:10
  • 1. Endorse use of parametrised queries: too many ways even "we thought it was good" data will get you into trobouble. 2. What is the value of `CommandText` after the assignment? – Richard Jun 19 '14 at 12:11
  • Also parameterized queries makes your code more readable – Archeg Jun 19 '14 at 12:11
  • @baRUSHek Get in the habit! For one thing, if someone's address fails because it has an apostrophe in it, it will make you look incompetent. – Tim Rogers Jun 19 '14 at 12:12
  • Have a look at the [documentation](http://www.sqlite.org/lang.html); there is no IF. Why are you trying to do this logic in SQL? – CL. Jun 19 '14 at 12:12
  • 2Richard: "IF (SELECT '2134121' FROM mains IS NULL) INSERT INTO mains (ID,Date,Adresse, Refills) values (2134121,'19.06.2014','Not assigned',0) ELSE UPDATE mains SET Date='19.06.2014', Adresse = 'Not assigned', Refills = (Refills + 1) WHERE ID = '2134121' END IF" 2Soner: Im working with SQL only for 3rd day, I'll try it out in a future. – baRUSHek Jun 19 '14 at 12:14
  • 2CL.: well, my bad. So how do I do it now? Without 'IF' and 'CASE'? – baRUSHek Jun 19 '14 at 12:17
  • Check out the linked question Steve referenced, "SQLite - UPSERT *not* INSERT or REPLACE" for your answer – Hambone Jun 19 '14 at 12:21
  • It doesn't work to me - it always INSERTs and never REPLACEs. – baRUSHek Jun 19 '14 at 12:23

0 Answers0