c# SqlDataReader throwing syntax error, but the command runs fine in SQL Server?

Question

I'm getting a syntax error from my SQL command shown in the code below. The error is happening on the ExecuteReader line, saying

Incorrect syntax near the keyword 'JOIN'.'

I have no idea why it's throwing a syntax error, the command works perfectly fine in SQL Server. Here's the code:

private void button1_Click(object sender, EventArgs e)
{
    SqlCommand sqlcomViewSmashRoster;
    SqlDataReader dataReader;
    String strSql, strOutput ="";

    strSql = "SELECT Roster.CharacterName, Roster.TotalTournamentsParticiaped, Roster.TotalWins, Roster.TotalLosses, Roster.Championships, Roster.InjuriesCaused, Roster.Injured" +
             "FROM Roster" +
             "INNER JOIN VideoGames" +
             "ON VideoGames.VideoGame_ID = Roster.VideoGame_ID" +
             "WHERE roster.VideoGame_ID = 2";

    cnn.Open();

    sqlcomViewSmashRoster = new SqlCommand(strSql, cnn);

    dataReader = sqlcomViewSmashRoster.ExecuteReader();

    while (dataReader.Read())
    {
        strOutput = strOutput + dataReader.GetValue(0) + " - " + dataReader.GetValue(1) + "\n";
    }

    cnn.Close();

    MessageBox.Show(strOutput);
}

Thanks!

You don't white space between your words. So you have `"...Injured" + "FROM Roster" + "INNER JOIN VideoGames" + ...` which becomes `"... InjuredFROM RosterINNER Join`. you can see the problem now. — Thom A, Nov 03 '19 at 09:52
You're missing spaces everywhere: change `"FROM Roster"` to `" FROM Roster"` and also in all other rows. — Zohar Peled, Nov 03 '19 at 09:52
In addition to the aboove comments - You could have seen the error if you had a look at your _strSql_ variable in the debugger... — Daniel Schmid, Nov 03 '19 at 09:55
Thank you guys. I'm a bonafide noob to programming and databases in general, i'm in the middle of a Programming I course right now, we're just barely learning about IDE's in general but i've taken it upon myself to do a personal project and go ahead on my own, i literally don't even know where the debugger is in visual studio. — Christian Caraballo, Nov 03 '19 at 09:59

score 8 · Accepted Answer · answered Nov 03 '19 at 09:52

Whitespace. Remember that:

"abc" +
"def"

is "abcdef"

Verbatim string literals are your friend:

strSql = @"
SELECT Roster.CharacterName, Roster.TotalTournamentsParticiaped, Roster.TotalWins,
       Roster.TotalLosses, Roster.Championships, Roster.InjuriesCaused, Roster.Injured
FROM Roster
INNER JOIN VideoGames
ON VideoGames.VideoGame_ID = Roster.VideoGame_ID
WHERE roster.VideoGame_ID = 2
";

Not only is this easier to code in the first place, but you can easily copy/paste between SSMS and devenv, without having to add quotes etc.

Thank you so much. I'm really new to using sql in c#, and so far have only used one-liners so i didn't run into this issue before. I'll mark yours as the answer in a few minutes, as soon as it lets me! — Christian Caraballo, Nov 03 '19 at 09:56

Learning · Answer 2 · 2019-11-04T03:21:41.907

Add space before " otherwise it will concatenate as single word example

"FROM Roster" +
             "INNER JOIN VideoGames" +

will become FROM RosterINNER JOIN VideoGames so add space before " & after "

example

strSql = "SELECT Roster.CharacterName, Roster.TotalTournamentsParticiaped, Roster.TotalWins, Roster.TotalLosses, Roster.Championships, Roster.InjuriesCaused, Roster.Injured " +
             " FROM Roster " +
             " INNER JOIN VideoGames " +
             " ON VideoGames.VideoGame_ID = Roster.VideoGame_ID " +
             " WHERE roster.VideoGame_ID = 2 ";

c# SqlDataReader throwing syntax error, but the command runs fine in SQL Server?

2 Answers2