Incorrect syntax near 'FROM' Keyword. C#

Question

I have tried debugging of code and commenting, but I can't seem to find the problem. I am getting the this error:

Incorrect syntax near 'FROM'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'FROM'.

My code:

public List<string> GetTableColumns(string tableName)
{
    List<string> colCollection = new List<string>();

    IDataReader reader = null;

    using (var cmd = MyDB.GetSqlStringCommand("SELECT * FROM " + tableName))
    {
        using (reader = MyDB.ExecuteReader(cmd))
        {
            foreach (DataRow r in reader.GetSchemaTable().Rows)
            {
                colCollection.Add(r["ColumnName"].ToString());
            }

            reader.Close();
        }
    }

    return colCollection;
}

Have you debugged this to see what `tableName` actually is? go do that and report back — TheGeneral, Jul 03 '18 at 05:58
What is the name of the Table and what is being passed as `tableName`? — Samvel Petrosov, Jul 03 '18 at 06:08
OT if you just want the columns, add a WHERE 1=0. You do apparently not want any rows, so why get them? — Hans Keﬆing, Jul 03 '18 at 06:13
I don't think error is in the query. Can you post the function details which is GetSqlStringCommand? — Rashedul.Rubel, Jul 03 '18 at 06:51
Generic debugging advice for concatenated queries: what *is* the resulting query text exactly? Pay special attention to missing or extra spaces and "special characters" — Hans Keﬆing, Jul 03 '18 at 07:42

Pranay Rana · Answer 1 · 2018-07-03T06:29:56.227

2

Suggest you to format string properly like using string format as below and also check tablename is present or not

 public List<string> GetTableColumns(string tableName)
 {
   if(!String.IsNullOrWhiteSpace(tableName))
   {
    string query = string.Format( "SELECT * FROM  [{0}]",tableName);
    // or use string interpolation
    string query = $"SELECT * FROM [{tableName}]";
    //rest of the code execute reader  
    //return collection of string   
   }
   return new List<string>();
  }

Add [] around table name because table name in sql may contains white space example. SELECT * FROM [My Table] , that also can cause issue (check this answer for more detail : SELECT query on a table with a space in the name using SQSH)

edited Jul 03 '18 at 06:29

answered Jul 03 '18 at 06:15

Pranay Rana

175,020
35
237
263

this is the same as `"SELECT * FROM " + tableName`, why do you think this can help? – vasily.sib Jul 03 '18 at 06:17
@vasily.sib - but its more clear and i added code to check tableName is empty or not – Pranay Rana Jul 03 '18 at 06:17
I very doubt about `more clear`. And checking for empty is no sence. `"some string " + string.Empty`, `"some string " + null` and `$"some string {null}"` will produce same string – vasily.sib Jul 03 '18 at 06:20
@vasily.sib - you are correct but if you send "select * from (no value in empty string)" then sql gives you error , that also cause of issue as per error given by OP in question – Pranay Rana Jul 03 '18 at 06:23
@vasily.sib - as per this `Incorrect syntax near 'FROM'.` error can be cause if there is not table value given or may be table value contains wrong input – Pranay Rana Jul 03 '18 at 06:24
you are right, but this is not a "formatting" issue, so your answer is misleading. I think your answer should contains information from your comment (the one with _select * from (no value in empty string)_) and not what it contains now. – vasily.sib Jul 03 '18 at 06:28
and one more. Is this `[{tableName}]` escape sequence is valid for MySQL, for example? – vasily.sib Jul 03 '18 at 07:49

score 0 · Answer 2 · answered Jul 03 '18 at 08:26

As @PranayRana mention in comments to his answer, this issue is mostly because of that tableName parameter, passed to GetTableColumns(), is null, or empty string. I'm 98% sure.

You need to check parameter value before you use it. But instead returning a default value (empty list, for example), I prefer throwing an exception (because it is, literally, exceptional situation), like this:

public List<string> GetTableColumns(string tableName)
{
    if (tableName == null)
        throw new ArgumentNullException(nameof(tableName));
    if (string.IsNullOrWhitespace(tableName))
        throw new ArgumentException("Table name can't be empty!", nameof(tableName));

    List<string> colCollection = new List<string>();

    using (var cmd = MyDB.GetSqlStringCommand($"SELECT * FROM {tableName}"))
    using (var reader = MyDB.ExecuteReader(cmd))
        foreach (DataRow r in reader.GetSchemaTable().Rows)
            colCollection.Add(r["ColumnName"].ToString());

    return colCollection;
}

One more thing. You also need to handle escaping of your SQL commands. For example, you can't just SELECT * FROM User; with MS SQL Server, you need to SELECT * FROM [User];, or even SELECT * FROM [dbo].[User]; sometimes. This may vary from DBMS to DBMS.

As advise, I recomend you to learn something about ORMs, like EntityFramework or NHibernate, because you will be then free from this DBMS-relative stuff like syntax, or escaping sequences.

Incorrect syntax near 'FROM' Keyword. C#

2 Answers2