Is this SQL syntax different when being used in VB....

[skinrock]

I just realized that I'm going to need to create a table on my MS Access database inside my application. I have already been inserting and selecting from it successfully for weeks now, but I'm running into a problem when creating the table. Not to say that I can't create one at all, I can, it's the options on the column that I can't get right. Basically I have made a table that has a few number columns and a few text columns. I noticed in Access, they call the types "Number" and "Text" and "AutoNumber", well, those won't work in my SQL statement, I have to use "int" and "varchar". Now, my question is, how to I modify an int column to be auto incrementing? phpMyAdmin is showing me this:

CREATE TABLE `test` (

`num` INT NOT NULL AUTO_INCREMENT,
`test` VARCHAR NOT NULL ,
`test` VARCHAR NOT NULL 
)

but VB.net isn't liking the auto_increment line. The not null works, but the increment causes an error. So what is the vb.net version to making an int column auto increment?

 

[RiverGuy]

Try the keyword IDENTITY.

 

[skinrock]

It's not liking that keyword either.

 

[RiverGuy]

Try using IDENTITY or AUTO_INCREMENT without the NOT NULL, as an Identity column cannot be null be definition.

 

[skinrock]

Both of them cause errors even without NOT NULL. btw, I am using the int keyword as well, that shouldn't be a problem should it? It only works if I just use int, but never when I add identity or auto_increment.

 

[RiverGuy]

Wait..You have two columns with the same name. That has to be it.

 

[skinrock]

TNUMBER int,AMOUNT int,TYPE varchar,CATEGORY varchar,DATEOFT varchar

Those are the column names I'm using in my create statement. As soon as I add either identity or auto_increment to the TNUMBER column, it crashes.

 

[skinrock]

Does it have something to do with being an indexed column? I was reading that auto_increment can only be applied to columns that are indexed, how would I go about doing that?