| Think outside the box!
|
*************************************************
"B. Chernick" <BChern***@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?

"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" <BChern***@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?
>
>
>

| Think outside the box!
|
*************************************************
"B. Chernick" <BChern***@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" <BChern***@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?
>>
>>
>>

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in
message news:uqNO8AkGIHA.484@TK2MSFTNGP06.phx.gbl...
> 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" <BChern***@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?
>
>

> 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?

"Patrice" wrote:

> AFAIK you can actually do this automatically using :
> http://msdn2.microsoft.com/en-us/library/system.data.foreignkeyconstraint(VS.80).aspx
> and check the UpdateRule property in the code sample.
>
> That said I agree with Cowboy that a "true" primary key should never be
> updated (basically the idea is that if you can change the PK you can
> identify each record at a *particular* time but the record X you have now in
> your DB could be something else than the record X that was in your DB 3 days
> ago i.e. you can't identify records wihtout refering to when).
>
> Basically the idea is that I say now that this is the record X I don"t have
> to say that this is X . If this is a user controlled value you may want to
> create a unique index on this colmun and use your own application internal
> primary key...
>
> A valid scenario though could be to create client side temporary ids (such
> as negavtive value for counters) and update them in cascade when the update
> is commited in the DB. Anoher option would be to use a guid.
>
> --
> Patrice
> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le message
> de news: FA5ED528-EC3F-426B-81AA-D0F34F880***@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?
>
>
>

> Dear Patrice,
>
> Thanks for the reference.  I've managed to make my example work but I'm
> still a bit confused.
>
> I started with the assumption that the foreign key constraint already
> existed in the xsd.  I can see it declared in relations, in the XSD
> designer
> file (or so I thought).
>
> After some frustration trying to extract a existing foreign key
> constraint,
> I gave up and copied the code in the example, dropping the constraint
> before
> the database read (because the fk conflicts with table clears if it
> already
> exists) and recreating the constraint afterwords.  Now the grids do
> exactly
> what I want, redisplaying the new keys instantly and updating to the db
> correctly.
>
> So do I understand this correctly?  Whatever that 'relation' in the
> designer
> may be, it is not a foreign key constraint. Visual Studio XSDs do not
> automatically create foreign key constraints.  It always has to be done
> programmatically?
>
> Thanks.
>
> "Patrice" wrote:
>
>> AFAIK you can actually do this automatically using :
>> http://msdn2.microsoft.com/en-us/library/system.data.foreignkeyconstraint(VS.80).aspx
>> and check the UpdateRule property in the code sample.
>>
>> That said I agree with Cowboy that a "true" primary key should never be
>> updated (basically the idea is that if you can change the PK you can
>> identify each record at a *particular* time but the record X you have now
>> in
>> your DB could be something else than the record X that was in your DB 3
>> days
>> ago i.e. you can't identify records wihtout refering to when).
>>
>> Basically the idea is that I say now that this is the record X I don"t
>> have
>> to say that this is X . If this is a user controlled value you may want
>> to
>> create a unique index on this colmun and use your own application
>> internal
>> primary key...
>>
>> A valid scenario though could be to create client side temporary ids
>> (such
>> as negavtive value for counters) and update them in cascade when the
>> update
>> is commited in the DB. Anoher option would be to use a guid.
>>
>> --
>> Patrice
>> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le
>> message
>> de news: FA5ED528-EC3F-426B-81AA-D0F34F880***@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?
>>
>>
>>

"Patrice" wrote:

> Not sure. Your best bet would be likely to check the generated code.
>
> Instead of driopping/creating the constraint you can also just
> acativate/deactivate those constraints. Also if tables are cleared in the
> correct order iy shiuld likely work...
>
> --
> Patrice
>
>
> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le message
> de news: 301FC931-4B25-4BB1-81C7-92F16294C***@microsoft.com...
> > Dear Patrice,
> >
> > Thanks for the reference.  I've managed to make my example work but I'm
> > still a bit confused.
> >
> > I started with the assumption that the foreign key constraint already
> > existed in the xsd.  I can see it declared in relations, in the XSD
> > designer
> > file (or so I thought).
> >
> > After some frustration trying to extract a existing foreign key
> > constraint,
> > I gave up and copied the code in the example, dropping the constraint
> > before
> > the database read (because the fk conflicts with table clears if it
> > already
> > exists) and recreating the constraint afterwords.  Now the grids do
> > exactly
> > what I want, redisplaying the new keys instantly and updating to the db
> > correctly.
> >
> > So do I understand this correctly?  Whatever that 'relation' in the
> > designer
> > may be, it is not a foreign key constraint. Visual Studio XSDs do not
> > automatically create foreign key constraints.  It always has to be done
> > programmatically?
> >
> > Thanks.
> >
> > "Patrice" wrote:
> >
> >> AFAIK you can actually do this automatically using :
> >> http://msdn2.microsoft.com/en-us/library/system.data.foreignkeyconstraint(VS.80).aspx
> >> and check the UpdateRule property in the code sample.
> >>
> >> That said I agree with Cowboy that a "true" primary key should never be
> >> updated (basically the idea is that if you can change the PK you can
> >> identify each record at a *particular* time but the record X you have now
> >> in
> >> your DB could be something else than the record X that was in your DB 3
> >> days
> >> ago i.e. you can't identify records wihtout refering to when).
> >>
> >> Basically the idea is that I say now that this is the record X I don"t
> >> have
> >> to say that this is X . If this is a user controlled value you may want
> >> to
> >> create a unique index on this colmun and use your own application
> >> internal
> >> primary key...
> >>
> >> A valid scenario though could be to create client side temporary ids
> >> (such
> >> as negavtive value for counters) and update them in cascade when the
> >> update
> >> is commited in the DB. Anoher option would be to use a guid.
> >>
> >> --
> >> Patrice
> >> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le
> >> message
> >> de news: FA5ED528-EC3F-426B-81AA-D0F34F880***@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?
> >>
> >>
> >>
>
>
>

> That's just it.  So far, I cannot find any reference to a
> foreignkeyconstraint in the designer.vb file (if that's what you're
> referring
> to) so I'm guessing that Visual Studio is not automatically creating it,
> even
> though the xsd designer surface is displaying a relationship by that name.
> (The foreign key is stored as a 'system.data.datarelation', but as I
> mentioned earlier, I haven't been able to find anyway to use that as a
> foreignkeyconstraint.)
>
> Could this have something to do with using SQL Server 2000 rather than
> 2005?
>
> "Patrice" wrote:
>
>> Not sure. Your best bet would be likely to check the generated code.
>>
>> Instead of driopping/creating the constraint you can also just
>> acativate/deactivate those constraints. Also if tables are cleared in the
>> correct order iy shiuld likely work...
>>
>> --
>> Patrice
>>
>>
>> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le
>> message
>> de news: 301FC931-4B25-4BB1-81C7-92F16294C***@microsoft.com...
>> > Dear Patrice,
>> >
>> > Thanks for the reference.  I've managed to make my example work but I'm
>> > still a bit confused.
>> >
>> > I started with the assumption that the foreign key constraint already
>> > existed in the xsd.  I can see it declared in relations, in the XSD
>> > designer
>> > file (or so I thought).
>> >
>> > After some frustration trying to extract a existing foreign key
>> > constraint,
>> > I gave up and copied the code in the example, dropping the constraint
>> > before
>> > the database read (because the fk conflicts with table clears if it
>> > already
>> > exists) and recreating the constraint afterwords.  Now the grids do
>> > exactly
>> > what I want, redisplaying the new keys instantly and updating to the db
>> > correctly.
>> >
>> > So do I understand this correctly?  Whatever that 'relation' in the
>> > designer
>> > may be, it is not a foreign key constraint. Visual Studio XSDs do not
>> > automatically create foreign key constraints.  It always has to be done
>> > programmatically?
>> >
>> > Thanks.
>> >
>> > "Patrice" wrote:
>> >
>> >> AFAIK you can actually do this automatically using :
>> >> http://msdn2.microsoft.com/en-us/library/system.data.foreignkeyconstraint(VS.80).aspx
>> >> and check the UpdateRule property in the code sample.
>> >>
>> >> That said I agree with Cowboy that a "true" primary key should never
>> >> be
>> >> updated (basically the idea is that if you can change the PK you can
>> >> identify each record at a *particular* time but the record X you have
>> >> now
>> >> in
>> >> your DB could be something else than the record X that was in your DB
>> >> 3
>> >> days
>> >> ago i.e. you can't identify records wihtout refering to when).
>> >>
>> >> Basically the idea is that I say now that this is the record X I don"t
>> >> have
>> >> to say that this is X . If this is a user controlled value you may
>> >> want
>> >> to
>> >> create a unique index on this colmun and use your own application
>> >> internal
>> >> primary key...
>> >>
>> >> A valid scenario though could be to create client side temporary ids
>> >> (such
>> >> as negavtive value for counters) and update them in cascade when the
>> >> update
>> >> is commited in the DB. Anoher option would be to use a guid.
>> >>
>> >> --
>> >> Patrice
>> >> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le
>> >> message
>> >> de news: FA5ED528-EC3F-426B-81AA-D0F34F880***@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?
>> >>
>> >>
>> >>
>>
>>
>>

"Patrice" wrote:

> In this relation you have a ChildKeyConstraint (that is a
> ForeignKeyConstraint) (as well as a ParentKeyConstraint that is a
> UniqueConstraint) that should expose UpdateRule, DeleteRule etc...
>
> You may want to rephrase though the exact problem you are trying to solve
> now as the introduction was quite general...
>
> Have you tried to change an id in table 1 ? (if I remember cascading is the
> default anyway).
>
> --
> Patrice
>
>
> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le message
> de news: 1A5AEAF2-BA1B-4839-A4AF-45C2D45F7***@microsoft.com...
> > That's just it.  So far, I cannot find any reference to a
> > foreignkeyconstraint in the designer.vb file (if that's what you're
> > referring
> > to) so I'm guessing that Visual Studio is not automatically creating it,
> > even
> > though the xsd designer surface is displaying a relationship by that name.
> > (The foreign key is stored as a 'system.data.datarelation', but as I
> > mentioned earlier, I haven't been able to find anyway to use that as a
> > foreignkeyconstraint.)
> >
> > Could this have something to do with using SQL Server 2000 rather than
> > 2005?
> >
> > "Patrice" wrote:
> >
> >> Not sure. Your best bet would be likely to check the generated code.
> >>
> >> Instead of driopping/creating the constraint you can also just
> >> acativate/deactivate those constraints. Also if tables are cleared in the
> >> correct order iy shiuld likely work...
> >>
> >> --
> >> Patrice
> >>
> >>
> >> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le
> >> message
> >> de news: 301FC931-4B25-4BB1-81C7-92F16294C***@microsoft.com...
> >> > Dear Patrice,
> >> >
> >> > Thanks for the reference.  I've managed to make my example work but I'm
> >> > still a bit confused.
> >> >
> >> > I started with the assumption that the foreign key constraint already
> >> > existed in the xsd.  I can see it declared in relations, in the XSD
> >> > designer
> >> > file (or so I thought).
> >> >
> >> > After some frustration trying to extract a existing foreign key
> >> > constraint,
> >> > I gave up and copied the code in the example, dropping the constraint
> >> > before
> >> > the database read (because the fk conflicts with table clears if it
> >> > already
> >> > exists) and recreating the constraint afterwords.  Now the grids do
> >> > exactly
> >> > what I want, redisplaying the new keys instantly and updating to the db
> >> > correctly.
> >> >
> >> > So do I understand this correctly?  Whatever that 'relation' in the
> >> > designer
> >> > may be, it is not a foreign key constraint. Visual Studio XSDs do not
> >> > automatically create foreign key constraints.  It always has to be done
> >> > programmatically?
> >> >
> >> > Thanks.
> >> >
> >> > "Patrice" wrote:
> >> >
> >> >> AFAIK you can actually do this automatically using :
> >> >> http://msdn2.microsoft.com/en-us/library/system.data.foreignkeyconstraint(VS.80).aspx
> >> >> and check the UpdateRule property in the code sample.
> >> >>
> >> >> That said I agree with Cowboy that a "true" primary key should never
> >> >> be
> >> >> updated (basically the idea is that if you can change the PK you can
> >> >> identify each record at a *particular* time but the record X you have
> >> >> now
> >> >> in
> >> >> your DB could be something else than the record X that was in your DB
> >> >> 3
> >> >> days
> >> >> ago i.e. you can't identify records wihtout refering to when).
> >> >>
> >> >> Basically the idea is that I say now that this is the record X I don"t
> >> >> have
> >> >> to say that this is X . If this is a user controlled value you may
> >> >> want
> >> >> to
> >> >> create a unique index on this colmun and use your own application
> >> >> internal
> >> >> primary key...
> >> >>
> >> >> A valid scenario though could be to create client side temporary ids
> >> >> (such
> >> >> as negavtive value for counters) and update them in cascade when the
> >> >> update
> >> >> is commited in the DB. Anoher option would be to use a guid.
> >> >>
> >> >> --
> >> >> Patrice
> >> >> "B. Chernick" <BChern***@discussions.microsoft.com> a écrit dans le
> >> >> message
> >> >> de news: FA5ED528-EC3F-426B-81AA-D0F34F880***@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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>