How to insert a automatically generated Value "ID"

1
votes

I am working with an existing table using SQL. I have to insert the following data into this table. This is a homework Question! Below is the question and my code. I keep getting the following error.....Cannot insert the value NULL into column 'ProductID', table 'jc0541535.dbo.Products'; column does not allow nulls. INSERT fails. How do i get this ProductId to automatically generate?

  1. ProductID: The next automatically generated ID
  2. CategoryID: 4
  3. ProductCode: dgx_640
  4. ProductName: Yamaha DGX 640 88-Key Digital Piano
  5. Description: Long description to come
  6. ListPrice: 799.99
  7. DiscountPercent: 0
  8. DateAdded: Today's date/time

    use column list for this statment

< This is the code that I used but it is not working properly. Can someone please point out why.

INSERT INTO Products (ProductID,CategoryID,ProductCode,ProductName,Description,ListPrice,DiscountPercent ,DateAdded) VALUES(DEFAULT,4,'dgx_640','Yamaha DGX 640 88-Key Digital Piano', 'Long description to come.',799.99,0,CURRENT_TIMESTAMP);

5
sql-server
I just tried removing the Product column in the INSERT statement, and also the value that was in the VALUES statement....... now the Error is saying that the table Products is invalid? That is very strange and I don't understand - Aaron Collins

5 Answers

2
votes

Don't use DEFAULT -- just remove the column from the insert statement:

INSERT INTO Products (CategoryID,ProductCode,ProductName,Description,ListPrice,DiscountPercent ,DateAdded) 
VALUES(4,'dgx_640','Yamaha DGX 640 88-Key Digital Piano', 'Long description to come.',799.99,0,CURRENT_TIMESTAMP);

SQL Fiddle Demo

Also, as others have said, make sure the column is an IDENTITY.

0
votes

Check the column properties in SQL Server Management Studio. Ensure that "Is Identity" is set to 'Yes' for ProductID and that "Identity Increment" is set to 1.

0
votes

In design , click on the column product id -> column properties-> look for Identity Specification-> Identity Seed and increment by 1(or any desired value) and set (Is Identity) to yes/true.

0
votes
DECLARE @ProductID INTEGER

SELECT @ProductID = ISNULL(MAX(ProductID),1)
FROM Products

INSERT INTO products(ProductID, CategoryID, ...)
VALUES(@ProductID, 4, ...)

Will this be sufficient for your requirements?

0
votes

I had to take mine out and it looked like this: Try this

INSERT INTO Products
(CategoryID, ProductCode, ProductName, Description, ListPrice, DiscountPercent, DateAdded)
VALUES (4, 'dgx_640', 'Yamaha DGX 640 88-Key Digital Piano', 'Long description to come', 799.99, 0, CURRENT_TIMESTAMP);