Query works in HeidiSQL but fails in C#

Question

The below Query works in HeidiSQL but gives me an error when trying to use it in C#

SELECT title.id,title.title,title.production_year,movie_info.info FROM title NATURAL JOIN movie_info WHERE title.id <= 1000;

It tells me the following:

Error

I use the following code to retrieve a MySqlDataReader object:

public override MySqlDataReader Retrieve(string sql)
{
    MySqlCommand cmd = new MySqlCommand(sql, GetConnection());
    return cmd.ExecuteReader();
}

I call it from my main method:

static void Main(string[] args)
{
    MySQLFacade facade = new MySQLFacade("127.0.0.1", "omitted", "omitted", "imdb");
    MySqlDataReader reader = facade.Retrieve(
        "SELECT title.id,title.title,title.production_year,movie_info.info" +
        "FROM title " +
        "NATURAL JOIN movie_info" +
        " WHERE title.id <= 1000;");
    using (reader)
    {
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Console.WriteLine(reader.GetValue(i));
            }
            Console.WriteLine();
        }
    }
    Console.ReadLine();
}

Am I possibly missing something about formatting an SQL string for usage in C# compared to a Database Manager?

Thomas Weller · Accepted Answer · 2015-03-29T20:43:02.940

2

There's a space missing before FROM:

MySqlDataReader reader = facade.Retrieve(
 "SELECT title.id,title.title,title.production_year,movie_info.info" + // <-- here
 "FROM title " +
 "NATURAL JOIN movie_info" +
 " WHERE title.id <= 1000;");

In your answer, you have put all into one line and you have added the missing space.

edited Mar 29 '15 at 20:43

answered Mar 29 '15 at 20:42

Thomas Weller

55,411
20
125
222

Oh yeah, that makes much more sense haha. Thanks! – OmniOwl Mar 29 '15 at 20:42

score 0 · Answer 2 · answered Mar 29 '15 at 22:31

Use the @ for multi-lines it is better and always use parameters to avoid sql injections:

static void Main(string[] args)
{
    MySQLFacade facade = new MySQLFacade("127.0.0.1", "omitted", "omitted", "imdb");
    MySqlDataReader reader = facade.Retrieve(
        @"SELECT title.id,title.title,title.production_year,movie_info.info
        FROM title
        NATURAL JOIN movie_info
        WHERE title.id <= 1000");
    using (reader)
    {
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Console.WriteLine(reader.GetValue(i));
            }
            Console.WriteLine();
        }
    }
    Console.ReadLine();
}

The @ before the text allow you to write on multil lines without the need to user the end " + on each line and it give u a space between the words when ever you go to a new line. — , Mar 30 '15 at 16:43

Query works in HeidiSQL but fails in C#

2 Answers2