SQL HELP!

Ayukat SG

I want to to insert some values into a database using MS Access.

And I wanna keep the auto generated Primary Key, the ID Field..

Problem is this way I can't do an insert without altering the ID column... help!!!! How to insert... yet dun touch the ID column?

Xcert SG

sounds very cheem....cannot just insert at the end of the database and then retrieve it with conditions?

something like SELECT field1 WHERE condition1 is valid.

anyway....if U are looking for syntax help....can download the pdf manual from http://dev.mysql.com/doc/

I am not too familiar with SQL in MS Access...but my understanding is that MS SQL and mySQL syntax are the same so U should have no problems with using the pdf manual.

MCY SG

If your ID Field is Primary Key, make sure you select under the Index part "Yes (Duplicates OK)".

It seems rather strange cause usually for primary keys we don't allow duplicates so that each record will be unique but in your situation I would say your ID field will not be your Primary key (unless you combine with another field to make it unique)

cannonprinter SG

U already keep ur ID field as a primary key and u set it to auto generated already. what for u need to alter the ID column while inserting values into it?

MCY SG

Originally posted by cannonprinter:

U already keep ur ID field as a primary key and u set it to [b]auto generated already. what for u need to alter the ID column while inserting values into it? [/b]

My guess is that he wants to keep the ID field same as the previous record.

new_b SG

If tht is the case y dont you add another column to keep the ID field?

btw..you could always ask in other developer forums like
sgdotnet.org or google it..

Ayukat SG

How to say ar?

Lets say I insert a row into a database, and to do tat correctly I need to also insert the ID (which is the primary key that is auto generated.).

INSERT INTO Blahblah Values ( 1, "Blahblah" )

But I want the database Access to auto generate out the ID ( I dun wan to insert it manually ), while inserting a new row.

I am using VB.NET, n if I do an INSERT on a table tat contains column[ID, Data]

I can do use INSERT INTO Blahblah Vales ( 'Data' ) becoz it will not tally with the columns in the database, but I want the ID to auto generate itself using access, I dun wan to insert it manually...

I nid to keep every single ID unique.

ndmmxiaomayi SG

For MS Access, you can change the settings to auto generate it, since it's the primary key.

For MySQL, an example below.

This statement will make it auto increase every time you insert a new entry.

MCY SG

Now I get what you mean...
If your ID column is created as auto increment then you can ignore the ID field when adding new records. That is ...
INSERT INTO Blahblah (description) Values ("Blahblah" )

berll SG

or u can also use this in sql

CREATE SEQUENCE CUSTOMER_ID
INCREMENT BY 1
START WITH 10
MAXVALUE 99
NOCACHE
NOCYCLE;

INSERT INTO Customer VALUES
(CUSTOMER_ID.NEXTVAL)

R3SsH|n SG

re-create the table that you need…think of all the attributes and conditions you want it to have beforehand…

sohguanh SG

Above seem to be Oracle syntax. AFAIK, not all SQL extension are supoprted by all databases. For MS Access I dunno if above is acceptable syntax. Maybe it is so gotta juz execute and see how Mr. Green

berll SG

yah i know, thats y i say use in sql Laughing

just providing more alternatives for him Very Happy

ndmmxiaomayi SG

No see this kind of codes before...

sohguanh SG

Those are proprietary database product syntax called Oracle if I'm not wrong Mr. Green

ndmmxiaomayi SG

No wonder. School also never use, they don't teach it either. Only Microsoft SQL and MySQL. MS Access is we go and play ourselves one.

sohguanh SG

Oracle very expensive lar. M$ Access and MySQL much cheaper for teaching purposes Mr. Green

ndmmxiaomayi SG

I see. Haha, not all my teachers are skilled in Oracle either, so not possible to have such a class. Laughing

meltz SG

dont know this is what u want anot ...

create yr table first (MS access)

ID field .. set as primary key ,auto increment, no duplicate

SQL code

insert ( ' ', 'name', age); ... etc

the ' ' is for the ID ... each row u insert .. the id in the table will auto increase by 1