Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-10 : 00:45:55
|
| diver writes "Using SQL Server 2000 with Visual BasicI'm very frustrated... I've got a simple query that is using an Inner Join, nothing fancy. The network I am using is very slow, so I am doing everything in my power to make changes to a disconnected recordset and then send it back to the server for an update (just once, UpdateBatch).The problem is, that the table does not have a unique record, so once I disconnect, and then reconnect, I get that error message "Insufficient Base Table Information... etc", and the updating is not allowed.Just don't know how to get around this. Has anyone found a way to circumvent this? Or, is it possible to use the ROWID in SQL Server query to create some kind of unique ID? I don't know if that would work or not. Just looking for some other ideas... even if it is "outside the box".All help is appreciated!diver" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-10 : 07:14:40
|
| This problem will remain in one form or another as long as you don't have a primary key on the table(s) you're updating. Fix that first.There is no ROWID in SQL Server; ROWID is not a relational database concept, it's an Oracle vendor extension. You have to add a primary key to the data you have, or add a column that acts as a primary key. |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-06-10 : 22:40:22
|
| You can add an Identity column (an auto-increment column), as Primary key. But I must warn you, from my own experience with ADO, you very often cannot update a Joined recordset properly.Sarah Berger MCSD |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-06-11 : 03:26:44
|
| I don't get it. ADO is specifically designed to handle database traffic between the front and the back-end. My experience is, that the problems, that cause ADO not to work properly, are mainly the fault of the developer. In this case I would suggest that you lookup the "unique table" property and the "resync command" property. Check for the latest version of ADO (2.7) if you are using SQL2K.I would suggest to read programming ADO (MS PRESS), it's for VB6 but it works simmilar to ADO.NET. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-06-11 : 11:00:14
|
Microsoft's documentation is misleading. Unique Table property only works for deletions i.e. it will only delete rows from the unique table, but has no effect for additions or modifications. Resync Command in combination with Unique Table is mostly to ensure Foreign key integrity i.e. that child rows don't get updated before a parent row does and so become 'orphaned'. quote:
My experience is, that the problems, that cause ADO not to work properly, are mainly the fault of the developer.
Mostly you're right, but not necessarily. ADO does have its limitations, especially considering the fact that you're really dealing with different providers and backends, each supporting its ownshenanigans. Robvolk informed me that Unique Table only works as expected for Access, he thinks. Sometimes, to do something you want, ADO will make you jump through hoops and use workarounds. No use blaming the poor developer.Sarah Berger MCSD |
|
|
|
|
|
|
|
|
Disconnected Recordset Updates (JOIN Query)