1

I have 2 tables which are the customers table and orders table. Basically my problem is that it always give me an error which is 'unknown field list'. I have tried in phpmyadmin XAMPP it works the only problem is that when I use it in VS studio c# it gives me an error. I'm confused the back ticks '`' don't work but the back ticks work on localhost. Please help me.

This is my sql syntax:

SQL = "SELECT o.`Order ID`, o.Description, o.Amount 
       FROM tbl_orders AS o 
           INNER JOIN tbl_customers AS c ON o.`Order ID` = c.`Order ID` 
       WHERE c.`Customer ID` = '" + cust_id + "'";
            cmd.Connection = dbCon;
            cmd.CommandText = SQL;
            rdr= cmd.ExecuteReader();
            dt.Load(rdr);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user3233787
  • 379
  • 1
  • 10
  • 32

2 Answers2

1

Have tried deleting and retyping the ` around the column names incase it didnt copy correctly. Also try putting a @ before the string so. 

SQL = @"<the sql>";

this works on mysql xampp

 MySqlConnection sqlConnection = new MySqlConnection(@"Server=localhost;Database=test;");
        var sql = @"SELECT o.`Order ID`, o.Description, o.Amount 
   FROM tbl_orders AS o 
       INNER JOIN tbl_customers AS c ON o.`Order ID` = c.`Order ID` 
   WHERE c.`Customer ID` = 1";
        sqlConnection.Open();
        MySqlCommand sqlCommand = new MySqlCommand(sql,sqlConnection);
        sqlCommand.ExecuteNonQuery();
        sqlConnection.Close();
Jake Rote
  • 2,177
  • 3
  • 16
  • 41
0

Columns with spaces in their name should be enclosed in square brackets.

Try

SQL = "SELECT o.[Order ID], o.Description, o.Amount 
       FROM tbl_orders AS o 
           INNER JOIN tbl_customers AS c ON o.[Order ID] = c.[Order ID] 
       WHERE c.[Customer ID] = '" + cust_id + "'";
            cmd.Connection = dbCon;
            cmd.CommandText = SQL;
            rdr= cmd.ExecuteReader();
            dt.Load(rdr);

http://tinyurl.com/qcf45vf

What is the use of the square brackets [] in sql statements?

Community
  • 1
  • 1
Myles B. Currie
  • 61
  • 5
  • MySql.Data.MySqlClient.MySqlException: 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 '[Order ID]... – user3233787 Jan 24 '14 at 23:08
  • Can you try removing the AS when setting the alias. So just ... FROM tbl_orders o INNER JOIN tbl_customers c ... – Myles B. Currie Jan 24 '14 at 23:09
  • the backticks work on localhost i just don't know why it won't work on vs studio. – user3233787 Jan 24 '14 at 23:10
  • What MySQL connector version are you using in your VS project? – Myles B. Currie Jan 24 '14 at 23:13
  • MySQL Connector Net 6.2.4 – user3233787 Jan 24 '14 at 23:20
  • Download the NuGet package manager extension for VS if you don't have it, this just makes package management a breeze then update your MySQL connector in your project to 6.7.4. the package is called "MySql.Data" I have just recreated your scenario on my localhost MySQL server and you original query runs fine when you remove the "AS" strings in the alias naming. Might also be worth sharing your full method for creating and executing the query (blank out any con strings) etc just to make sure you are using all the correct variable types. – Myles B. Currie Jan 24 '14 at 23:32
  • i dont know why it wont work on my pc but thank you for your help i really appreciate it. i guess i will use nested queries. – user3233787 Jan 24 '14 at 23:46