locked
Incorrect syntax near '?' exception RRS feed

  • Question

  • Hi,

     

    This simple code is retrieving column information from the schema. I have similar code working for dealing with other data and everything works well. However in this particular case an exception (Incorrect syntax near '?') is raised.

     

          string serverName =
          string dbName =
          string tableName =

          string connectionString = String.Format("Data Source={0};Database={1};Integrated Security=SSPI;", serverName, dbName);
          SqlConnection conn = new SqlConnection(connectionString);

          string query = @"SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"; 
          SqlCommand myCommand = new SqlCommand(query, conn);
          myCommand.Parameters.Add(new SqlParameter("", tableName));

          try
          {
            conn.Open();
            SqlDataReader reader = myCommand.ExecuteReader();
            while (reader.Read())
            {
            }
          }
          catch (SqlException ex)
          {
          }
          finally
          {
            conn.Close();
          }

     

    If I use dynamic SQL as bellow everything works ok.


          string query = String.Format(@"SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName);  
          SqlCommand myCommand = new SqlCommand(query, conn);

    Please let me know if there is any known limit/problem in using parameters for schema tables?

     

    Thanks,

    Dejan

     

    Monday, July 21, 2008 1:19 AM

Answers

  • Hi Dejan,

     

    You're using ODBC-style parameter markers but the SqlClient .NET data provider accepts only "named parameter" style markers. Just change it to:

     

    string query = @"SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @a"; 

     

    and

     

    myCommand.Parameters.Add(new SqlParameter("@a", tableName));

     

    If you were using the stored procedure rather than a parameterized SQL statement, the parameter names would be the ones specified in the stored proc definition. But in a parameterized query the names just have to match (between the query text and parameter name) and start with "@".

     

    If you want to maximize query plan reuse, it's also a best practice to specify a length for string parameters.

     

    Cheers,

    Bob Beauchemin

    SQLskills

     

    Monday, July 21, 2008 2:56 AM
    Answerer