SQL HELP!
-
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? -
sounds very cheem....cannot just insert at the end of the database and then retrieve it with conditions?Originally posted by Ayukat: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?
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. -
If your ID Field is Primary Key, make sure you select under the Index part "Yes (Duplicates OK)".Originally posted by Ayukat: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?
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) -
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?Originally posted by Ayukat: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? -
Originally posted by cannonprinter:
My guess is that he wants to keep the ID field same as the previous record.
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] -
If tht is the case y dont you add another column to keep the ID field?
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?
[/quote]
indeed? when u write insert statement it will auto generarte the ID for u...
[quote]
Originally posted by MCY:My guess is that he wants to keep the ID field same as the previous record.
btw..you could always ask in other developer forums like
sgdotnet.org or google it.. -
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" ) <- I need to insert the Primary Key ID too (which is 1), if not cannot insert.
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. -
For MS Access, you can change the settings to auto generate it, since it's the primary key.
For MySQL, an example below.code:(
CUSTOMER_ID INTEGER AUTO INCREMENT,
PRIMARY KEY(CUSTOMER_ID),
);
This statement will make it auto increase every time you insert a new entry. -
Now I get what you mean...Originally posted by Ayukat: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" ) <- I need to insert the Primary Key ID too (which is 1), if not cannot insert.
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.
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" ) <- less the ID field -
or u can also use this in sqlOriginally posted by ndmmxiaomayi:For MS Access, you can change the settings to auto generate it, since it's the primary key.
For MySQL, an example below.code:(
CUSTOMER_ID INTEGER AUTO INCREMENT,
PRIMARY KEY(CUSTOMER_ID),
);
This statement will make it auto increase every time you insert a new entry.
CREATE SEQUENCE CUSTOMER_ID
INCREMENT BY 1
START WITH 10
MAXVALUE 99
NOCACHE
NOCYCLE;
INSERT INTO Customer VALUES
(CUSTOMER_ID.NEXTVAL) -
re-create the table that you need...think of all the attributes and conditions you want it to have beforehand...
-
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 howOriginally posted by berll: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)
-
yah i know, thats y i say use in sqlOriginally posted by sohguanh: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 
just providing more alternatives for him
-
No see this kind of codes before...Originally posted by berll: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) -
Those are proprietary database product syntax called Oracle if I'm not wrongOriginally posted by ndmmxiaomayi:No see this kind of codes before... -
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.Originally posted by sohguanh:Those are proprietary database product syntax called Oracle if I'm not wrong -
Oracle very expensive lar. M$ Access and MySQL much cheaper for teaching purposesOriginally posted by ndmmxiaomayi: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. -
I see. Haha, not all my teachers are skilled in Oracle either, so not possible to have such a class.Originally posted by sohguanh:Oracle very expensive lar. M$ Access and MySQL much cheaper for teaching purposes -
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