Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
annas
Starting Member
36 Posts |
 Posted - 2007-10-05 : 02:11:57
|
| Haiii, i got these problem that i can't solveWhat 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.The Code is in this URL:my nick there is UbuntuXp07[url]http://www.sqlservercentral.com/Forums/Topic407110-149-1.aspx?Update=1[/url]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 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-05 : 23:11:14
|
| You can use trigger to handle that. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-06 : 04:34:57
|
"autmaically the CustID will increment by1"Set the CustID column to IDENTITY"also the time will be saved"Set that column to have a Default of GetDate()"http://www.sqlservercentral.com/Forums/Topic407110-149-1.aspx?Update=1"Folk won't be able to read that if they are not registered.The essence seems to be that you are trying to do:INSERT INTO Customer(Time) VALUES ('" & masa & "')"INSERT INTO Bill2(Time) VALUES ('" & masa & "')"and presumably what you need to do is get the Identity value allocated to the new Customer record, ad then pass that to the Bill2 insert.So something like:INSERT INTO Customer(Time) VALUES ('" & masa & "')"; SELECT [MyID] = Scope_Identity()-- now get the [MyID] value in the resultset --INSERT INTO Bill2(Time, CustID) VALUES ('" & masa & "', " & MyID & ")"Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-06 : 04:35:39
|
| If you feel comfortable with it: a better way would be to create a Stored Procedure which did these two inserts for you, and you just pass the "masa" parameter to that Stored Procedure. |
|
|
|
|
|
|
|
|
Database Relationship