Home > Software engineering >  Trying to create an SQL table with c# but having trouble with the query
Trying to create an SQL table with c# but having trouble with the query

Time:11-03

public static void CreateSqlTable() 
        {
            try
            {
                DateTime today = DateTime.Today;
                String query = 
                "CREATE TABLE [dbo].[01/19/2001_Test_Log]("
                 "[Entry_ID] [int] IDENTITY(1,1) NOT NULL,"
                 "[Execution_Time] [datetime] NULL,"
                 "[Message_Type] [varchar](4) NULL,"
                 "[Environment] [varchar](10) NULL,"
                 "[Method_ID] [int] NULL,"
                 "[Method_Description] [varchar](max) NULL,"
                 "[Execution_Duration] [float] NULL,"
                 "CONSTRAINT [PK_01/19/2001_Test_Log] PRIMARY KEY CLUSTERED"
                 "("
                 "[Entry_ID] ASC"
                 ")"
                 " ON [PRIMARY]";
                using (SqlConnection connection = new SqlConnection(credentials)) //credentials from connection string
                {
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                        connection.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                int i = 0;
            }
        }

Getting the error "Incorrect syntax near keyword 'ON'." Struggling to figure out where the issue is as this query runs fine in ssms. I have another method that inserts into a table using the connection string and this one uses the same so I do not think that is the issue here. Thanks!

Edit: Removed some and am now getting Invalid syntax near PRIMARY.

CodePudding user response:

You have problem with yours SQL Query. Propably duplicated ON[PRIMARY] text

...)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]...

should looks like

...)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]...

CodePudding user response:

I highly suggest using something like EntityFramework code first, or fluent SQL to generate tables via code.

If you insist on using inline SQL, then I suggest creating a string with an at symbol in front. Using the @ sign (string literal), you can create a multi-line string.

string bla = @"Something 
hehe look at me 
I'm many lines."
  • Related