B. Clay Shannon February 2016

Why am I getting "Incorrect syntax near..." in my DDL here?

In my Winforms app, I'm trying to programmatically create a local SQL table with this code:

using (var connection = new 
    System.Data.SqlClient.SqlConnection(connStr))
{
    try
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("CREATE TABLE [dbo].[AssignmentHistory] ");
            sb.Append("(");
            sb.Append("[Id] INT NOT NULL PRIMARY KEY ");
            sb.Append("[WeekOfAssignment] DATE NOT NULL ");
            sb.Append("[TalkType] INT NOT NULL ");
            sb.Append("[StudentID_FK] INT NOT NULL FOREIGN KEY ");
            sb.Append("[AssistantID_FK] INT NOT NULL FOREIGN KEY ");
            sb.Append("[CounselPoint] INT NOT NULL");
            sb.Append(")");

            command.CommandText = sb.ToString();
            command.ExecuteNonQuery();
        }
    }
    //catch (SqlException sqlex) <= SqlException not 
        found/resolvable
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

...but I'm getting, "Incorrect syntax near WeekOfAssignment"

What is wrong with the syntax there? I derived this code from what I gleaned from here.

This is a "local" db, based on "Service-based database" as shown in the link above; if it makes any difference, I'm using VS 2015 Community.

Answers


JaydipJ February 2016

Missing commas

using (var command = connection.CreateCommand())
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("CREATE TABLE [dbo].[AssignmentHistory] ");
        sb.Append("(");
        sb.Append("[Id] INT NOT NULL PRIMARY KEY, ");
        sb.Append("[WeekOfAssignment] DATE NOT NULL ,");
        sb.Append("[TalkType] INT NOT NULL ,");
        sb.Append("[StudentID_FK] INT NOT NULL FOREIGN KEY, ");
        sb.Append("[AssistantID_FK] INT NOT NULL FOREIGN KEY, ");
        sb.Append("[CounselPoint] INT NOT NULL");
        sb.Append(")");

        command.CommandText = sb.ToString();
        command.ExecuteNonQuery();
    }

Post Status

Asked in February 2016
Viewed 3,553 times
Voted 4
Answered 1 times

Search




Leave an answer