Updating a Table

10 Apr 2006 3:41 AM

Roshawn Dawson

Hi,

I have three datatables, the last of which represents a many-to-many relationship. Here's how it looks:

Table #1
attID (autoincrement)
Attribute
Name
ProdNumber

Table #2
valID (autoincrement)
Text
Number
Department

Table #3
attID
valID

Here's the scenario. I have an empty database that I'm using. I'm retrieving data from a web
service and placing it in my datatables. I can easily place the desired data into the first two
tables, but not the third one. For that matter, I wouldn't know how to save the data for the third
table.

I'm certain that some ADO.NET guru out there can help me as you've helped many others. Anyone have
any ideas?

Thanks,
Roshawn

10 Apr 2006 4:58 AM

OHM ( One Handed Man )

The design here is flawed. Table3 serves no purpose. If however this was a
DataRelation then it would provide a constraint where entries in the many
side may not exist without an entry in the one side.

I think this is what you are looking for. Furthermore, the DataRelation does
not need to be saved as it acts as your referential intrgrity cop inside the
DataSet

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net

Show quote

"Roshawn Dawson" <udr***@bellsouth.net> wrote in message
news:unSAZCFXGHA.3660@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I have three datatables, the last of which represents a many-to-many
> relationship. Here's how it looks:
>
> Table #1
> attID (autoincrement)
> Attribute
> Name
> ProdNumber
>
> Table #2
> valID (autoincrement)
> Text
> Number
> Department
>
> Table #3
> attID
> valID
>
>
> Here's the scenario. I have an empty database that I'm using. I'm
> retrieving data from a web service and placing it in my datatables. I can
> easily place the desired data into the first two tables, but not the third
> one. For that matter, I wouldn't know how to save the data for the third
> table.
>
> I'm certain that some ADO.NET guru out there can help me as you've helped
> many others. Anyone have any ideas?
>
> Thanks,
> Roshawn

10 Apr 2006 9:04 AM

Aziz

Are you trying t get the automatically created value for attID and
valID?

If so, David Sceppa has some code that works to return the values for
autogenerated PKs. Search for his name here.

10 Apr 2006 12:02 PM

Otis Mukinfus

On Sun, 09 Apr 2006 22:41:01 -0500, Roshawn Dawson <udr***@bellsouth.net> wrote:

Show quote

>Hi,
>
>I have three datatables, the last of which represents a many-to-many relationship. Here's how it looks:
>
>Table #1
>attID (autoincrement)
>Attribute
>Name
>ProdNumber
>
>Table #2
>valID (autoincrement)
>Text
>Number
>Department
>
>Table #3
>attID
>valID
>
>
>Here's the scenario. I have an empty database that I'm using. I'm retrieving data from a web
>service and placing it in my datatables. I can easily place the desired data into the first two
>tables, but not the third one. For that matter, I wouldn't know how to save the data for the third
>table.
>
>I'm certain that some ADO.NET guru out there can help me as you've helped many others. Anyone have
>any ideas?
>
>Thanks,
>Roshawn

I believe your third table is a many to many link table. Is that correct? If
so you may have to write code to do the inserts. I don't think the wizards are
smart enough to do that.

I'm on the way to work now, but will work up some code for you this evening, if
some one else hasn't already done that.

Basically, to will need to do it like this:

1. Insert to Table1 and get the primary key (Row id?) for the row.
2. Insert to Table2 and get the primary key (Row id?) for the row.
3. Insert the two primary keys (Row ids?) into the third table.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

10 Apr 2006 5:17 PM

Roshawn Dawson

OHM: great advice

Aziz: appreciate the resource

Otis: it appears that you have just what I'm looking for. Table #3 definitely represents a
many-to-many relationship. The difficulty is inserting the correct data into the table itself
(inserting into the other two tables are simple enough).

Sadly, I haven't found any code using ADO.NET to handle this scenario. However, I am attempting to
restructure the tables to see if I can omit the need for a many-to-many relationship. Might not
prove fruitful, but I'll try anyway.

Thanks,
Roshawn

10 Apr 2006 5:26 PM

OHM ( One Handed Man )

You know what, I misread your post. I thought you were trying to add a
constraint. If you are using table3 threre should be no problem. provided
that the two keys are a composite PK then it should not be any issue at all.

--
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net

Show quote

"Roshawn Dawson" <udr***@bellsouth.net> wrote in message
news:On1U1KMXGHA.3656@TK2MSFTNGP05.phx.gbl...
> OHM: great advice
>
> Aziz: appreciate the resource
>
> Otis: it appears that you have just what I'm looking for. Table #3
> definitely represents a many-to-many relationship. The difficulty is
> inserting the correct data into the table itself (inserting into the other
> two tables are simple enough).
>
> Sadly, I haven't found any code using ADO.NET to handle this scenario.
> However, I am attempting to restructure the tables to see if I can omit
> the need for a many-to-many relationship. Might not prove fruitful, but
> I'll try anyway.
>
> Thanks,
> Roshawn