October 4, 2007 at 2:56 pm
Haii...
Ok, rigt im developing a database consist of four table. First table is Customer(attributes:CustID,Time),
second table is Bill(BillNo,CustID and time),third table is Orders(BillNo,Code,Name and Price), the last table is Foods(Code,name,category,price).
Customer__________>Bill________>Orders_________>Foods
this is the relation ship. What im trying to do is, when ever a customer order, the database will increment by one and date and time will be saved(date and time from sytem). The problem here is when ever i, tried to save. An error occur said that CustID in table Bill cannot be null. Is it when we add data in the Customer table, immediatly the data will shown in Table bill,since it is relationship.
Does anyone know how to insert a data into a table, and the table has the realtionship with it will have the same value
Thank U
October 4, 2007 at 3:14 pm
It would help if you would post your insert code so we could look at what is producing your error. It sounds like you are attempting to create a Bill row without inserting a value for the CustID. You must then have a FK set up between Bill and Customer on CustID and this is giving you the error. Your INSERT statement into your Bill table must contain a valid CustID.
October 4, 2007 at 4:31 pm
Aside from what John said, can you better explain what is inserted into each table and when? (order). Also, what relationships you have?
Typically you insert into the parent tables. Then use the data from that insert to insert into the child (and/or grandchild) tables.
October 5, 2007 at 12:07 am
Thank a lo to steve and rowan, i really appreciate it. I already know that someone will not understand what im try to say, my english quite bad. What im trying to do here is that, (starrt fromt the first table) when ever a customer order is been key in,autmaically the CustID will increment by1 and also the time will be saved. At the same time the Bill table is initiate,and BillNo will increment by 1(auto increment). Also the CustId and time will be saved. After that the system will redirect to the order menu form,to take order.
Private Sub Meja_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim dt As DateTime = DateTime.Now
lblTimeNow.Text = dt.ToLocalTime
Dim masa As String
masa = dt.ToLocalTime.ToString()
Dim DataSource As New SqlConnection("Data Source=ANNAS\SQLEXPRESS; Initial Catalog=DBRMS;Integrated Security=True")
Dim command As New SqlCommand("INSERT INTO Customer(Time) VALUES ('" & masa & "')", DataSource)
Dim command2 As New SqlCommand("INSERT INTO Bill2(Time) VALUES ('" & masa & "')", DataSource)
DataSource.Open()
command.ExecuteNonQuery()
command2.ExecuteNonQuery()
DataSource.Close()
End Sub
This is the code that i use to store the time into Customer table and Bill2.
i thought that in relationship of databased, when ever we insert data in first table, the foreign key in the second table will get the same data. But the problem here is it doesnt work,when i try to insert the data into database in the order form, the system said that CustID cannot contain NULL, even i already insert it in the first table.
I did ask somebody in other forum, he said that i need to create a method so that the CustID will be pass to the second table
October 5, 2007 at 4:36 am
...
i thought that in relationship of databased, when ever we insert data in first table, the foreign key in the second table will get the same data. But the problem here is it doesnt work,when i try to insert the data into database in the order form, the system said that CustID cannot contain NULL, even i already insert it in the first table.
I did ask somebody in other forum, he said that i need to create a method so that the CustID will be pass to the second table
Foreign keys do not create rows in the referenced table. This makes sense, since the referenced table generally has more columns for which values need to be specified, than the referenced columns in the foreign key. What foreign keys can do, is enforce referential integrity. When you insert a row into a table that has a foreign key on it, SQL Server will check whether the valuea for the referencing columns exist in the referenced table. If they do not exist, since SQL Server cannot create for you the row in the referenced table, it will raise an error. You can insert nulls however, if the columns are nullable.
In case of deletes and alters in the referenced table, if you delete or alter a referenced row, SQL Server can ensure that the rows from the referencing tables are updated/deleted. You can set this behavior for the foreign key constraint.
You may want to read http://msdn2.microsoft.com/en-us/library/ms175464.aspx
Regards,
Andras
October 5, 2007 at 11:43 am
ubuntuXP07,
I understand what you are trying to do. Here is what I suggest you do to accomplish what you are after. First, let's go over you table schema. What data type is your CustID column? Int? If so, I would suggest changing it to an IDNETITY column so that it auto-increments for you. This way, each time you INSERT a new Customer row, the CustID column will automatically increment by one (or by whatever increment you choose). If you are not familiar with IDENTITY, look it up in BOL and post any additional questions you may have. Next, you said you want to save the time of the Customer row insert. This is easy, just add a DEFAULT GETDATE() value to the datetime column that you wish to track this in. Each time you INSERT a new Customer row, the DEFAULT will then put the current date/time into your column. Next for the inserts themselves. What you need to do here is to create a stored procedure that does the following:
1. BEGIN TRANSACTION
2. INSERTs a row into your Customer table. You may have to manually find the next valid CustID if you are not going to use IDENTITY.
3. INSERT a row into your Bill table. Use the CustID from step #2.
4. COMMIT/ROLLBACK TRANSACTION
Instead of having your code call each INSERT seperately, put all of your T-SQL into a stored procedure that is designed to INSERT the rows in the proper order with the correct values. Then have your code call the stored procedure.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply