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

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

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" <BChernick@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message
de news: 1A5AEAF2-BA1B-4839-A4AF-45C2D45F7E3A@xxxxxxxxxxxxxxxx
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" <BChernick@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le
message
de news: 301FC931-4B25-4BB1-81C7-92F16294C865@xxxxxxxxxxxxxxxx
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" <BChernick@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le
message
de 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: How to create ForeignKeyConstraint?
    ... If the boss wants to change the price of most of the ... a foreign key constraint won't give exectly what you want. ... I may have a table A, that references ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How much of the functionality of SQL Server is mirrored in ADO
    ... though the xsd designer surface is displaying a relationship by that name. ... I started with the assumption that the foreign key constraint already ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: "ORA-00984: column not allowed here" Creating table
    ... foreign key reference to the table foo, ...   weird_id VARCHAR2, ...   FOREIGN KEYREFERENCES foo ...
    (comp.databases.oracle.server)
  • Re: How to create ForeignKeyConstraint?
    ... a foreign key constraint won't give exectly what you want. ... I may have a table A, that references ... For instance, table A could have the columns IDA, IDB (an FK ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How much of the functionality of SQL Server is mirrored in ADO
    ... The main problem as I saw it was that I had to display 2 tables in 2 grids ... though the xsd designer surface is displaying a relationship by that name. ... Instead of driopping/creating the constraint you can also just ... I started with the assumption that the foreign key constraint already ...
    (microsoft.public.dotnet.framework.adonet)