Updating linked tables - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating linked tables

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2004-03-13 : 22:44:17

Consider this simple database which describes a many-to-many relationship between items and vendors (pardon the sloppy syntax!)

Table: Item
Columns: ItemID int identity pk, Item nvarchar(50)

Table: Vendor
Columns: VendorID int identity pk, Vendor nvarchar(50)

Table: ItemVendor
Columns: ItemVendorID int identity pk, ItemID int, VendorID int, ItemVendorCost smallmoney

In my VB.NET application, I have a form that allows the user to create vendors. I also have a form that allows the user to create items and associate vendors (and vendor prices) with these items. Needless to day, this is all being done using disconnected data.

Here is what’s bothering me: When I create the tables within my DataSet, I use FillSchema to get primary key and the identity information. When a new item is created, an ItemID is assigned to that item in the DataSet (“Item”). When I associate a vendor with the item, the value for ItemID in the DataSet (“Item”) is placed into the table ItemVendor in the DataSet.

When I go to write these data to the SQL database, there is nothing to guarantee that the ItemID’s generated in the DataSet are valid (i.e., some other user could have added an item, which could cause ItemID to be duplicated, which clearly cannot happen). Writing the item data is not the problem. The problem is this – how do I get the value of ItemID from the underlying database so that the value of ItemID stored in ItemVendor is correct? It seems that one needs to use some sort of RowUpdated event and an output parameter that returns the ItemID.

If you could please provide an example of such an application, I would be greatly appreciative.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-14 : 18:31:31

I would suggest you use a stored procedure to issue the INSERT statements for Item and Vendor. That stored procedure would then send an output parameter back to your VB code containing the value returned from SCOPE_IDENTITY(). It would look something like this:


CREATE PROCEDURE ItemInsert
  @ItemDescription nvarchar(50),
  @ItemID int OUTPUT
AS
  INSERT INTO Item (Item) VALUES (@ItemDescription)
  SET @ItemID = SCOPE_IDENTITY()
GO

By the way, I think it's a really bad idea to have a Table name the same as a column name (e.g. Item and Item)

Then in your VB code, use an ADO Command object to add Parameters, assign the values, execute, return the OUTPUT parameter and you've got the new Identity value that was assigned and available to be used in your next set of code, such as inserting that value into the ItemVendor table.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

groston
Yak Posting Veteran

61 Posts

Posted - 2004-03-14 : 18:39:11

AjarnMark

Thank you for replying.

Although I fully understand your suggestion (and you are absolutely correct), I was thinking in terms of inserting multiple records into Item. In this case, a single output parameter will not (???) solve the problem. This is why I thought that some type of RowUpdated event handler was needed.

Also, why do you "think it's a really bad idea to have a Table name the same as a column name"? (Not trying to start a flame war - just wanting to learn!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources