We help IT Professionals succeed at work.

how do u create autonumber for primary ket in ms sql?

jblayney
jblayney asked
on
880 Views
Last Modified: 2010-05-18
Hello,
I am using ms sql for the first time, I am using it with dreamweaver..Whenever i try to use the record insertion , i get errors....

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'adID', table 'mglf2004.dbo.t_ad'; column does not allow nulls. INSERT fails.

It doesnt seem to recogize adID  as the primary key...this could be my problem, this is the first time i created a table with ms sql, for the adID field i couldnt find "autonumer" like i would with access or php myAdmin, so i use unique identifier,, is this what my problem is, or have i just forgotten how to use dreamweaver... (i have been hand coding db stuff for a while now)
Justin

Comment
Watch Question

Commented:
Hi jblayney,

Its probably because you try to add a null value into this column, and in sql server it do not accept null value.

In you table list, rigth click on the table and click modify table or edit table. Now you have all column and you could set primary key accept null or not and the autoNumber!

Hope it help!
Have Fun!

Author

Commented:
but thier is nothing that says autonumber.... just unique identifier....
Hi jblayney,

a table with an "autonumber" column would be created as:
CREATE TABLE table1
(
 id_num int IDENTITY(1,1),
 field1 varchar (20),
)



Esteban Felipe
www.estebanf.com

Commented:
jblayney,

In sqlServer, its something like increment set increment, you must a select a column to edit it



Have Fun!

Commented:
Its autoIncrement no? You can set the increment 1 or anything else.
CERTIFIED EXPERT

Commented:
Try this:

INSERT INTO mglf2004.dbo.t_ad (t_ad, col2, coletc)
Values (NEW_ID(), 5, 'Test')

Here is an example from help file.

CREATE TABLE cust
(
 cust_id uniqueidentifier NOT NULL
   DEFAULT newid(),
 company varchar(30) NOT NULL,
 contact_name varchar(60) NOT NULL,
 address varchar(30) NOT NULL,
 city varchar(30) NOT NULL,
 state_province varchar(10) NULL,
 postal_code varchar(10) NOT NULL,
 country varchar(20) NOT NULL,
 telephone varchar(15) NOT NULL,
 fax varchar(15) NULL
)
GO
-- Inserting data into cust table.
INSERT cust
(cust_id, company, contact_name, address, city, state_province,
 postal_code, country, telephone, fax)
VALUES
(newid(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL,
 '90110', 'Finland', '981-443655', '981-443655')

regards-
newid() is a very costly function. It would really mess performance in a heavy load production enviroment. And there's no point in using it because you can simple make your column identity.

BTW, there's not "autoincrement" in SqlServer

Author

Commented:
thanks everyone,
i am really confused, i dont know the first thng about ms sql, i know mySQl and access, cant i simply click on the column and choose something that will make datatype the primary key and autonumber?

i am using a Im to talk to the admin, he is using SQL Server Enterprise Manager., he says their is no autoincrement, autonumber, they cant be choosen

Author

Commented:
he says the only dattypes to choose from are as follows

bigint
binary
bit
char
datetime
decimal
float
int
money
nchar
ntext
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
sql_variant
text
timestamp
tinyint
uniqueident
varbinary
varchar

do i have to create yh table in a certain way before i add the fields?

Commented:
In enterprse manager, when you rigth click on your table, click modify table or edit.

Its show all column with their type and description also column name.

After click a column, down theres information look there, you can set an autoNumber (i dont know the exact name cause i dont have sql server at home, but you can) of course it must be integer or smallInt etc.

To set primary key, rigth click on your column and there should be an option!

Have Fun!

Author

Commented:
ok, when u choose integer, u can appartenly only  have 4 as the length, will this change when we make it autonumber?

should it be a required field or not?

Commented:
jblayney,

If its a required field, it will not accept null!

Also the primary key is always required, did you set a primary key in your table?? Also do you have admin privilege on the database?

Because autoIncrement must be very easy is just an option. I use it every day and its really simple ill try to find screenshot :)

Have Fun!
that 4 means four bytes. You can hold numbers up to 2,147,483,647 in a int column

Author

Commented:
this is what is happening, its not working

i made a new table.
made a new column
made it an int

at the bottom... just like in Access.
there's description

Default Value
that's a pull down menu that's blank...
Identity is set to No
Formula

and the rest is grayed out....
they are..

Precision
Scale
Identity Seed
Identity Increment
Is RowGuid
Collation
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:

Lol damn too fast :) check the time haha

Have Fun!

Author

Commented:
thank you everyone, all i can say is fu....... microsoft,

Commented:
jblayney,

:) microsoft make good development tools

glad to help!

Have Fun!

Commented:
I found the best way to cerate a autonumber ID like access is to use the Search Query Analyzer and use a simple IDENTITY clause.

ie.

CREATE TABLE yourtable
(
ID tinyint IDENTITY (1,1),
colum1 char (15),

-and so on.
)

Things can get confusing when constructing with the enterprise manager,  I only use it to view DB's

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.