Error: "There was an error parsing the query." while trying to create SQL Server CE table

Question

I am trying to create a table in my SQL Server CE database. I am receiving an error. I thought it was the ' in the name, but even running it with just the numbers I receive the same error. I did a print on the command and ran it over at: http://www.w3schools.com/sql/trysql.asp?filename=trysql_create_table and it worked fine. Can someone steer me into the right direction of what I am missing?

Here is the error:

There was an error parsing the query. [Token in line number = 1, Token line offset 13, Token in error 191019 ]

Here is the code:

    public static void CreateDataTable(string dataBase, string tableName)
    {
        try
        {
            tableName = "John's License_191019";
            var createUser = string.Format(@"CREATE TABLE [{0}] (
                   [Identifier]  INT NOT NULL ,
                   [Id]  INT NULL ,
                   [Name]  NVARCHAR(255) NULL ,
                   [Zone]  INT NULL ,
                   [Map]  INT NULL ,
                   [State]  NVARCHAR(255) NULL ,
                   [Type]  NVARCHAR(255) NULL ,
                   [Faction]  NVARCHAR(255) NULL ,
                   [X]  FLOAT NULL ,
                   [Y]  FLOAT NULL ,
                   [Z]  FLOAT NULL ,
                   [Create_Date]  DATETIME NULL ,
                   [Update_Date]  DATETIME NULL ,
                   PRIMARY KEY ([Identifier]))", tableName);

            using (var conn = new SqlCeConnection(SQLHelper.Connection(dataBase)))
            {
                conn.Open();

                using (var cmd = new SqlCeCommand(createUser, conn))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("ExecuteNonQuery: " + ex);
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
                }

                conn.Close();
                conn.Dispose();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("CreateDataTable: " + ex);
            throw;
        }
    }

How the string prints:

CREATE TABLE [John's License_191019] ([Identifier] INT NOT NULL ,[Id] INT NULL ,[Name] NVARCHAR(255) NULL ,[Zone] INT NULL ,[Map] INT NULL ,[State] NVARCHAR(255) NULL ,[Type] NVARCHAR(255) NULL ,[Faction] NVARCHAR(255) NULL ,[X] FLOAT NULL ,[Y] FLOAT NULL ,[Z] FLOAT NULL ,[Create_Date] DATETIME NULL ,[Update_Date] DATETIME NULL ,PRIMARY KEY ([Identifier]))

Steve · Accepted Answer · 2014-06-11 19:39:59Z

1

The correct delimiters for Sql Compact Edition are not the backticks but the Open/Close square brackets

var createUser = @"CREATE TABLE [John's License_191019] (
                       [Identifier]  INT NOT NULL,
                       [Id]  INT NULL ,
                       [Name]  NVARCHAR(255) NULL ,
                       [Zone]  INT NULL ,
                       [Map]  INT NULL ,
                       [State]  NVARCHAR(255) NULL ,
                       [Type]  NVARCHAR(255) NULL ,
                       [Faction]  NVARCHAR(255) NULL ,
                       [X]  FLOAT NULL ,
                       [Y]  FLOAT NULL ,
                       [Z]  FLOAT NULL ,
                       [Create_Date]  DATETIME NULL ,
                       [Update_Date]  DATETIME NULL ,
                       PRIMARY KEY ([Identifier]))";

By the way, your code above don't need the string.Format (a typo?)

And two final notes.
An empty Try/Catch is a very bad practice. I suggest to remove it or handle the exception.
The using statement removes the need to call Close and Dispose on the objects involved.

answered Jun 11, 2014 at 19:39

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1530253 Over a year ago

string format was me not removing it before pasting the code. The tablename is actually passed into the method. I was just using the table name "John's License_191019" as a test case. There is also code for the try catch that I removed. Essentially just logging code that was not relevant to the problem =).

user1530253 Over a year ago

I received this error: " There was an error parsing the query. [Token in line number = 1, Token line offset 13, Token in error 191019 ] " I used your code except this time I made it use the string.Format and made "John's Licenses_191019" a string.

Steve Over a year ago

Could you update your question with this last version of your createUser variable?

Steve Over a year ago

Sorry, but I can't reproduce. What happen if you use a table name without spaces and quotes embedded in its name?

user1530253 Over a year ago

The error was being tossed here: MessageBox.Show("ExecuteNonQuery: " + ex); It was my code.. your code was correct. Thank you for your time.

Collectives™ on Stack Overflow

Error: "There was an error parsing the query." while trying to create SQL Server CE table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related