CODEWITHSUNDEEP
c#mysql
user1530253
user1530253

Reputation:

"Error in your SQL syntax" while creating new table

I am getting an error while trying to create a Table with in MySQL. I can not figure out what it is stemming from. I thought it might be a reserved word "Name" but I renamed it CharName figuring that would help. Anyone have any insight into this?

Here is my string:

CREATE TABLE xxx.xxx.xxx.xxx_10FEED988A06 (
    `Date`  datetime NOT NULL ,
    `Guid`  varchar(255) NULL ,
    `CharName`  varchar(255) NULL ,
    `Server`  varchar(255) NULL ,
    `CpuId`  varchar(255) NULL ,
    `HardDriveId`  varchar(255) NULL ,
    `MacAddress`  varchar(255) NULL ,
    `IpAddress`  varchar(255) NULL ,
    `MD5`  varchar(255) NULL ,
    PRIMARY KEY (`Date`)
)

Here is my Error:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for the
 right syntax to use near '70.210.131.54_10FEED988A06 (`Date`  datetime NOT NULL 
 ,`Guid`  varchar(255) NULL' at line 1

Here is my Code:

public static void CreateNonUsersTable()
{
    const string myConString = "Server=" + Server + ";" +
                               "PORT=" + Port + ";" +
                               "DATABASE=" + NonusersDataBase + ";" +
                               "Persist Security Info=No;" +
                               "Convert Zero Datetime=True;" +
                               "Encrypt=True;" +
                               "SslMode=Required;" +
                               "username=" + DbUser + ";" +
                               "password=" + DbPass + ";";

    string createUser = @"CREATE TABLE " + Username + " (" +
                        "`Date`  datetime NOT NULL ," +
                        "`Guid`  varchar(255) NULL ," +
                        "`CharName`  varchar(255) NULL ," +
                        "`Server`  varchar(255) NULL ," +
                        "`CpuId`  varchar(255) NULL ," +
                        "`HardDriveId`  varchar(255) NULL ," +
                        "`MacAddress`  varchar(255) NULL ," +
                        "`IpAddress`  varchar(255) NULL ," +
                        "`MD5`  varchar(255) NULL ," +
                        "PRIMARY KEY (`Date`))";

    Logger.Write(myConString, Enums.LogType.Info);
    Logger.Write(createUser, Enums.LogType.Info);

    using (var conn = new MySqlConnection(myConString))
    {
        conn.Open();
        using (var cmd = new MySqlCommand(createUser, conn))
        {
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Write(ex.ToString(), Enums.LogType.Exception);
            }
            finally
            {
                cmd.Dispose();
            }
        }
        conn.Close();
        conn.Dispose();
    }
}

Upvotes: 0

Views: 148

Answers (4)

Alberto Solano
Alberto Solano

Reputation: 8227

You cannot create a table using a name like this xxx.xxx.xxx.xxx_10FEED988A06 with the dots. Using the dots is an illegal expression.

Use underscores _ instead (they're used as common convention). Their use is legal:

CREATE TABLE xxx_xxx_xxx_xxx_10FEED988A06

See SQL Fiddle here.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37243

you have to escape your table name by backticks .

replace this

  CREATE TABLE xxx.xxx.xxx.xxx_10FEED988A06

by

  CREATE TABLE `xxx.xxx.xxx.xxx_10FEED988A06`

Or better change . to some other caracters like _ , -

Upvotes: 0

Jason Rust
Jason Rust

Reputation: 346

Put the table name in backticks and it should work. Periods are messing it up because they are normally used to define a database/table combination (e.g. CREATE TABLE mydb.mytable)

Upvotes: 0

Jim H.
Jim H.

Reputation: 5579

The . periods in your table name are causing the error. See Does MySQL allows to create database with dot?

Upvotes: 3

Related Questions