Adding ENUM column into a table (C#.NET)

[John Carret]

I need users to be able to edit the database file created by my application in MS Access and I
need to ensure they will enter suitable data only to prevent possible future errors. That's why
I need to use ENUM typed columns. I don't know where the problem might be but I'm not able to
add them into an Access database table. When I try to create a table with columns using

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\somefolder\\file.mdb";
string SQLQuery = "CREATE TABLE tname (" +
"col_1 ENUM('opt1', 'opt2') WITH COMPRESSION NOT NULL, " +
"col_2 VARCHAR(38) WITH COMPRESSION NOT NULL, " +
// etc.
"col_nminusone VARCHAR(9) WITH COMPRESSION NOT NULL, " +
"col_n TEXT WITH COMPRESSION NULL)";
OleDbCommand SQLCmd = new OleDbCommand(SQLQuery, conn);
conn.Open()
SQLCmd.ExecuteNonQuery();
conn.Close();

I encounter a "Field definition syntax error". I'm sure the error is caused by the ENUM thing,
because everything is fine when I execute the query without this part of the query string.
That's why I tried to execute the query without the "WITH COMPRESSION" or "NOT NULL" or both set
for the col_1, but the result was the same as the first time.

Another thing I tried is the SET column type => the same exception error message again.

What am I doing wrong?

Along with this I'd be very grateful for some other suggestions:
- how to set the format of a BIT typed column to yes/no upon creation using SQL query (this
property could be found in MS Access on the Lookup tabpage in the table design mode); the column
looks like a DataGridViewCheckBoxColumn then
- how to set the Allow Zero Length to false upon creation using SQL query

Any help would be greatly appreciated.

With regards
John Carret

 

[Miha Markic [MVP C#]]

Hi John,

I don't think that there is an enum type in JET.
Enum types are most probably created using master-detail schema when you
want to enforce the integrity.

 

[John Carret]

Hi Miha,
thank you for your response. I haven't programmed ADO etc. much yet so I have no idea what that
means. Could you please clarify it a bit? Or, would it be possible for you to provide a sample
source code?

Thank you in advance.

J.C.

 

[Miha Markic [MVP C#]]

Hi John,

Usually, the lookup values are stored in another table and both tables are
linked through a reference.
For example let's take table Persons. Each person can be either Male or
Female.
The master detail approach is to create table Genders
Id Descrition
1 Female
2 Male
Where Id is primary key.

In table Persons you would define a field GenderId (int) and create a
reference between Genders.Id -> Persons.GenderId. This reference will
guarantee you data integrity (no other value can be stored).
In UI you would display values from Genders and store appropriate Genders.Id
into Persons.GenderId when user has selected a gender.
This is just a very quick description on a very simple example.

HTH

 

[John Carret]

Miha,
thank you very much for the example. I think I'll code this quite quicky now... At first I
thought I'll solve this by setting the explicit values accepted in the column (as it could be
done directly in Access), but this is just as good solution as the previous one.

Thanks a lot...

With regards
J.C.


Miha Markic [MVP C#] napsal(a):