submitting updates when there's no primary key

  • Thread starter Thread starter Bernie Yaeger
  • Start date Start date
B

Bernie Yaeger

I cannot for the life of me figure out how to modify the backend when a
table has no primary key. I have used all of the examples in Sceppa's book
and they all fail - or, rather, update every row, even unmodified rows, when
a table (VFP tables) have no primary key (I'm using the most recent vfp
oledb data adapter). I cannot alter the tables for business reasons. Is
there are solution to this dilemma? I cannot offer a where clause that
makes sense - one table has 75 columns and all may be exactly the same.

Tx for any help.
 
Why in the world would you be working with tables that have no PK? The
solution is to add a PK to your table.
 
Hi Scott,

You didn't read the post - I cannot change the tables. They belong to a
different provider; I am just hooking into them to get/change data.

Bernie
 
Ok, but there must be some way to uniquely identify each row. Find the
column(s) that make the row unique and hard-code these into the UPDATE
statement WHERE clause.

UPDATE Fred SET x = 'y' WHERE ColThatMakesRowUnique = 16

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
It's likely they have a pk even if not explicitely defined by the (bad ?)
programmer. You'll have to find that "unpublished" pk.

Else you could have two identical rows and the application would be unable
to distinguish which is the one involved in the current operation...

Patrice
 
Hi Bill,

I was hoping to hear from you and David. Tx for responding.

These legacy tables have 50, 60, sometimes more columns each. There is no
way to set uniqueness without writing 60 parameter values in the update
commands twice each! And if I open a datatable with, say, 'select shipdt,
shiplist, boxtype, shipby from boxtable', then I don't even have all 60 cols
to work with (and I don't want to call all of them all the time).

But I have an idea - can I add a primary key to the datatable (which, after
all, is in memory(, such that I now have shipdt, shiplist, boxtype, shipby
and pkcol, and pkcol, having been added autoincrements the datatable so I
now have row uniqueness?

Thanks for your help.

Bernie
 
Hi Patrice,

Tx for your response.

These are legacy vfp tables - they have no primary keys. You're correct -
there can indeed be duplicate rows - nothing prevents this. Such is the way
of old dbase and dbase-like tables.

Bernie
 
Yes, you absolutely can, since you are in full control of the dataTable. My
point was that without a unique way to identify your fields (a PK), there is
no way know that you are dealing with the correct record.
 
Bernie,

Uniquely identifying the rows in the DataTable is not the issue. You
need to be able to send the changes back in an UPDATE query like the one
Bill described, where the FoxPro driver will update at most one row.

If you can't change the schema of the FoxPro tables, you can't use
disconnected optimistic updates with a technology like ADO.NET. You could
try to use server-side cursors with ADO "classic" and COM interop, but I
think you'd be much better off explaining the problem in an attempt to get
the provider to change the schema. A row version or last updated column
would help immesurably.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
Back
Top