Inserting records with master/detail relationship

[jartman]

I'm using a web form in MS Visual Studio 6.0 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.

On the SQL programming forum I got a suggestion to use a stored procedure to combine the two INSERT statements. Not sure if I can do that - I'm actually using ODBC to Paradox tables. Any hints?

 

[BobRodes]

I don't know the DataAdapter control, but I can say that I avoid using the update facilities of Microsoft's controls like the plague. My general approach is to capture the data from visual elements and use ADO objects to update the database, and refresh the visual elements, generally manually. This is conservative, but at least I'm spending my time debugging my own work instead of wandering around the internet trying to figure out obscure anomalous behaviors in different controls.

So, you could probably just not use the DataAdapter to insert records at all. When the user hits the Upload button, just pull the data from the visual elements that the user has put them into, and use ADO to run the necessary SQL statements. Then, refresh controls as necessary to rebind to the new data. In this way, you can pretty much do what you want.

HTH

Bob

 

[jartman]

In this case the DataAdapter lives in a web service, so it doesn't have access to the visual elements. But I think I've done basically what you're suggesting. I let the DataAdapter.Update() do its thing, which inserts the record into the detail table, plus who-knows what else in the background to keep the DataAdapter object happy. Then I defined a new OdbcCommand to insert a record into the master table, and ran it with ExecuteNonQuery(). Seems to work.

I'm a complete novice at this stuff and I'm spending all of my time, as you say, wandering around the internet trying to figure out anomalous behaviors. I thought you couldn't do any of this without a DataAdapter (judging from the MS docs). Thanks for the hint.

- John

 

[johnwm]

I thought that DataAdaptor was a .NET thing - this is the VB5/6 forum!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[BobRodes]

I guess that is a .Net thing, isn't it? I'm only about 1000 pages into .net research, so I missed that entirely. Thought it was just another COM class that I don't use. Anyway, jartman, if you think that it is only complete novices who spend much of their development time wandering around on the internet trying to figure out anomalous behaviors, it's only because you're a complete novice.

Bob