Cowboy
10/29/2007 9:24:00 PM
Politics often force decisions that are, otherwise, not a good idea. I have
been there.
You will still have to sync with the database as quickly as possible to make
sure things are up to snuff, but add, link, delete may solve the main
problem.
--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA
*************************************************
| Think outside the box!
|
*************************************************
"B. Chernick" <BChernick@discussions.microsoft.com> wrote in message
news:BD86A105-31AB-4DEF-BCD2-F2985563986B@microsoft.com...
> First of all, as to 'Why?'. Unfortunately the answer is politics and
> legacy
> data structure. Indeed I may have coded myself into a corner but this is
> what the design seems to call for. (Also I'm a new employee here and I
> really don't want to tick off management by telling them it's impossible
> or
> insane.)
>
> In the actual application I have 2 DataGridViews on one screen. In the
> first there is a combobox that is part of the primary key of the first
> table.
> The second grid displays records from a table whose primary key is mostly
> made up of key fields from the first table. Using a bindingsource, the
> second grid only displays records related to the currently selected row in
> the first grid. So once I change the value of the combobox in the first
> grid, any existing child records in the second grid are inaccessable until
> a
> database update is performed.
>
> (To make matters worse the combobox in the first grid is populated from
> another table in which it is the primary key. This record has a boolean
> field, which if true, means that the record in the first grid cannot have
> child records in the second grid, hence I have to code some sort of delete
> routine.)
>
>> The concept you are talking about here can be done in ADO.NET, but you
>> are
>> going to have to copy information to a new record, alter the foreign key
>> and
>> then delete the first. This is also what happens in SQL Server, although
>> you
>> do not see it. SQL Server works the cascade without your knowledge. :-)
>
> On the other hand I did not thing about deleting and replacing records in
> the second grid on combobox change. I will give that a try. Thanks.
>
>
> "Cowboy (Gregory A. Beamer)" wrote:
>
>> The concept you are talking about here can be done in ADO.NET, but you
>> are
>> going to have to copy information to a new record, alter the foreign key
>> and
>> then delete the first. This is also what happens in SQL Server, although
>> you
>> do not see it. SQL Server works the cascade without your knowledge. :-)
>>
>> Now, to a more important question: Why?
>>
>> Primary keys are immutable in most properly designed systtems. Once a
>> primary key is assigned, it should not be altered. If the primary key
>> also
>> has human value that has to be altered, then you should add another key
>> and
>> allow the human alterable bits to be altered at their whim. In every case
>> I
>> can think of, alteration of primary keys was an indication a fundamental
>> application/business flaw. Please note that I am accepting that your
>> application may require primary key changes, but I cannot currently think
>> of
>> a reason to do this.
>>
>> --
>> Gregory A. Beamer
>> MVP, MCP: +I, SE, SD, DBA
>>
>> *************************************************
>> | Think outside the box!
>> |
>> *************************************************
>> "B. Chernick" <BChernick@discussions.microsoft.com> wrote in message
>> news:FA5ED528-EC3F-426B-81AA-D0F34F880D32@microsoft.com...
>> > Ok, bear with me. This is a bit lengthy. This roughly explains a
>> > real-life
>> > legacy data structure problem I'm having.
>> >
>> > Assume a SQL Server 2000 database with 2 tables, Table1 and Table2.
>> >
>> > Table1 has fields id1 (Integer, primary key, but NOT an identity field)
>> > and
>> > content (varchar 10).
>> >
>> > Table2 has fields id1 (integer), id2 (integer, identity field) and
>> > content
>> > (varchar 10). The primary key of Table2 is id1/id2.
>> >
>> > The 2 tables are joined by a relationship in which table1/id1 is the
>> > foreign
>> > key of table2/id1. Also the options are set to cascade updates and
>> > deletes.
>> >
>> > Now I go into SQL Server Enterprise Manager and:
>> > 1. Create a Table1 record with an id1 = 1
>> > 2. Create a Table2 record with an id1 = 1
>> > 3. I then go back into Table1 and change id1 to a value of 2. The
>> > change
>> > is
>> > immediately reflected in the Table2 record. That's exactly what I
>> > expected
>> > and wanted.
>> >
>> > Now the big question - can I get ADO to do the same thing in memory?
>> >
>> > Now I create a Winforms app in VB/Dot Net 2.0. I add a dataset (xsd)
>> > to
>> > the
>> > project and drag tables 1 and 2 onto the designer simultaneously so
>> > that
>> > the
>> > relationship comes too. I then create a minimal class inheriting from
>> > the
>> > dataset so I can fill Tables 1 and 2 from the server. I add a pair of
>> > DataGridViews to the form to display Tables 1 and 2.
>> >
>> > If I update Table1, id1, update to the database and reload the data,
>> > the
>> > changes are reflected in Table2 as expected.
>> >
>> > What I cannot figure out is how to immediately show the changes in the
>> > Table2 grid without an update. In other words is there a way to make
>> > the
>> > system do this?
>> >
>> > Am I looking for something that does not exist?
>>
>>
>>