Syntax error in SQL for create a table C#

Question

Hallo ladies and gentlemen

I am strugling with what I think should be a easy solution but can't find it

Basically I am creating a table through SQL and there is a problem with the SQL when I run it. Here is my code:

OleDbCommand cmd = new OleDbCommand (@"CREATE TABLE " + ClientID + " (Transaction_ID INT PRIMARY KEY NOT NULL, Transaction CHAR(255), Date DATE, Transaction_Amount INT, Balance INT, Deposited INT, Withdrew INT, Loan INT, Applied INT, Approved INT, Payment_Monthly INT, Monthly_Income INT)", mydb);

Now I tested my SQL statement to found out which part of my SQL gives the error and I have found when I remove the fieldsTransaction and Date it works. If I remove only one of them it still crashes. I even tried to change data types like CHAR(255) to VARCHAR(255) also DATE to INT.

To be honest I can't seem to find the problem. Please help

Thank you for your time.

PS: ClientID is a combination of three numbers and three letters with a hashtag for example. 123#ABC

competent_tech · Accepted Answer · 2014-10-06 17:46:42Z

6

Those two column names are reserved words in SQL. Enclosing them in square brackets will solve your problem:

[Transaction] CHAR(255), 
[Date] DATE,

Keep in mind that any time you access these fields in dynamic SQL or stored procedures, you will have to use the square brackets as well, i.e.

SELECT * FROM ClientTable WHERE [Transaction] = 'A'

answered Oct 6, 2014 at 17:46

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

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

4 Comments

ChrisLively Over a year ago

A better solution would be to be more specific on those column names. By Transaction does the OP mean TransactionId or TransactionCode or something like that? By Date is that the date is was processed, the date it should be processed, the date it was approved, the date it was first entered? A more descriptive column name would be advisable.

competent_tech Over a year ago

I totally agree, but there are times when the developer has no control over the column names and they must be able to have a way to handle these scenarios. For example, we work with a 15 year old app that has column names similar to these and based on the fragility of the source application, we cannot afford to try to change the column names, so we are left with this kind of workaround. Obviously not ideal, but it is a real-life scenario.

Michael Over a year ago

A bigger problem that wasn't addressed is the table name: naming a table starting with a number will never work. Also, Depending on what flavor of SQL OP is using, the keyword Date for the column name may not need to be escaped (it works without motification in MS SQL 2012).

competent_tech Over a year ago

@Michael: I let the table naming slide because the OP said it worked when the columns were removed, so I assumed that the format of the table name wasn't quite as posted. However, the square brackets will allow you to create and use a table name that starts with numbers.

Collectives™ on Stack Overflow

Syntax error in SQL for create a table C#

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related