What is wrong with the syntax of this SQL statement I am using to create a table?

Question

I keep running the code/debugger and even set break points so I can step through the code and haven't been able to find where what the syntax error is I know its within this C# method for creating a new table in the c-tree database.

Here's the code:

/*
   Define() method
   where specific data definitions are established also will include the definition of the table 
   being created as well as defining rows and columns of the table
*/
public static void Define()
{
    Console.WriteLine("DEFINE");

    try
    {
        //Creation of the table with the specified data types and the column Names 
        Console.WriteLine("\tCreating table...");

        cmd.CommandText = "CREATE TABLE testData (" +
           "Trackingid VARCHAR(35)," +
           "dates DATETIME," +
           "Name_Of_Recipient VARCHAR(35)," +
           "addresss VARCHAR(35)," +
           "address2 VARCHAR(31)," +
           "City VARCHAR(34)," +
           "Town VARCHAR(23)," +
           "states VARCHAR(5)," +
           "ZipCode INT )";
        cmd.ExecuteNonQuery();

    }
    catch(CtreeSqlException e)
    {
        Console.WriteLine(e + " could not define this table");
    }
    catch(Exception e)
    {
        Console.WriteLine(e + "Error the table could not be defined");
    }
}

Like I have mentioned before I did try and step through the code multiple times, I am assuming that it could be that I have to many column entries? or Relying on the VARCHAR data type heavily?

Exception message being thrown:

"        Creating table...
Ctree.Data.SqlClient.CtreeSqlException (0x7FFFB1DD): Syntax error ---> Ctree.SqlClient.Common.FcSqlException: Syntax error
   at Ctree.SqlClient.FcSqlXApi.SQLExec(FcStatement stmt, Int32 InStatementType, FcSqlDA ida, FcSqlDA oda, FcSqlCA sqlca)
   at Ctree.SqlClient.FcSqlXApi.Prepare(FcStatement stmt, FcSqlDA input_sqlda, FcSqlDA output_sqlda, Int32 fetchSize)
   at Ctree.SqlClient.FcConnection.Prepare(FcStatement statement, FcSqlDA inputDA, FcSqlDA outputDA, Int32 fetchSize)
   at Ctree.SqlClient.FcPreparedStatement..ctor(FcConnection connexion, String sql, Int32 fetchSize, Int32 timeout)
   at Ctree.Data.SqlClient.CtreeSqlCommand.InternalPrepare(Boolean resultSet)
   at Ctree.Data.SqlClient.CtreeSqlCommand.ExecuteNonQuery()
   at Ctree.Data.SqlClient.CtreeSqlCommand.ExecuteNonQuery()
   at DBbeta.Program.Define() in C:\Users\WVX0DYQ\source\repos\DBbeta\DBbeta\Program.cs:line 76 could not define this table"

[Zip codes are strings not ints](https://stackoverflow.com/questions/893454/). — Dour High Arch, Jun 25 '18 at 17:32
I don't know anything about ctree, but [that doesn't look at all like the documentation for creating a table.](https://docs.faircom.com/doc/ctreedn/#38364.htm) — Crowcoder, Jun 25 '18 at 17:33
@AceNinja1101 how do you expect to store a zip code that starts with 0... — maccettura, Jun 25 '18 at 17:34
Changing the zipcode to a character didn't really do anything @DourHighArch — AceNinja1101, Jun 25 '18 at 17:37
[This document](https://docs.faircom.com/doc/sqlref/#createtable.htm) suggests that the create table sql syntax for c-tree ends with a `;` — Jonathon Chase, Jun 25 '18 at 17:37
`ZipCode INT` is where the syntax error occurs. It should be `INTEGER` (but as others have mentioned, zip codes should be stored as char types). Reference: https://docs.faircom.com/doc/sqlref/#33350.htm — Bill the Lizard, Jun 25 '18 at 17:38
This is where I am getting my information in regrads to ctreeACE https://docs.faircom.com/doc/ado_net/#41986.htm — AceNinja1101, Jun 25 '18 at 17:46
Looks like `INT` should be `INTEGER` and `DATETIME` should be `TIMESTAMP` https://docs.faircom.com/doc/sqlref/sqlref.pdf — Aaron Dietz, Jun 25 '18 at 17:50
You have `DATETIME` right now so I just guessed you wanted both. Looks like your options are `DATE`, `TIME`, or `TIMESTAMP` (both date and time). — Aaron Dietz, Jun 25 '18 at 18:02

score 0 · Answer 1 · answered Jun 25 '18 at 18:13

First check whether table exists or not. If not exists then it create the table.

This is running example put your values and run this

And use INTEGER not int See this for Datatypes

var commandStr= "CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime)";

using (SqlCommand command = new SqlCommand(commandStr, con))
command.ExecuteNonQuery();

Find reference

What is wrong with the syntax of this SQL statement I am using to create a table?

1 Answers1