Dataset Multi-Table Row changes

microsoft.public.dotnet.framework.adonet

8 Nov 2005 2:53 AM

Michael Jenck

Hi All,

After quite a bit of searching through the newsgroups I have been able
to fill a dataset with two tables using two separate datadapters.

What I need to do now is to update the records in table1 based on the
records in table2.

A simplifed example

Table1                  ' When loaded Level is Hi All,

After quite a bit of searching through the newsgroups I have been able
to fill a dataset with two tables using two seperate datadapters.

What I need to do now is to update the records in table1 based on the
records in table2.

A simplifed example

Table1                  ' When loaded Level is NULL
GUID|LastName|Score|TestDate |Level
0000|TEST1     |    7 | 5/6/05 | Hi All,

After quite a bit of searching through the newsgroups I have been able
to fill a dataset with two tables using two seperate datadapters.

What I need to do now is to update the records in table1 based on the
records in table2.

A simplifed example

Table1
GUID|LastName|Score|TestDate | Level    |
0000|TEST1     |    7 | 5/6/05 | NULL   |
0001|TEST2     |   21 | 5/6/05 | NULL   |
0002|TEST3     |   15 | 5/6/05 | NULL   |
etc.

Table 2
GUID|StartDate|EndDate|MinScore|MaxScore|Level      |
A000|1/1/00    | NULL    |    0       |   10       | Level 1 |
A001|1/1/00    | NULL    |   11      | 23        | Level 2 |
A002|1/1/00    | NULL    |   24      | 39        | Level 3 |
etc.
NOTE: Enddate can be NULL all other are NOT NULL
           A NULL enddate means to use today as the enddate

The results I need to acheive would be:

So Table 1 would look like
GUID|LastName|Score|TestDate | Level    |
0000|TEST1     |    7 | 5/6/05 | Level 1 |
0001|TEST2     |   21 | 5/6/05 | Level 2 |
0002|TEST3     |   15 | 5/6/05 | Level 2 |
etc.

My questions:
1) Is this possible through teh dataset by an SQL query?
(If I need to loop record at a time, this would take an extremely long
time as there are 8 different score to look up per record.)

2) Would I be better creating a stored procedure on the SQL server to
acomplish this.
(The goal was to offload to the client as much as processing as
possible)

Thanks in Advance,

Mike