Inserting records into master/detail tables

[jartman]

I'm using a web form in MS Visual Studio to view a list of specifications related to a particular part number. The specification files are in one table, and the mapping from part number to filename is in another. I filled a dataset with a JOIN in the SELECT statement and filtered the files to a particular part number using a DataView. So far so good.

But now I want to add an "upload" button which inserts a new record. I can get the filename into the specifications table but I can't figure out how to insert the PN/FILENAME mapping into the master table. I'm creating a new Row, adding it to the dataset, and calling the DataAdapter.Update function. The DataAdapter object only lets me put a single SQL INSERT statement into it (no compound statements).

Should I make a separate dataAdapter for the master table? Or is it just a matter of using some method built into the dataAdapter that I haven't found yet? I'm a bit handicapped because most of what's happening is behind the scenes in the Update method and I'm not sure exactly how it works.

 

[donutman]

As worded this isn't really a SQL question as much as it's a VB question. However, it should be, because it would really be better to call a SP that inserted both records for you. I believe that you can do that with .NET (that's what you're using right?) by writing your own version of the Insert SQL which would call a SP.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jartman]

Yep, .NET. Thanks for the hint. I don't know how to do what you're suggesting from within MSVS, but I'll pursue that over on the InterDev forum.

- John

 

[donutman]

John, if you need help with the SP just drop by again.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]