Selecting from a SQL table but there is an exception thrown

Question

I get this error when I want to read the table:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at hamsohbat.Form1.showFriends(Update update) in C:\Users\Soroush\documents\visual studio 2015\Projects\hamsohbat\hamsohbat\Form1.cs:line 327 at hamsohbat.Form1.GetUpdates(Int64 ii, Int32 offset) in C:\Users\Soroush\documents\visual studio 2015\Projects\hamsohbat\hamsohbat\Form1.cs:line 113 ClientConnectionId:02ad4c40-e0e7-47ac-91cc-ad88bcdf057d Error Number:102,State:1,Class:15

the related section of my code is:

        using (SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;AttachDbFilename=" + Directory.GetCurrentDirectory() + @"\MembersDB.mdf;Integrated Security=True;User Instance=True"))
        {
            foreach (Int32 x in matches)
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT ([UserName], [FName], [LName], [NickName]) FROM [Table] WHERE [TelegramId]=" + x.ToString(), con))

                    using (SqlDataReader reader = cmd.ExecuteReader())

                        while (reader.Read())

                            bot.SendTextMessage(update.Message.Chat.Id, "Nick: " + reader["NickName"].ToString() + "\nFirst Name: " + reader["FName"].ToString() + "\nLast Name: " + reader["LName"].ToString() + "\nTelegram ID: @" + reader["UserName"].ToString());

            }

        }

I put some sendmessages between the lines of my code to tracing it and I think the problem is about this line (Maybe I'm wrong):

using (SqlCommand cmd = new SqlCommand("SELECT ([UserName], [FName], [LName], [NickName]) FROM [Table] WHERE [TelegramId]=" + x.ToString(), con))

and the columns of my table are Id, TelegramId, Username, FName, LName, Nickname

thank you for your attention

Answer 1

You don't have to put the braces ( ) at start and end of your column names, that is possibly the reason your query has syntax errror, you should have first tried running the query in the sql server and if it runs fine, then port it in the code base, change your query by removing the unnecessary braces:

SELECT [UserName], [FName], [LName], [NickName] FROM [Table]

and you should not be doing string concatenation in the queries the way you are doing right, you need to use parameterized queries to be safe from SQL Injection attacks.

For seeing how to write parameterized queries, please refer to the following post:

Parameterize SQL query

or this link can also be helpful:

http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

Hope it helps.

Answer 2

You only need remove "(" and ")" in SELECT query. I hope it will work for you.

using (SqlCommand cmd = new SqlCommand("SELECT [UserName], [FName], [LName], [NickName] FROM [Table] WHERE [TelegramId]=" + x.ToString(), con))

Answer 3

It looks like your use of brackets in your select statement is at fault:

SELECT ([UserName], [FName], [LName], [NickName]) FROM [Table] WHERE [TelegramId]=1

It should just be:

SELECT [UserName], [FName], [LName], [NickName] FROM [Table] WHERE [TelegramId]=1

(without the brackets before [UserName] and after [NickName]).

With the brackets, the database will be trying to interpret the bracketed term as a single term, and hence complaining about the unexpected comma.

Answer 4

Yesss I deleted the braces and it worked. Thank you friends

I changed that line of my code to:

using (SqlCommand cmd = new SqlCommand("SELECT ([UserName], [FName], [LName], [NickName]) FROM [Table] WHERE [TelegramId]=" + x.ToString(), con))

and it worked

Answer 5

change this line

using (SqlCommand cmd = new SqlCommand("SELECT ([UserName], [FName], [LName], [NickName]) FROM [Table] WHERE [TelegramId]=" + x.ToString(), con))

into this

using (SqlCommand cmd = new SqlCommand("SELECT [UserName], [FName], [LName], [NickName] FROM [Table] WHERE [TelegramId]=" + x.ToString(), con))