February 18, 2012 at 9:33 am
Hello everyone. I'm currently a CIS major and working on a project for one of my classes. Basically the project is making an E-Commerce site. I'm doing most of the programming in ASP and connecting to SQL. No current issue with connection or anything. What I am having issues with is with pulling off a form into my SQL database.
In this database I have a Product table and 3 other subtype tables that relate back to it. They are Books, DVD's, and Video Games. Here's how I have the tables set up:
Product:
Product_ID (PK)
Title
Price
Description
..
..
Book:
Product_ID(PK, FK)
Author
ISBN
..
..
DVD:
Product_ID(PK,FK)
Rating
..
..
Ok so I want to allow the website user to insert data from the website into the tables. Since this is a supertype and subtype relationship between Products and the other 3 I need to pull from the form into 2 different tables. The difficulty I am having is when it comes to Product_ID in the subtype tables.
this is just something I tried for inserting into the sql table to test it out. I get an error saying Book.Product_ID can not be null. I guess I was wondering how you guys would do this and how to set Book.Product_ID to Product.Product_ID
Insert into dbo.Product(Title,Description,Price,Quantity_on_Hand, Product_Type) values('23','32432',23.33, 2, 2)
insert into dbo.Book(ISBN,Author,Publisher,Edition,Category,Cover_Type,Pages) values(2343242, 'asdfaf', '234234', '2','fad','asdf',234)
maybe I didn't set up the table or relationship correctly. SOrry if this question is vague or doesn't make sense... Been working on this for a while and my little brain is fried. :hehe:
February 18, 2012 at 9:51 am
May not be the only method but I would look at setting up an insert, update trigger on each of the subtype tables. Maybe a redesign of table structure to eliminate the subtypes and add a field to the product table to do reference a lookup table of types would help.
February 18, 2012 at 2:44 pm
If I am reading your posting correctly (and there is a good chance that I am not) in the Product table, you have designated the Product_ID (PK). Where I take PK to mean primary key.
Now you can not insert null values into a primary key column as you are attempting to do with this statement:
Insert into dbo.Product(Title,Description
,Price,Quantity_on_Hand, Product_Type)
values('23','32432',23.33, 2, 2)
To better help those who wish to help you please click on the first link in my signature block and read how to post readily consumable information. The article contains sufficient T-SQL code to make this task relatively easy and fast.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply