0

i have problem in my c# application, it was used MYSQL database and no problem where !, but when i change the database to MS access this problem appear: The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

and this is my code:

OleDbCommand cmd = new OleDbCommand("SELECT action.info, relation.id AS action FROM relation LEFT OUTER JOIN conditions ON relation.conditions_id = conditions.id LEFT OUTER JOIN situation ON relation.situation_id = situation.id LEFT OUTER JOIN car_type ON relation.car_id = car_type.id LEFT OUTER JOIN action ON relation.action_id = action.id LEFT OUTER JOIN signal AS signaal1 ON relation.signal_id = signaal1.id where car_type.info=@car_type" + string2 + "  ORDER BY conditions.id ASC", objConn);
            cmd.Parameters.AddWithValue("@car_type", radButton1.Text);

            OleDbDataReader dataReader = cmd.ExecuteReader();

the error appear when last line executed..! "OleDbDataReader dataReader = cmd.ExecuteReader();"

-------------------------------------EDITED I changed tables names by adding 'z' to tables names, and now another message appear:

enter image description here

Minions
  • 5,104
  • 5
  • 50
  • 91
  • "action" is a reserved word. You need to place brackets around it [action] or use another name for that item. – KingOfAllTrades May 06 '15 at 18:28
  • @KingOfAllTrades: Action is not in the [list of reserved words](https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html)? – Andomar May 06 '15 at 18:29
  • 2
    @GrantWinney: Access would not allow multiple left joins either? I seem to remember it requires parenthesis around consecutive joins. – Andomar May 06 '15 at 18:33
  • `action` is reserved in t-sql. Something with triggers. I agree it is not on either of those lists, but it will highlight in blue in SSMS. While OP is using MS-Access, I would remove all MS related SQL reserved words. – KingOfAllTrades May 06 '15 at 18:39
  • 2
    I would never use the same name (reserved word or not) both as an alias for a field expression and as a table name. The query above includes `SELECT relation.id AS action` and later `LEFT OUTER JOIN action`. Not claiming that contributes to the error, but it just seems wrong to me. :-( – HansUp May 06 '15 at 18:44
  • 1
    @HansUp I agree, and would add that words like `relation`, `condition`, and `action` always be avoided in SQL. I try to make all my aliases something readable but not an actual word. I would use `rels`, `cond` or `thisaction`. – KingOfAllTrades May 06 '15 at 18:58
  • 3
    Just seconding Andomar's statement that MSAccess requires parenthesis around consecutive joins. Of the form `FROM (((a LJ B) LJ c) LJ d)` – Uueerdo May 06 '15 at 19:07
  • What is the value of `string2`? That could be a sql injection vulnerability. – Chris Dunaway May 06 '15 at 19:50
  • I don't think that are reserved words ! because they was work in MYSQL ! – Minions May 06 '15 at 21:43
  • This is value of string2: string string2 = ""; if (radButton2 != null) { string2 = " and signaal1.info='" + radButton2.Text + "' "; } else string2 = " and signaal1.info='0' "; if (radButton3 != null) { string2 += " and situation.info='" + radButton3.Text + "' "; } – Minions May 06 '15 at 21:43
  • @user3763770, you said: "I don't think that are reserved words ! because they was work in MYSQL !". But different sql engines can work differently. So just because something worked in MySql does not mean it will work in another engine. – DWright May 06 '15 at 23:17
  • try SELECT action.info, relation.id AS action FROM ((((relation LEFT OUTER JOIN conditions ON relation.conditions_id = conditions.id) LEFT OUTER JOIN situation ON relation.situation_id = situation.id) LEFT OUTER JOIN car_type ON relation.car_id = car_type.id) LEFT OUTER JOIN action ON relation.action_id = action.id) LEFT OUTER JOIN signal AS signaal1 ON relation.signal_id = signaal1.id – nazark May 07 '15 at 06:56
  • The select statement includes a reserved word – Minions May 07 '15 at 10:07

1 Answers1

0

Thanx @Grant_Winney, i changed action and relation, the error disappear :D but another one appear :s ( i asked another question)

Minions
  • 5,104
  • 5
  • 50
  • 91