Re: How much of the functionality of SQL Server is mirrored in ADO.Net

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FA5ED528-EC3F-426B-81AA-D0F34F880D32@xxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Re: trying to use dual relationships and getting dupes
    ... the primary key in Table1 should be linked to a corresponding *foreign key* ... the primary key in Table2 should be linked to a corresponding *foreign key* ... add a subform control to "hold" subform1. ...
    (comp.databases.ms-access)
  • Re: How much of the functionality of SQL Server is mirrored in ADO.Net
    ... but I'm with Mr. Beamer. ... Hitchhiker's Guide to Visual Studio and SQL Server ... 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. ... changes are reflected in Table2 as expected. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: count in a query
    ... I used Distinct in table2 but time did not got better ... The fields in where clause are the primary key of Table1 and works as ... I see I have to suffer before upgrade to SQL Server ...
    (microsoft.public.access.queries)
  • Re: Newbie here, how does subquery work in sql server?
    ... SELECT * FROM table2 WHERE ... INNER JOIN Table1 ... primary key field and one that is a primary key of a second table ... And what what the stored procedure look like (sorry, ...
    (microsoft.public.sqlserver.programming)
  • Re: Can this be done?
    ... I think that you are confusing the Primary Key with the Foreign Key. ... for table2 (incidentally a Foreign Key is not necessarily part of the Primary ... > to create parent/child relationship. ...
    (microsoft.public.access.formscoding)