Adding data into multiple tables

[o-mheien@hdsoftware.no]

Hi folks
Lets say you have two tables in the database: Customers and Orders
Ok. Lets assume the software starts with a browser that shows all the orders. User press the "insert new order" button and a blank Order form shows. On this the user can select the customer. Lets allso assume that the customer doesnt exist. This means, New order and new customer.
ok. Her's the problem:
When the user press the NEW Customer button I have the following code;

if frmCustomers.ShowDialog = [SIZE=2]Windows.Forms.DialogResult.OK[/SIZE]
[SIZE=2]  Me.CustomerTableAdapter.Insert(frmCustomer.CustomerName.text,,,,,etc)[/SIZE]
[SIZE=2]End if[/SIZE]
[SIZE=2]

[/SIZE]
Now we have the new customer, but how to I get the new record ID to add to the order record?

Please help
Ole

 

[cjard]

as a start, you can read the dw2 link in my signature, section on displaying related data

also, this is a strong case for having your customer PK made up from data about the customer, not a meaningless autonumber...

 

[arnet11]

one way is to have a table which stores max ids of every table, say "tablemax", for every insert opertaion retrive max id from that table and also modify tablemax for present max id.

but i dont think it is prefreable

 

[o-mheien@hdsoftware.no]

Thanx to both of you, but none of the answers solves the situation like I want. I mean, this problem is possibly the most common situation in all programs. I am used to work in Clarion, which is a dev tool that doesnt use database engines. This means I have to code the autonumber myselfe and of course, I can do that here to, but why should i? I mean, the server has this already built in. Allso, the server side relations is buold using primary key, which as far as I know, nead to be an autonumbered integer of some sort.

Bottom line - does this mean that i still have to take care of this manually?

 

[cjard]

Allso, the server side relations is buold using primary key, which as far as I know, nead to be an autonumbered integer of some sort.

Nooo... no database system was ever like that. A primary key is a column or multiple columns suitable for uniquely identifying a particular row

Suppose you have a table, TblAddress

We can give every row an autonumber, sure, but can we think of enough about an address to uniquely identify it?

Well, it cant be address line 1, because there might be ten roads called "Smith Street" in the country
It cant be just the postcode because lots of buildings have the same postcode
It cant be postcode + house number because of apartment blocks
But what about flat_number + building + postcode?

If youre still struggling with this idea, post the structure of your table and we will help out

 

[o-mheien@hdsoftware.no]

Well, working with databases (file based) is something I have been working with for 20 years så building relations is not the problem. This is sort of a theoretical problem, probably because I give to much credit to a system that calls it self a Database Engine. I dont see any reason why I would nead two keys in a table that are unique. In my opinion a foreign key is only to be used as a search key/index sort of speak. I have allways used the primary key in my relations in the past. I understand from what you say that this is not the situation and that the primary key is only to be used by the engine itselfe. I allso understand that relations on the server side is only for showing data.

Thanx

Ole

 

[cjard]

Well, working with databases (file based) is something I have been working with for 20 years så building relations is not the problem. This is sort of a theoretical problem, probably because I give to much credit to a system that calls it self a Database Engine.

SQL Server? Ya, I agree it's not as good (for what I want) as other engines..

I dont see any reason why I would nead two keys in a table that are unique.

Indeed, the idea is that you have one unique key, and if that can be made from the data, then why have an additional column that stores an incrementing number for no good reason other than to guarantee a unique value. What I'm saying is, forget the autonumber, and make a PK from the data itself..

In my opinion a foreign key is only to be used as a search key/index sort of speak.

A foreign key is actually implemented as a constraint in most databases. It may use an index, but it is not the index in the child table. When a record is inserted into the child, a select is performed on the parent to ensure that a parent record exists. The foreign key, in ensuring this is a constraint becuase you are constrained to entering only data that has a parent.

I have allways used the primary key in my relations in the past.

It's not actually mandatory from a conceptual point of view, to have a primary key to enforce a foreign key constraint, as any set of columns from the parent table could be selected from to ensure they exist. From an efficiency point of view, the columns in the parent should be indexed, and from a relational/hierarchical point of view, it makes sense to have them unique. As it can be asserted that a unique index and a primary key are synonymous, you could say that PK is a sensible requirement for the concept of relations

However, that I understand from what you say that this is not the situation and that the primary key is only to be used by the engine itselfe.

Uhm.. Kind of. The PK is significant to the engine in enforcement of the constraints of the relationship.. But you can, and should, use it too for locating specific rows to update..

I allso understand that relations on the server side is only for showing data.

Not sure what this means; relations on the server side are for preserving the integrity of data, given that the server doesnt really have any responsibility to show data; only to store and manage it?

 

[o-mheien@hdsoftware.no]

Well, I maybe wasnt making myself clear. Relations on the server side is of course a help for the server to preserve the integrity, but what I ment was that I cant use it in my program for other purposes than reports etc.

Anyway, I see you rpoint of using fields as elements of the PK, but I dont think that is a good idea. Its hard to create an unique index based on customer data. You wil end up in an autoinc because there is absolutly no guarantee that two persons in one location dont have same name, age, etc. etc.

But what I have discovered is that .NET has something called GUID. This really helped me alot because I then can move the generation of the GUID from the serverside to the client side like this:

me.CustomerTable.CustomerID = GUID.NewGUID().ToString

It really does the trick. The chance that two identical GUID are generated is extreemly rare. I beleive the GUID is created using some computer related info plus date and time plus a random number. This makes it pretty unique.

Regards

Ole

 

[cjard]

Relations on the server side is of course a help for the server to preserve the integrity, but what I ment was that I cant use it in my program for other purposes than reports etc.

True true

You wil end up in an autoinc because there is absolutly no guarantee that two persons in one location dont have same name, age, etc. etc.

I normally draw a line somewhere that is sensible: 2 people of the same name and datetime of birth, living in the same house, are the same person..

If I really want an autoinc, oracle keeps one for me (ROWID) and every row in every table in any database on this machine, has its own ROWID -> ultimate PK.. only used in extreme cases though.. Most the data we have is PK by elements of the data.. Saves keeping useless redundant indexes that cannot be searched with meaningful info..


I've actually forgotten the initial problem, but I'm glad GUIDs solve yours!