RE: create table error
Two things I'd guess at, try a semi-colon between statements and the !-- all possible answers for any poll available -- comment is an HTML one not a CF one, add two more hyphens !--- --- Ade -Original Message- From: Daniel Kessler [mailto:[EMAIL PROTECTED] Sent: 16 September 2004 12:47 To: CF-Talk Subject: create table error I'm trying to create a set of relational tables in Oracle.I'm receiving the error missing right parenthesis. The only other relational work that I've done was in mySQL so I'm sure that it's a syntax or keyword Oracle issue. Here's the two tables and it's the second one that gives me the error: !--- worked aok --- create table fsnep_polls ( p_id NUMBER Primary Key, p_date_added date, p_date_last_used date, p_question VARCHAR2(400), p_status NUMBER ) CREATE SEQUENCE unique_poll_Num_s START WITH 1 !--- ***I receive an error here***--- !-- all possible answers for any poll available -- create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_pollID NUMBER Foreign Key REFERENCES fsnep_polls(p_id), pA_answer NUMBER ) -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD20742-2611 301-405-2545 Phone www.phi.umd.edu [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: create table error
If you mean have a semi-colon between each create table and create sequence, I actually input them seperately, individually.Not as a set of statements.When I get the error, I'm only doing (without the comments field which I only have in my documentation): create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_pollID NUMBER Foreign Key REFERENCES fsnep_polls(p_id), pA_answer NUMBER ) If I remove the foregin key reference it's fine.I included the other table in the post so that you could see the reference explicitly. thanks. Two things I'd guess at, try a semi-colon between statements and the !-- all possible answers for any poll available -- comment is an HTML one not a CF one, add two more hyphens !--- --- [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: create table error
daniel kessler wrote: If you mean have a semi-colon between each create table and create sequence, I actually input them seperately, individually.Not as a set of statements.When I get the error, I'm only doing (without the comments field which I only have in my documentation): create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_pollID NUMBER Foreign Key REFERENCES fsnep_polls(p_id), pA_answer NUMBER ) create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_pollID NUMBER REFERENCES fsnep_polls(p_id), pA_answer NUMBER ) Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: create table error
That great and it executed. So I don't have a foreign key anymore?Is it not needed?I think that it's part of the available Oracle syntax, so what do I lose by not having it? btw, anyone know of a good online Oracle reference or list? create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_pollID NUMBER REFERENCES fsnep_polls(p_id), pA_answer NUMBER ) Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: create table error
daniel kessler wrote: That great and it executed. So I don't have a foreign key anymore?Is it not needed?I think that it's part of the available Oracle syntax, so what do I lose by not having it? You foreign key is still there, you just don't need the foreign key keyword if you define your foreign keys implicitly. You only need the keyword if you use the full CONSTRAINT syntax, i.e.: create table fsnep_pollAnswers ( pA_id NUMBER, pA_pollID NUMBER, pA_answer NUMBER, CONSTRAINT constraint_name PRIMARY KEY (pA_id), CONSTRAINT constraint_name FOREIGN KEY (pA_pollID) REFERENCES fsnep_polls(p_id) ) Every database I know off works like that. btw, anyone know of a good online Oracle reference or list? Google: http://www.google.com/search?q=oracle+foreign+key+create+table Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: create table error
I appreciate the explanation.It's much simpler to read. For the online reference, I didn't this topic specifically, though that's why I needed it today.I find google only good if I KNOW the keywords that I need.An online reference is a little easier to browse.I have a good CF one. I did find a pretty good Oracle site: http://www.dbasupport.com/dsc/ Thank you very much for the answers. daniel kessler wrote: That great and it executed. So I don't have a foreign key anymore?Is it not needed?I think that it's part of the available Oracle syntax, so what do I lose by not having it? You foreign key is still there, you just don't need the foreign key keyword if you define your foreign keys implicitly. You only need the keyword if you use the full CONSTRAINT syntax, i.e.: create table fsnep_pollAnswers ( pA_id NUMBER, pA_pollID NUMBER, pA_answer NUMBER, CONSTRAINT constraint_name PRIMARY KEY (pA_id), CONSTRAINT constraint_name FOREIGN KEY (pA_pollID) REFERENCES fsnep_polls(p_id) ) Every database I know off works like that. btw, anyone know of a good online Oracle reference or list? Google: http://www.google.com/search?q=oracle+foreign+key+create+table Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Create table ID field
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TABLE_NAME]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) ALTER TABLE TABLE_NAME ADD CONSTRAINT UNIQUELY_NAME_YOUR_PK PRIMARY KEY ( TABLE_FIELD_NAME ) Mike -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:53 AM To: CF-Talk Subject: Create table ID field I have this code to create a table in a cfm file: CFQuery datasource=test name=#trialname# Create table #trialname# (Orderdate Char (50) not null, Institution Char (50) not null, Customer Char (50) not null, address Char (50) not null, city Char (10) not null, state Char (10) not null, zip Char (5) not null, phone Char (8) not null, login Char (10) not null, email1 Char (50) not null) /CFQUERY What is the code to add a primary key ID field? Thx. Robert O. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
Hello Mike, I'm kinda new to this. I don't quite understand your coding. My primary key is in an Access table. How or where do I add this to my code listed below? Thx. Robert O. -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:56 AM To: CF-Talk Subject: RE: Create table ID field IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TABLE_NAME]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) ALTER TABLE TABLE_NAME ADD CONSTRAINT UNIQUELY_NAME_YOUR_PK PRIMARY KEY ( TABLE_FIELD_NAME ) Mike -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:53 AM To: CF-Talk Subject: Create table ID field I have this code to create a table in a cfm file: CFQuery datasource=test name=#trialname# Create table #trialname# (Orderdate Char (50) not null, Institution Char (50) not null, Customer Char (50) not null, address Char (50) not null, city Char (10) not null, state Char (10) not null, zip Char (5) not null, phone Char (8) not null, login Char (10) not null, email1 Char (50) not null) /CFQUERY What is the code to add a primary key ID field? Thx. Robert O. _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Create table ID field
Robert Orlini wrote: CFQuery datasource=test name=#trialname# Create table #trialname# (Orderdate Char (50) not null, Institution Char (50) not null, Customer Char (50) not null, address Char (50) not null, city Char (10) not null, state Char (10) not null, zip Char (5) not null, phone Char (8) not null, login Char (10) not null, email1 Char (50) not null) , id INTEGER , CONSTRAINT #trialname#_pkey PRIMARY KEY (id) /CFQUERY But as usual, check the manual of your dbms. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
Ahhh, Access. You stumped me then.:-) Check the manual as Jochem recommended The way I showed you was for SQL Server... -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 11:02 AM To: CF-Talk Subject: RE: Create table ID field Hello Mike, I'm kinda new to this. I don't quite understand your coding. My primary key is in an Access table. How or where do I add this to my code listed below? Thx. Robert O. -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:56 AM To: CF-Talk Subject: RE: Create table ID field IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TABLE_NAME]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) ALTER TABLE TABLE_NAME ADD CONSTRAINT UNIQUELY_NAME_YOUR_PK PRIMARY KEY ( TABLE_FIELD_NAME ) Mike -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:53 AM To: CF-Talk Subject: Create table ID field I have this code to create a table in a cfm file: CFQuery datasource=test name=#trialname# Create table #trialname# (Orderdate Char (50) not null, Institution Char (50) not null, Customer Char (50) not null, address Char (50) not null, city Char (10) not null, state Char (10) not null, zip Char (5) not null, phone Char (8) not null, login Char (10) not null, email1 Char (50) not null) /CFQUERY What is the code to add a primary key ID field? Thx. Robert O. _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
Depends on the database If you're using SQL Server; IDfield identity primary key You could expand it further IDfield int identity(1,1) primary key But they do the same thing -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:53 AM To: CF-Talk Subject: Create table ID field I have this code to create a table in a cfm file: CFQuery datasource=test name=#trialname# Create table #trialname# (Orderdate Char (50) not null, Institution Char (50) not null, Customer Char (50) not null, address Char (50) not null, city Char (10) not null, state Char (10) not null, zip Char (5) not null, phone Char (8) not null, login Char (10) not null, email1 Char (50) not null) /CFQUERY What is the code to add a primary key ID field? Thx. Robert O. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
Thanks Jochem,
I added your code as:
CFQuery datasource=test name=#trialname#
Create table #trialname#
(id INTEGER,
CONSTRAINT #trialname#_pkey PRIMARY KEY (id),
Orderdate Char (50) not null,
Institution Char (50) not null,
Customer Char (50) not null,
address Char (50) not null,
city Char (10) not null,
state Char (10) not null,
zip Char (5) not null,
phone Char (8) not null,
login Char (10) not null,
email1 Char (50) not null)
/CFQUERY
One final problem. I get an error ODBC Error Code = 23000 (Integrity constraint violation)PP [Microsoft][ODBC Microsoft Access Driver] Index or primary key cannot contain a Null value.
I get this when I try to insert my first record.
My code:
CFQUERY name=get datasource=test
INSERT INTO #trialname# (
Orderdate,
Institution)
VALUES ('#DateFormat(Now())#',
'#FORM.Institution#'')
/CFQUERY
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Friday, January 02, 2004 11:09 AM
To: CF-Talk
Subject: Re: Create table ID field
Robert Orlini wrote:
CFQuery datasource=test name=#trialname#
Create table #trialname#
(Orderdate Char (50) not null,
Institution Char (50) not null,
Customer Char (50) not null,
address Char (50) not null,
city Char (10) not null,
state Char (10) not null,
zip Char (5) not null,
phone Char (8) not null,
login Char (10) not null,
email1 Char (50) not null)
, id INTEGER
, CONSTRAINT #trialname#_pkey PRIMARY KEY (id)
/CFQUERY
But as usual, check the manual of your dbms.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Create table ID field
Robert Orlini wrote:
I added your code as:
CFQuery datasource=test name=#trialname#
Create table #trialname#
(id INTEGER,
CONSTRAINT #trialname#_pkey PRIMARY KEY (id),
Orderdate Char (50) not null,
Institution Char (50) not null,
Customer Char (50) not null,
address Char (50) not null,
city Char (10) not null,
state Char (10) not null,
zip Char (5) not null,
phone Char (8) not null,
login Char (10) not null,
email1 Char (50) not null)
/CFQUERY
One final problem. I get an error ODBC Error Code = 23000 (Integrity constraint violation)PP [Microsoft][ODBC Microsoft Access Driver] Index or primary key cannot contain a Null value.
I get this when I try to insert my first record.
My code:
CFQUERY name=get datasource=test
INSERT INTO #trialname# (
Orderdate,
Institution)
VALUES ('#DateFormat(Now())#',
'#FORM.Institution#'')
/CFQUERY
You need to insert a value for your primary key field.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Create table ID field
How do I do this? I've tried different ways. Is it in the Insert section?
RO
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Friday, January 02, 2004 11:21 AM
To: CF-Talk
Subject: Re: Create table ID field
Robert Orlini wrote:
I added your code as:
CFQuery datasource=test name=#trialname#
Create table #trialname#
(id INTEGER,
CONSTRAINT #trialname#_pkey PRIMARY KEY (id),
Orderdate Char (50) not null,
Institution Char (50) not null,
Customer Char (50) not null,
address Char (50) not null,
city Char (10) not null,
state Char (10) not null,
zip Char (5) not null,
phone Char (8) not null,
login Char (10) not null,
email1 Char (50) not null)
/CFQUERY
One final problem. I get an error ODBC Error Code = 23000 (Integrity constraint violation)PP [Microsoft][ODBC Microsoft Access Driver] Index or primary key cannot contain a Null value.
I get this when I try to insert my first record.
My code:
CFQUERY name=get datasource=test
INSERT INTO #trialname# (
Orderdate,
Institution)
VALUES ('#DateFormat(Now())#',
'#FORM.Institution#'')
/CFQUERY
You need to insert a value for your primary key field.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Create table ID field
Robert Orlini wrote: How do I do this? I've tried different ways. Is it in the Insert section? INSERT INTO #trialname# (ID, Orderdate, Institution) VALUES (#ID#, '#DateFormat(Now())#', '#FORM.Institution#') Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
One more thing please Jochem. What if I want to AutoNumber the primary id field? I can do that from the Access table, but is there a way to do it when I create the table? Thx! RO -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 11:51 AM To: CF-Talk Subject: Re: Create table ID field Robert Orlini wrote: How do I do this? I've tried different ways. Is it in the Insert section? INSERT INTO #trialname# (ID, Orderdate, Institution) VALUES (#ID#, '#DateFormat(Now())#', '#FORM.Institution#') Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
Here's an example for an Access table, where the primary key is an autonumber field, and there's a separate index as well. cfquery datasource=#request.blahblah# CREATE TABLE myfile ( ID COUNTER not NULL PRIMARY KEY, ParentID number NULL, LinkID number NULL, Slot number NULL , ItemType text(10) NULL ); /cfquery cfquery datasource=#request.blahblah# CREATE INDEX ParentID on myfile(ParentID); /cfquery Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
Matt, This is the code I have: CFQuery datasource=test name=#trialname# Create table #trialname# ( id INTEGER, CONSTRAINT #trialname#_pkey not null PRIMARY KEY (id), Orderdate Char (50) not null, Institution Char (50) not null... Do I jhave to add all those fields you specifiedjust to make it autonumber? Thx. RO -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 12:43 PM To: CF-Talk Subject: RE: Create table ID field Here's an example for an Access table, where the primary key is an autonumber field, and there's a separate index as well. cfquery datasource=#request.blahblah# CREATE TABLE myfile ( ID COUNTER not NULL PRIMARY KEY, ParentID number NULL, LinkID number NULL, Slot number NULL , ItemType text(10) NULL ); /cfquery cfquery datasource=#request.blahblah# CREATE INDEX ParentID on myfile(ParentID); /cfquery Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
No.That was a complete example of a table with an index (a table where I validate data at the code level, hence the allowance of null values, which is something that will be ignored on all Access versions prior to XP).This line sets up the autonumbered primary key: ID COUNTER not NULL PRIMARY KEY Of course name your pk to whatever you like.I call mine ID I know this code works on all versions of Access from '97 to XP.No idea if your version works at all.I know Access has more MSSQL-like features, but have never bothered to explore the details. Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 11:22 AM To: CF-Talk Subject: RE: Create table ID field Matt, This is the code I have: CFQuery datasource=test name=#trialname# Create table #trialname# ( id INTEGER, CONSTRAINT #trialname#_pkey not null PRIMARY KEY (id), Orderdate Char (50) not null, Institution Char (50) not null... Do I jhave to add all those fields you specifiedjust to make it autonumber? Thx. RO -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 12:43 PM To: CF-Talk Subject: RE: Create table ID field Here's an example for an Access table, where the primary key is an autonumber field, and there's a separate index as well. cfquery datasource=#request.blahblah# CREATE TABLE myfile ( ID COUNTER not NULL PRIMARY KEY, ParentID number NULL, LinkID number NULL, Slot number NULL , ItemType text(10) NULL ); /cfquery cfquery datasource=#request.blahblah# CREATE INDEX ParentID on myfile(ParentID); /cfquery Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
That did it!! Thanks much Matt. And thanks all the others for chipping in as well. Robert O. -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 2:39 PM To: CF-Talk Subject: RE: Create table ID field No.That was a complete example of a table with an index (a table where I validate data at the code level, hence the allowance of null values, which is something that will be ignored on all Access versions prior to XP).This line sets up the autonumbered primary key: ID COUNTER not NULL PRIMARY KEY Of course name your pk to whatever you like.I call mine ID I know this code works on all versions of Access from '97 to XP.No idea if your version works at all.I know Access has more MSSQL-like features, but have never bothered to explore the details. Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 11:22 AM To: CF-Talk Subject: RE: Create table ID field Matt, This is the code I have: CFQuery datasource=test name=#trialname# Create table #trialname# ( id INTEGER, CONSTRAINT #trialname#_pkey not null PRIMARY KEY (id), Orderdate Char (50) not null, Institution Char (50) not null... Do I jhave to add all those fields you specifiedjust to make it autonumber? Thx. RO -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 12:43 PM To: CF-Talk Subject: RE: Create table ID field Here's an example for an Access table, where the primary key is an autonumber field, and there's a separate index as well. cfquery datasource=#request.blahblah# CREATE TABLE myfile ( ID COUNTER not NULL PRIMARY KEY, ParentID number NULL, LinkID number NULL, Slot number NULL , ItemType text(10) NULL ); /cfquery cfquery datasource=#request.blahblah# CREATE INDEX ParentID on myfile(ParentID); /cfquery Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Create table ID field
This brings up an interesting question I've had for a while: I've seen a few (but only a few!) developers use CreateUUID() for creating a record's PKID, and therefore have the PKIDs be strings rather than integers. (their argument was that they didn't have to worry about another record being inserted in between when the CreateUUID() was called and when the first record was actually inserted into the DB)I questioned them about the impact this has on their joins and a few said they never saw a real decrease in performance (I don't have any proof, but I inherently distrust that statement!) and a few others say that they used that only for creating a unique ID, but that they had a separate ID field in each table that was numeric which they used for joins.This part seemed like overkill to me - surely there's some stored procedure that accomplishes all of this? Can someone who actually thinks about this stuff regularly (uh, that means you, Dave Watts!) explain whether there's any merit to either group of people above? I thought the whole point of autonumber fields was exactly to make this issue moot. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create table ID field
If I am not mistaken UUID is a big string of mess. Now granted a PK as a char or int may not inherently be much to fuss over when the chat gets large it does become a very big issue. I can't see how a large char could ever compete against a small int. At the bit level you can not say that 102031 is the same as parsing 122434TREgh-e-rfwer43 so and an so on. Regardless, comparing int to varchar is measured at the ms level so I wouldn't worry about it too much... Steve http://intranet Stephen E. Schuster PeopleSoft Administrator 2000 Ashland Drive Ashland, KY 41101 Office Phone 606.920.7447 Cell Phone 606.831.4590 -Original Message- From: John Quarto-vonTivadar [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 3:19 PM To: CF-Talk Subject: Re: Create table ID field This brings up an interesting question I've had for a while: I've seen a few (but only a few!) developers use CreateUUID() for creating a record's PKID, and therefore have the PKIDs be strings rather than integers. (their argument was that they didn't have to worry about another record being inserted in between when the CreateUUID() was called and when the first record was actually inserted into the DB)I questioned them about the impact this has on their joins and a few said they never saw a real decrease in performance (I don't have any proof, but I inherently distrust that statement!) and a few others say that they used that only for creating a unique ID, but that they had a separate ID field in each table that was numeric which they used for joins.This part seemed like overkill to me - surely there's some stored procedure that accomplishes all of this? Can someone who actually thinks about this stuff regularly (uh, that means you, Dave Watts!) explain whether there's any merit to either group of people above? I thought the whole point of autonumber fields was exactly to make this issue moot. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Create table ID field
John wrote: ...a few others say that they used that only for creating a unique ID, but that they had a separate ID field in each table that was numeric which they used for joins.This part seemed like overkill to me - surely there's some stored procedure that accomplishes all of this? I do this.To me, this method has a couple of benefits.Primary of which is it is db-independent.You run into trouble when you try to find one solution that will run on everything.If the software has to do that and you don't have the luxury of optimizing for a given platform, I think this is the best bet.Plus it requires no cftransactions, locks etc. to be able to pull out the most recently inserted ID. I have heard strenuous arguments that you should optimize for the platform you are running on and re-code if you change.These tend to be some very smart people and I won't argue with them, but personally I prefer a solution where -- if I must -- I can move from X to Y without worrying about platform-specifics.For me this goes as deep as eschewing stored procs altogether; an approach which I'm sure will cause some folks to absolutely blow a gasket, howl with laughter etc. But sticking to the subject at hand, I've always felt that one extra field and index provides me with a lot of flexibility at negligible cost. -- --- Matt Robertson,[EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com --- -- [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Create table ID field
John Quarto-vonTivadar wrote: This brings up an interesting question I've had for a while: I've seen a few (but only a few!) developers use CreateUUID() for creating a record's PKID, and therefore have the PKIDs be strings rather than integers. Storage and internal representation is not necessarily related to representation in the front-end. A UUID (or GUID) could easily be stored as an 128 bit integer. [1] record was actually inserted into the DB)I questioned them about the impact this has on their joins and a few said they never saw a real decrease in performance (I don't have any proof, but I inherently distrust that statement!) It shouldn't really matter much if the database uses a 128 bit integer approach. A bit more if it uses a string, but unless you use very large (and narrow) tables and indexes I expect it to drown in the noise if you benchmark it. and a few others say that they used that only for creating a unique ID, but that they had a separate ID field in each table that was numeric which they used for joins.This part seemed like overkill to me - surely there's some stored procedure that accomplishes all of this? Don't rely on stored procedures to fix design errors. Using any field other than the primary key for making foreign keys is not smart. I thought the whole point of autonumber fields was exactly to make this issue moot. There are situations where a simple autonumber doesn't do. Replication and inheritance for instance. [1] In fact, I would argue that is the only right way to store them, since there are 2 competing formats [2] with different generation algorithms and a different length string representation. Having a database that accepts both representations in the input and stores them as a 128 bit integer would be a nice way around that. You should be able to verify in your dbms manual what the internal representation of a UUID is and which type is used. [2] Microsoft uses a different format as CF for instance. But both formats are guaranteed not to overlap eachother ever in a 128 bit representation. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Create table
If I'm not mistaken it would be: cfquery datasource=#DataSourceName# CREATE TABLE #Form.UserFirstName#_#Form.UserMiddleName#_#Form.UserLastName# ( BookmarkID IDENTITY NOT NULL PRIMARY KEY, BookmarkName VARCHAR(255), BookmarkCategory VARCHAR(255), BookmarkURL VARCHAR(255), BookmarkDescription TEXT, BookmarkAdded DATETIME, BookmarkVisits INT, BookmarkRating VARCHAR(50), BookmarkLastVisit DATETIME ); /cfquery At 11:56 AM 10/24/2002 +1000, you wrote: What would be the SQL Server equivalent of the following code (particularly the COUNTER attribute)?: cfquery datasource=#DataSourceName# CREATE TABLE #Form.UserFirstName#_#Form.UserMiddleName#_#Form.UserLastName# ( BookmarkID COUNTER NOT NULL PRIMARY KEY, BookmarkName TEXT(255), BookmarkCategory TEXT(255), BookmarkURL TEXT(255), BookmarkDescription MEMO, BookmarkAdded DATETIME, BookmarkVisits NUMBER, BookmarkRating TEXT(50), BookmarkLastVisit DATETIME ); /cfquery Cheers! Peter Tilbrook Project Officer Strategic Development Australian Building Codes Board GPO Box 9839 CANBERRA ACT 2600 Telephone: (02) 6213 6731 Facsimile: (02) 6213 7287 ** The information contained in this e-mail, and any attachments to it, is intended for the use of addressee and is confidential. If you are not the intended recipient, you must not use, disclose, read, forward, copy or retain any of the information. If you have received this e-mail in error, please delete it and notify the sender by return e-mail or telephone. The Commonwealth does not warrant that any attachments are free from viruses or any other defects. You assume all liability for any loss, damage, or other consequences which may arise from opening or using the attachments. ** ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Create table
BookmarkID IDENTITY NOT NULL PRIMARY KEY, I think: BookmarkID INT IDENTITY NOT NULL PRIMARY KEY, Matthew Walker http://www.matthewwalker.net.nz/ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Create table
Matt, you're a legend! Thank you! I'll let you know what I needed the code for shortly. Cheers! Peter Tilbrook Project Officer Strategic Development Australian Building Codes Board GPO Box 9839 CANBERRA ACT 2600 Telephone: (02) 6213 6731 Facsimile: (02) 6213 7287 -Original Message- From: Matthew Walker [mailto:Matthew;cabbagetree.co.nz] Sent: Thursday, 24 October 2002 12:15 PM To: CF-Talk Subject: RE: Create table BookmarkID IDENTITY NOT NULL PRIMARY KEY, I think: BookmarkID INT IDENTITY NOT NULL PRIMARY KEY, Matthew Walker http://www.matthewwalker.net.nz/ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Create table
Matt, that worked! I left my SQL book at home but at least you helped me get this app running today! Thanks! -Original Message- From: Matthew Walker [mailto:Matthew;cabbagetree.co.nz] Sent: Thursday, 24 October 2002 12:15 PM To: CF-Talk Subject: RE: Create table BookmarkID IDENTITY NOT NULL PRIMARY KEY, I think: BookmarkID INT IDENTITY NOT NULL PRIMARY KEY, Matthew Walker http://www.matthewwalker.net.nz/ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Create Table Question
I'd like to proven wrong on this, but I think you're at the mercy of Jet-SQL which doesn't allow direct manipulation of numerous properties of an Access table. e.g. Default Value, AutoNumber types, etc... -Original Message- From: Will Ryan [SMTP:[EMAIL PROTECTED]] Sent: 13 November 2000 14:36 To: CF-Talk Subject: Create Table Question Greeting Guru's I'm trying to create a table in Access via CF. I need to create an AutoNumber field for an ID. I don't want to create it through access as I would have to upload a 60 meg DB to our offsite server. Any suggestions for the SQL Statement? create table T_AddBook ( AddID autonumber, Name varchar (20), Add1 varchar (40), Add2 varchar (40) ) Thanks, Will Will Ryan [EMAIL PROTECTED] http://www.zerotoeighty.com w: 410-327-9152 f: 410-327-4086 -- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] --- Any opinions expressed in this message are those of the individual and not necessarily the company. This message and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that you have received this message in error and that any use is strictly prohibited. Sapphire Technologies Ltd http://www.sapphire.net Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table Question
You've pretty much got ittry using text instead of varchar as the datatype for the charachter fields. -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Will Ryan [mailto:[EMAIL PROTECTED]] Sent: 13 November 2000 14:36 To: CF-Talk Subject: Create Table Question Greeting Guru's I'm trying to create a table in Access via CF. I need to create an AutoNumber field for an ID. I don't want to create it through access as I would have to upload a 60 meg DB to our offsite server. Any suggestions for the SQL Statement? create table T_AddBook ( AddID autonumber, Name varchar (20), Add1 varchar (40), Add2 varchar (40) ) Thanks, Will Will Ryan [EMAIL PROTECTED] http://www.zerotoeighty.com w: 410-327-9152 f: 410-327-4086 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Create Table Question
Try the below: create table T_AddBook ( AddID COUNTER PRIMARY KEY, Name text(20), Add1 text(40), Add2 text(40) Remove the PRIMARY KEY code if the filed is not to be a primary key. Sincerely, Gary Groomer - Original Message - From: "Will Ryan" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Monday, November 13, 2000 6:36 AM Subject: Create Table Question Greeting Guru's I'm trying to create a table in Access via CF. I need to create an AutoNumber field for an ID. I don't want to create it through access as I would have to upload a 60 meg DB to our offsite server. Any suggestions for the SQL Statement? create table T_AddBook ( AddID autonumber, Name varchar (20), Add1 varchar (40), Add2 varchar (40) ) Thanks, Will Will Ryan [EMAIL PROTECTED] http://www.zerotoeighty.com w: 410-327-9152 f: 410-327-4086 -- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Create Table Question
You can create autonumbers. use "Counter" Adding default values seems a bit tricky, or not possible, I have tried and tried on that point. create table T_AddBook ( AddID counter, Name text(20), Add1 text(40), Add2 text(40) ) Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Create Table Question
A ) is missing at the end. Uwe - Original Message - From: "Gary Groomer" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Montag, 13. November 2000 16:31 Subject: Re: Create Table Question Try the below: create table T_AddBook ( AddID COUNTER PRIMARY KEY, Name text(20), Add1 text(40), Add2 text(40) Remove the PRIMARY KEY code if the filed is not to be a primary key. Sincerely, Gary Groomer - Original Message - From: "Will Ryan" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Monday, November 13, 2000 6:36 AM Subject: Create Table Question Greeting Guru's I'm trying to create a table in Access via CF. I need to create an AutoNumber field for an ID. I don't want to create it through access as I would have to upload a 60 meg DB to our offsite server. Any suggestions for the SQL Statement? create table T_AddBook ( AddID autonumber, Name varchar (20), Add1 varchar (40), Add2 varchar (40) ) Thanks, Will Will Ryan [EMAIL PROTECTED] http://www.zerotoeighty.com w: 410-327-9152 f: 410-327-4086 -- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
Here's one I used earlier. CREATE TABLE FeaturedSites (FeaturedSiteID COUNTER, Link VARCHAR(255), URL VARCHAR(255), CONSTRAINT CreatePK PRIMARY KEY (FeaturedSiteID)); This creates a table called FeaturedSites with an Auto number field called FeaturedSiteID and two text fields (Link and URL), set to 255 characters. Then it creates a constraint that makes the FeaturedSiteID the primary key. Where you see "CreatePK", that's just the name of the constraint being created... ie you can call it whatever you want. -- Aidan Whitehall [EMAIL PROTECTED] Netshopper UK Ltd Advanced Web Solutions Services http://www.netshopperuk.com/ Telephone +44 (01744) 648650 Fax +44 (01744) 648651 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
To create a primary key create a unique clusted index after you have crated the table like so CREATE TABLE table name ( field1 int PRIMARY KEY CLUSTERED , field2 datetime DEFAULT GETDATE() , field3.. .. .. .. ) The other way is to creat the table and then create an unique clustered index index afterwards to create the primary key and then perform an alter table statement to set the default. hope that helps Andy -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:13 To: CF-Talk Subject: Create Table SQL I have 2 questions, I am writting an CREATE Table SQL statement for MS Access, How do I create the table to have a primary key? What statement do I use to create a date field with default Date()? Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
Sorry my last reply was confusing!.the example I gave was to create the default and the primary key at the same time as you create the tablenot afterwards as I suggested! If you want to do it afterwards here's how CREATE UNIQUE CLUSTERED INDEX PK_indexname ON tablename(fieldname) - you can list multiple field names if it is to be a joint primary key -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:23 To: CF-Talk Subject: RE: Create Table SQL To create a primary key create a unique clusted index after you have crated the table like so CREATE TABLE table name ( field1 int PRIMARY KEY CLUSTERED , field2 datetime DEFAULT GETDATE() , field3.. .. .. .. ) The other way is to creat the table and then create an unique clustered index index afterwards to create the primary key and then perform an alter table statement to set the default. hope that helps Andy -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:13 To: CF-Talk Subject: Create Table SQL I have 2 questions, I am writting an CREATE Table SQL statement for MS Access, How do I create the table to have a primary key? What statement do I use to create a date field with default Date()? Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
This isn't working for me and I can't figure out why. This works: auction_date DATE NOT NULL, however as soon as I ad the Default part auction_date DATE NOT NULL DEFAULT GETDATE(), I get errors. Any Ideas, Rich -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 9:23 AM To: CF-Talk Subject: RE: Create Table SQL To create a primary key create a unique clusted index after you have crated the table like so CREATE TABLE table name ( field1 int PRIMARY KEY CLUSTERED , field2 datetime DEFAULT GETDATE() , field3.. . . . ) The other way is to creat the table and then create an unique clustered index index afterwards to create the primary key and then perform an alter table statement to set the default. hope that helps Andy -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:13 To: CF-Talk Subject: Create Table SQL I have 2 questions, I am writting an CREATE Table SQL statement for MS Access, How do I create the table to have a primary key? What statement do I use to create a date field with default Date()? Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Create Table SQL
In Access setting type to "counter" works as an autonumber as well CREATE TABLE People( ID COUNTER, First Char(50) not null, Last Char(50) not null ) Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
Are you using SQL Server?if so neither will work as Date is not a valid type. Replace it with Datetime and it'll work -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:55 To: CF-Talk Subject: RE: Create Table SQL This isn't working for me and I can't figure out why. This works: auction_date DATE NOT NULL, however as soon as I ad the Default part auction_date DATE NOT NULL DEFAULT GETDATE(), I get errors. Any Ideas, Rich -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 9:23 AM To: CF-Talk Subject: RE: Create Table SQL To create a primary key create a unique clusted index after you have crated the table like so CREATE TABLE table name ( field1 int PRIMARY KEY CLUSTERED , field2 datetime DEFAULT GETDATE() , field3.. . . . ) The other way is to creat the table and then create an unique clustered index index afterwards to create the primary key and then perform an alter table statement to set the default. hope that helps Andy -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:13 To: CF-Talk Subject: Create Table SQL I have 2 questions, I am writting an CREATE Table SQL statement for MS Access, How do I create the table to have a primary key? What statement do I use to create a date field with default Date()? Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
Sorry...didn't read your initial mail properly. If you are using access you are quite right to use Date. Replace the function GETDATE() with NOW() for the default and see what happens -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:55 To: CF-Talk Subject: RE: Create Table SQL This isn't working for me and I can't figure out why. This works: auction_date DATE NOT NULL, however as soon as I ad the Default part auction_date DATE NOT NULL DEFAULT GETDATE(), I get errors. Any Ideas, Rich -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 9:23 AM To: CF-Talk Subject: RE: Create Table SQL To create a primary key create a unique clusted index after you have crated the table like so CREATE TABLE table name ( field1 int PRIMARY KEY CLUSTERED , field2 datetime DEFAULT GETDATE() , field3.. . . . ) The other way is to creat the table and then create an unique clustered index index afterwards to create the primary key and then perform an alter table statement to set the default. hope that helps Andy -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:13 To: CF-Talk Subject: Create Table SQL I have 2 questions, I am writting an CREATE Table SQL statement for MS Access, How do I create the table to have a primary key? What statement do I use to create a date field with default Date()? Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
This is a multi-part message in MIME format. --=_NextPart_000__01C0458B.5EE2DAB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit OK here is what I have so far: CREATE Table #userid# ( ID TEXT(40) NOT NULL PRIMARY KEY, userid TEXT(40) NOT NULL, category TEXT(10) NOT NULL, quant TEXT(10) NOT NULL, startprice TEXT(10) NOT NULL, duration TEXT(2) NOT NULL, auction_id TEXT(25) NOT NULL, title TEXT(45) NOT NULL, auction_date DATE NOT NULL, ebaybidareaMEMO NOT NULL ); This creates a table for the user with 10 fields. However I still have 2 problems. 1) The Primary Key is not set to auto number, how can I set this? Should I be using something in place of TEXT(40) I tried using AutoNumber but that returned errors. 2) I can't get the auction_date field to work. If I add the DEFAULT Date() to the SQL statement I get errors. Thanks, Rich --=_NextPart_000__01C0458B.5EE2DAB0 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IgYQAQaQCAAEAAABAAEAAQeQBgAI5AQAAADoAAEIgAcAGElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAgACAAEGgAMADgAAANAHCwADAAsALAUAIQEB A5AGAPwGAAAlCwACAAELACMAAAMAJgAACwApAAADADYAAB4AcAAB FQAAAFJFOiBDcmVhdGUgVGFibGUgU1FMAAIBcQABFgHARbVFxlHiaAhYm02M rFWE3byy24EAAAIBHQwBGFNNVFA6SUJUT0FEQFBVTktBU1MuQ09NAAsAAQ4AQAAG DgAIFUS1RcABAgEKDgEYANaQ1++mswRGj2g2rkmyT1nCgAAACwAfDgECAQkQ AQAAAI4CAACKAgAA9QMAAExaRnUvQwIcAwAKAHJjcGcxMjUWMgD4C2BuDhAwMzNPAfcCpAPjAgBj aArAc7BldDAgBxMCgH0KgZJ2CJB3awuAZDQMYA5jAFALAwu1IE9LIJJoBJBlIAQAIHcQ8CUFQEkT 8GF2FDBzb3QgZgrAOgqiCoQKgEOAUkVBVEUgVAGgMmwUMCN1ESAFEGQjxRW0KBW0SUQgGLoWsEBY VCg0MCkHsE8CVAewVUxMIFBSAElNQVJZIEtFfFksFbQXVBj/GgcbVWPpFKBlZwWweRxKD0AdL3xx dQBwBUAcLB9/CoBz6wGQACBwBRBjFDAhXx2JtmQIcBSgaQIgHEoyGfBtHT5hEsAlwl8cBSZ0NXsk fyXAdBcRHC0pTyfnZDseQRixRBaiGLIdTWVimGF5YheQCsBlYRiyME1FTU8aBxW0KTv5FbpUaBRR BQAv0B5QBCBfL+ABkRcRAhAFwHQUACDnF1IUcCpwaCAPQBVgCJAQbGRzLhiwSG93/mUVEAXAFNAj EAMQAyAU89QyICNgbxcBbTUwFbTuMRnwMkAUMFAFEADAHpHMS2UeoBRRbm8FQBEhzzMgFVAn8Dmx bnUG0ASQ/iwT8DWAMoADkTXxOYIyUfY/BgA6sHU1EBTBOmA0Af0LgGcVMQeAO5E84QuANtCzC2Aj kW9mHKgU0HQIgescEDy0QTnxTjpDPHA58KszwRShIAlwdAhwbj9R/wSQA2AREDdlFbQmwRTQOvH6 JwVAZztjFDAs2zTjOaJqdwWwazVBST5QFNBhhmRFMTPhREVGQRpgdRowRB5BKBnwObEz0lP+URqA IxEeUAeAINEU0EOip0HPMiIAcGtzG1VSDeAWaBW0EeEATEsAAYAIIAYAAMBG AAOFAwADgAggBgAAwEYAEIUDAAeACCAGAADA RgBShQAAJ2oBAB4ACYAIIAYAAMBGAFSFAAABBDku MAAeAAqACCAGAADARgA2hQAAAQEAHgALgAggBgAAwAAA AEYAN4UAAAEBAB4ADIAIIAYAAMBGADiFAAAB AQALAA2ACCAGAADARgCChQAAAQsAOoAIIAYAAMAA AABGAA6FAwA8gAggBgAAwEYAEYUDAD2ACCAG AADARgAYhQsAW4AIIAYAAMBGAAaFAwBc gAggBgAAwEYAAYUCAfgPAQAAABDWkNfvprMERo9oNq5Jsk9Z AgH6DwEQ1pDX76azBEaPaDauSbJPWQIB+w8BnwA4obsQBeUQGqG7CAAr KlbCAABQU1RQUlguRExMAABOSVRB+b+4AQCqADfZbgAAAEQ6XERvY3VtZW50cyBhbmQg U2V0dGluZ3NcQWRtaW5pc3RyYXRvclxMb2NhbCBTZXR0aW5nc1xBcHBsaWNhdGlvbiBEYXRhXE1p Y3Jvc29mdFxPdXRsb29rXG91dGxvb2sucHN0AAADAP4PBQMADTT9NwAAAgF/AAEy PEFFRUdJUEdJT0FET0pPRk5PSk9LTUVEUENCQUEuaWJ0b2FkQHB1bmthc3MuY29tPgMABhBk gH7wAwAHED0CAAADABAQAAMAERAAHgAIEAEAAABlT0tIRVJFSVNXSEFUSUhBVkVT T0ZBUjpDUkVBVEVUQUJMRSNVU0VSSUQjKElEVEVYVCg0MClOT1ROVUxMUFJJTUFSWUtFWSxVU0VS SURURVhUKDQwKU5PVE5VTEwsQ0FURUdPUgB3lQ== --=_NextPart_000__01C0458B.5EE2DAB0-- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
This is just a stab in the dark, but that SQL looks an awful lot like the SQL you use in SQL Server. Does it also work in Access (which Rich is using) or is there an alternate syntax? Bob -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 9:55 AM To: CF-Talk Subject: RE: Create Table SQL This isn't working for me and I can't figure out why. This works: auction_date DATE NOT NULL, however as soon as I ad the Default part auction_date DATE NOT NULL DEFAULT GETDATE(), I get errors. Any Ideas, Rich -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 9:23 AM To: CF-Talk Subject: RE: Create Table SQL To create a primary key create a unique clusted index after you have crated the table like so CREATE TABLE table name ( field1 int PRIMARY KEY CLUSTERED , field2 datetime DEFAULT GETDATE() , field3.. . . . ) The other way is to creat the table and then create an unique clustered index index afterwards to create the primary key and then perform an alter table statement to set the default. hope that helps Andy -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:13 To: CF-Talk Subject: Create Table SQL I have 2 questions, I am writting an CREATE Table SQL statement for MS Access, How do I create the table to have a primary key? What statement do I use to create a date field with default Date()? Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
OK here is what I have so far: CREATE Table #userid# ( ID TEXT(40) NOT NULL PRIMARY KEY, userid TEXT(40) NOT NULL, category TEXT(10) NOT NULL, quant TEXT(10) NOT NULL, startprice TEXT(10) NOT NULL, duration TEXT(2) NOT NULL, auction_id TEXT(25) NOT NULL, title TEXT(45) NOT NULL, auction_date DATE NOT NULL, ebaybidareaMEMO NOT NULL ); This creates a table for the user with 10 fields. However I still have 2 problems. 1) The Primary Key is not set to auto number, how can I set this? Should I be using something in place of TEXT(40) I tried using AutoNumber but that returned errors. 2) I can't get the auction_date field to work. If I add the DEFAULT Date() to the SQL statement I get errors. Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
1) Try using counter as the datatype for the field you want to be autonumber 2) Puzzling meI'll have a think!! -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 16:44 To: CF-Talk Cc: [EMAIL PROTECTED] Subject: RE: Create Table SQL This is a multi-part message in MIME format. --=_NextPart_000__01C0458B.5EE2DAB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit OK here is what I have so far: CREATE Table #userid# ( ID TEXT(40) NOT NULL PRIMARY KEY, userid TEXT(40) NOT NULL, category TEXT(10) NOT NULL, quant TEXT(10) NOT NULL, startprice TEXT(10) NOT NULL, duration TEXT(2) NOT NULL, auction_id TEXT(25) NOT NULL, title TEXT(45) NOT NULL, auction_date DATE NOT NULL, ebaybidareaMEMO NOT NULL ); This creates a table for the user with 10 fields. However I still have 2 problems. 1) The Primary Key is not set to auto number, how can I set this? Should I be using something in place of TEXT(40) I tried using AutoNumber but that returned errors. 2) I can't get the auction_date field to work. If I add the DEFAULT Date() to the SQL statement I get errors. Thanks, Rich --=_NextPart_000__01C0458B.5EE2DAB0 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IgYQAQaQCAAEAAABAAEAAQeQBgAI5AQAAADoAAEIgAcAGElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAgACAAEGgAMADgAAANAHCwADAAsALAUAIQEB A5AGAPwGAAAlCwACAAELACMAAAMAJgAACwApAAADADYAAB4AcAAB FQAAAFJFOiBDcmVhdGUgVGFibGUgU1FMAAIBcQABFgHARbVFxlHiaAhYm02M rFWE3byy24EAAAIBHQwBGFNNVFA6SUJUT0FEQFBVTktBU1MuQ09NAAsAAQ4AQAAG DgAIFUS1RcABAgEKDgEYANaQ1++mswRGj2g2rkmyT1nCgAAACwAfDgECAQkQ AQAAAI4CAACKAgAA9QMAAExaRnUvQwIcAwAKAHJjcGcxMjUWMgD4C2BuDhAwMzNPAfcCpAPjAgBj aArAc7BldDAgBxMCgH0KgZJ2CJB3awuAZDQMYA5jAFALAwu1IE9LIJJoBJBlIAQAIHcQ8CUFQEkT 8GF2FDBzb3QgZgrAOgqiCoQKgEOAUkVBVEUgVAGgMmwUMCN1ESAFEGQjxRW0KBW0SUQgGLoWsEBY VCg0MCkHsE8CVAewVUxMIFBSAElNQVJZIEtFfFksFbQXVBj/GgcbVWPpFKBlZwWweRxKD0AdL3xx dQBwBUAcLB9/CoBz6wGQACBwBRBjFDAhXx2JtmQIcBSgaQIgHEoyGfBtHT5hEsAlwl8cBSZ0NXsk fyXAdBcRHC0pTyfnZDseQRixRBaiGLIdTWVimGF5YheQCsBlYRiyME1FTU8aBxW0KTv5FbpUaBRR BQAv0B5QBCBfL+ABkRcRAhAFwHQUACDnF1IUcCpwaCAPQBVgCJAQbGRzLhiwSG93/mUVEAXAFNAj EAMQAyAU89QyICNgbxcBbTUwFbTuMRnwMkAUMFAFEADAHpHMS2UeoBRRbm8FQBEhzzMgFVAn8Dmx bnUG0ASQ/iwT8DWAMoADkTXxOYIyUfY/BgA6sHU1EBTBOmA0Af0LgGcVMQeAO5E84QuANtCzC2Aj kW9mHKgU0HQIgescEDy0QTnxTjpDPHA58KszwRShIAlwdAhwbj9R/wSQA2AREDdlFbQmwRTQOvH6 JwVAZztjFDAs2zTjOaJqdwWwazVBST5QFNBhhmRFMTPhREVGQRpgdRowRB5BKBnwObEz0lP+URqA IxEeUAeAINEU0EOip0HPMiIAcGtzG1VSDeAWaBW0EeEATEsAAYAIIAYAAMBG AAOFAwADgAggBgAAwEYAEIUDAAeACCAGAADA RgBShQAAJ2oBAB4ACYAIIAYAAMBGAFSFAAABBDku MAAeAAqACCAGAADARgA2hQAAAQEAHgALgAggBgAAwAAA AEYAN4UAAAEBAB4ADIAIIAYAAMBGADiFAAAB AQALAA2ACCAGAADARgCChQAAAQsAOoAIIAYAAMAA AABGAA6FAwA8gAggBgAAwEYAEYUDAD2ACCAG AADARgAYhQsAW4AIIAYAAMBGAAaFAwBc gAggBgAAwEYAAYUCAfgPAQAAABDWkNfvprMERo9oNq5Jsk9Z AgH6DwEQ1pDX76azBEaPaDauSbJPWQIB+w8BnwA4obsQBeUQGqG7CAAr KlbCAABQU1RQUlguRExMAABOSVRB+b+4AQCqADfZbgAAAEQ6XERvY3VtZW50cyBhbmQg U2V0dGluZ3NcQWRtaW5pc3RyYXRvclxMb2NhbCBTZXR0aW5nc1xBcHBsaWNhdGlvbiBEYXRhXE1p Y3Jvc29mdFxPdXRsb29rXG91dGxvb2sucHN0AAADAP4PBQMADTT9NwAAAgF/AAEy PEFFRUdJUEdJT0FET0pPRk5PSk9LTUVEUENCQUEuaWJ0b2FkQHB1bmthc3MuY29tPgMABhBk gH7wAwAHED0CAAADABAQAAMAERAAHgAIEAEAAA
RE: Create Table SQL
I am using Access 2000. If I manually go into the database I can set the Default to Date() and it will work however when I try to set it up from the SQL statement, I get errors. Rich -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 11:32 AM To: CF-Talk Subject: RE: Create Table SQL Are you using SQL Server?if so neither will work as Date is not a valid type. Replace it with Datetime and it'll work -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:55 To: CF-Talk Subject: RE: Create Table SQL This isn't working for me and I can't figure out why. This works: auction_date DATE NOT NULL, however as soon as I ad the Default part auction_date DATE NOT NULL DEFAULT GETDATE(), I get errors. Any Ideas, Rich -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 9:23 AM To: CF-Talk Subject: RE: Create Table SQL To create a primary key create a unique clusted index after you have crated the table like so CREATE TABLE table name ( field1 int PRIMARY KEY CLUSTERED , field2 datetime DEFAULT GETDATE() , field3.. . . . ) The other way is to creat the table and then create an unique clustered index index afterwards to create the primary key and then perform an alter table statement to set the default. hope that helps Andy -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: 03 November 2000 14:13 To: CF-Talk Subject: Create Table SQL I have 2 questions, I am writting an CREATE Table SQL statement for MS Access, How do I create the table to have a primary key? What statement do I use to create a date field with default Date()? Thanks, Rich Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http
RE: Create Table SQL
1) The Primary Key is not set to auto number, how can I set this? Should I be using something in place of TEXT(40) I tried using AutoNumber but that returned errors. CREATE Table #userid# ( ID COUNTER CONSTRAINT ID PRIMARY KEY, userid TEXT(40) NOT NULL, category TEXT(10) NOT NULL, quant TEXT(10) NOT NULL, startprice TEXT(10) NOT NULL, duration TEXT(2) NOT NULL, auction_id TEXT(25) NOT NULL, title TEXT(45) NOT NULL, auction_date DATE NOT NULL, ebaybidareaMEMO NOT NULL ); 2) I can't get the auction_date field to work. If I add the DEFAULT Date() to the SQL statement I get errors. I looked long and hard some time ago ... came to the conclusion that it can't be done through slq ... maybe through CFX using DAO ... I might have even started one but know I never successfully finished it. Joe Hoffman mailto:[EMAIL PROTECTED] National Institutes of Health Center for Information Technology Division of Computer System Services Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Create Table SQL
I solved the problem by using:
cfset auction_date="#DateFormat("#Now()#")#"
on the form page and then put '#auction_date#' in the insert statement.
However, I have another question now.
How can I create an SQL statement that will create a directory so my users
can upload to their own directory?
Thanks,
Rich
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Create TABLE in MS Access
This might be a little off topic: I am wondering if anyone knows how to specify field properties when using the Create TABLE or Alter TABLE SQL statements in MS Access. I am writing some code to create a few tables on the fly, and it would be very handy to be able to specify things like "required" and "Allow Zero Length strings", etc... when creating the tables or altering them. You should check out Jose's guide to using Jet SQL at http://www.citilink.com/~jgarrick/vbasic/database/jetsql.html I have a feeling that you can't set field properties such as "allow zero length" etc using jet SQL - although I may be mistaken. Rob Keniger -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
