Zaphod2003


Can someone tell me what's wrong with this code

CREATE TABLE countries( ;

id n(10,0) PRIMARY KEY, ;

ccode c(2) UNIQUE, ;

CHECK (LEN(ALLTRIM(ccode))=2) ;

ERROR "Currency code must be 2 characters", ;

currency n(10,0), ;

CHECK (currency>0), ;

cname c(30) UNIQUE ;

)

I have indentified the problem to be with the code CHECK (currency>0) where FoxPro 9 errors with an unhelp Syntax error message

If i remove this check the command works.

What's wrong with it




Re: Using VFoxPro 9 and SQL to create a table

Alex Feldstein


Take out the comma before CHECK (currency>0)






Re: Using VFoxPro 9 and SQL to create a table

Zaphod2003

If I do this:

CREATE TABLE countries( ;

id n(10,0) PRIMARY KEY, ;

ccode c(2) UNIQUE, ;

CHECK (LEN(ALLTRIM(ccode))=2) ;

ERROR "Currency code must be 2 characters", ;

currency n(10,0) ;

CHECK (currency>0), ;

cname c(30) UNIQUE)

As you suggested it still errors






Re: Using VFoxPro 9 and SQL to create a table

Naomi Nosonovsky

Did you try to use different name of the field, say, yCurrency





Re: Using VFoxPro 9 and SQL to create a table

Alex Feldstein

Worked for me. What's the error message You do have a database container open, don't you





Re: Using VFoxPro 9 and SQL to create a table

Tamar E. Granor

You also need to remove the comma after UNIQUE. Think of the syntax this way:

CREATE TABLE TableName ( ;
<all information for field 1>, ;
<all information for field 2>, ;
.... ,
<table-level information>)

IOW, no commas within a field definition.

Tamar




Re: Using VFoxPro 9 and SQL to create a table

CetinBasoz

Like Tamar said you need to write all information for a field w/o commas:

CREATE TABLE countries( ;

id n(10,0) PRIMARY KEY, ;

ccode c(2) UNIQUE ;

CHECK (LEN(ALLTRIM(ccode))=2) ;

ERROR "Currency code must be 2 characters", ;

currency n(10,0) CHECK (currency>0), ;

cname c(30) UNIQUE ;

)





Re: Using VFoxPro 9 and SQL to create a table

Zaphod2003

Thanks guys, talk about forests and trees. You were correct, I had too many commas.





Re: Using VFoxPro 9 and SQL to create a table

Fox Me Up

also a key note.... dont name fields with reserved words [words in blue] they cause problems sometimes.




Re: Using VFoxPro 9 and SQL to create a table

Zaphod2003

Good point. I now make sure that all my fields and variables are not reserved names.