Solved

Create Table thru SQL with Auto_Increment

Posted on 2002-03-13
13
932 Views
Last Modified: 2013-12-24
I'm having difficulty with my create table code.. any ideas on whats wrong?

<cfquery name="Addtable" datasource="#application.db#">
  create table tbl_UserLinks
  (
       link_ID_PK INT NOT NULL default 0 auto_increment,
     address char(150),
     sent_by char(20),
     primary key (link_ID_PK)
  )
</cfquery>
0
Comment
Question by:M_Corkish
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 5

Expert Comment

by:Yog
ID: 6861041
I guess

<cfquery name="Addtable" datasource="#application.db#">
 create table tbl_UserLinks
 (
      link_ID_PK INT NOT NULL default 0 auto_increment PRIMARY KEY,
    address char(150),
    sent_by char(20)
 )
</cfquery>
0
 
LVL 1

Expert Comment

by:techy6
ID: 6861065
What DB are you using?  I think there's a problem with your last line - the primary key statement.  If I was doing this on Oracle, I'd do it like this:

create table tbl_UserLinks
 (
    link_ID_PK number(8) NOT NULL primary key,
    address varchar2(150),
    sent_by varchar2(20)
 )

and use a sequence in order to auto-increment your key values.

0
 

Author Comment

by:M_Corkish
ID: 6861165
I'm using ms-sql, but also testing it locally sometimes on a different db (local access db)

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 6

Expert Comment

by:reitzen
ID: 6861181
For SQL Server, use the following:

CREATE TABLE
  tbl_UserLinks
  (
     link_ID INT IDENTITY (1,1) NOT NULL,
  ,  address char(150) NOT NULL
  ,  sent_by char(20) NOT NULL
  )
  ON PRIMARY
0
 
LVL 11

Expert Comment

by:jimmy282
ID: 6861198
here is correct SQL for ms-sql

CREATE TABLE [dbo].[SUBSCRIBERS] (
     [id] [int] IDENTITY (1, 1) NOT NULL ,
     [Name] [varchar] (50) NULL ,
     [Email] [varchar] (50) NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[SUBSCRIBERS] WITH NOCHECK ADD
     CONSTRAINT [PK_SUBSCRIBERS] PRIMARY KEY  CLUSTERED
     (
          [id]
     )  ON [PRIMARY]



Change your table names and column names.

Jimmy
0
 

Author Comment

by:M_Corkish
ID: 6861205
neither ideas seemed to work.  I don't know whats wrong..
0
 
LVL 6

Expert Comment

by:reitzen
ID: 6861269
I got this to work on my SQL Server 7.0.  Run it using Query Analyzer:

CREATE TABLE [dbo].[tbl_UserLinks] (
     [link_ID] [int] IDENTITY (1, 1) NOT NULL ,
     [address] [char] (150) NOT NULL ,
     [sent_by] [char] (20) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_UserLinks] WITH NOCHECK ADD
     CONSTRAINT [PK_tbl_UserLinks] PRIMARY KEY  CLUSTERED
     (
          [link_ID]
     )  ON [PRIMARY]
GO
0
 
LVL 11

Expert Comment

by:jimmy282
ID: 6861281
what error?
0
 

Author Comment

by:M_Corkish
ID: 6861327
this is the error i get on the local ms access DB:
=============

37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

0
 
LVL 11

Accepted Solution

by:
jimmy282 earned 75 total points
ID: 6861464
this is for Access


create table tbl_UserLinks
 (
      link_ID_PK    AUTOINCREMENT NOT NULL ,
    address char(150),
    sent_by char(20),
PRIMARY KEY (link_ID_PK)
 )



Jimmy
0
 

Author Comment

by:M_Corkish
ID: 6863863
Jimmy, that did the trick!  so what exactly is going on there.. is it just the auto_increment becomes autoincrement that was throwing it?

cheers everyone.

0
 
LVL 11

Expert Comment

by:jimmy282
ID: 6863866
yes

auto_increment is used in MYSQL.
in MS-Sql its called Identity(1,1)
In Msaccess its autoincrement.

HTH
Jimmy
0
 

Author Comment

by:M_Corkish
ID: 6863949
so if my web server uses ms-sql, but the database i put on their server is an access db (not a commerical site, so access is fine! 8) should i be using the ms-sql code, or the access code?

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IIS 8.5 2 52
Using Route 53, Record Sets & Health Checks 2 Node Exchange 2016 environment 2 99
Redundant SQL Servers Without Clustering 7 108
System Analysis 5 58
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question