UPDATING PROBLEM IN SQL TABLES

For any remote data you need primary keys (unique fields) in every record, that's true.

That you get a "connection busy" error when you delete records points out that you have record buffering. So deleting a record and leaving it causes an implicit tableupdate of the deleted record, it's also deleted in SQL Server.

I think you deleted the remote view records because your idea is to copy the remote view data into a table for editing and then copy the edited data back into the remote view by deleting everything and appending the edited data. Am I right with that guess? Well, this does not work at all this way.

With views you work by editing them and pushing edits back by tableupdate. Most often you'll use table buffering on the view to have full control about the moment of saving (or committing) the data by a tableupdate.

Chriss

hi Chris
Yes you are right. I do it in old way because my user maybe need to save and maybe do not save his work. Do you think I must work on view and all edit on it. but if user want to cancel what i must do ?
also I dont a uniqe field. can i use a field with time stamp instead ?
unfortunately i am absent in my work place for two days and can not see your answer. but thank you very much.

Hello Farzad,

farzad321 said:

if user want to cancel what i must do ?

Click to expand...

Well, that's easy, either not do anything at all or TABLEREVERT. Done on all rows this puts back the view to what it was and enables requery.

farzad321 said:

can i use a field with time stamp instead?

Click to expand...

I think you mean rowversion (No, that won't work. And if you mean a datetime, it's not guaranteed to be unique.

So to begin working with remote data you need to introduce actual primary key fields. It does not pay to work with any provisional fields just very likely to be unique, you'll just regret it.

Chriss

Hello,

On connection busy this may help : You can set the view to fetch all records , see view - extended options - check both "all"
60s seems to be long, do you have an index in SQL on sales number ?

Regards
tom

Thank you to all friends, Now i thik understand how do edit records. First open a remote cursor and make a table buffering . then user make some edit, delete and append. after that if user need to save i must do TABLEUPDATE() and everything's updated, and if no need to save I just close remote view and we have no any change in SQL ?
Just one question ,Realy i need to unique field for this exact updating in sql ? How is it going without this unique field?
[highlight #FCE94F]IF we need to use a unique field , how can make that[/highlight]? A serial number ? what is data type? binary or integer. what is the correct an best way.
please explain me.
sorry for Poor English.

First, in summary of all the following explanations, what you should do, is this:
You might be able to do that in one ALTER TABLE, I'm not sure you can specify a field you also create in the same alteration in the constraint part. But I'm sure it works in two steps.

Now you can specify GUID as the primary key field name in CURSORSETPROP.

Aside from that uniqueidentifier type, an automatically increasing integer also is used. An integer field with automatic sequence is an INT IDENTITY(1,1) field.

If you worked without primary keys so far you will also not have used actual relationships of tables with foreign keys, so that's another miss in your database conception. If you introduce that concept now it would be best you make yourself familiar with actual database concepts that are absolutely fundamental to designing a good database. Adding primary keys to all tables is just a starting point of enhancing your database design.

To clarify, that doesn't say anything about the business value of your data, only the technical level, but in general, you will have it much easier with any data processing if you know about database design principles under the main roof of the term "database normalization".

Just a little background knowledge you should have about the key types: If you decide for integers, the identity(1,1) clause will make it a readonly field. The values are 100% generated by the server, so any concurrency of adding records from different clients will be queued up by the database service process, executed sequentially and no records with same numbers are generated, as could be the case, if two clients compute MAX+1 based on the same current MAX value. Well, that's of course a nice aspect to save you from that harm, but it also means you can't use preliminary keys in your buffered views and that's important if you want to work on table hierarchies as simple as order and orderitems are already. The orderitems will point at their order via an orderid field and you can only know that value after TABELUPDATE of the order table. Worse than that, even after TABLEUPDATE your view record will not get the actual int id generated by storing the record and so to know what is the orderid you will need to requery or work with @@IDENTITY to know the key.

In short, it is easier to work with uniqueidentifier keys, as you also can write to such fields and generate keys on clients, too. With as simple code as:

And that's the reason I started with the code to add GUID field and key constraint.

Chriss

Thank you chriss,
I was insert a GUID into my sql table and make primary key on it.Now this field is full and until now every thing is okay.
but still when open a remote view with 50 records and make a change on some record, my records was doubled and not working properly ,

1- i have a unique field
2- make a primary key
3- rebuild remote view in my database and make updatable all fields

4-open remote view alias suny
5- = CURSORSETPROP('Buffering', 5, 'suny' )
6- make some edit ,delete,append
7- myresult = TABLEUPDATE(.T.)
i dont see any error but my records was doubled.!!
what is my mistake
please show me
with best regards
farzad

Point 6, what did you append? Just a few blank records?

At first, I would add the GUID to the updatable fields (click the check under the pencil symbol) and whenever you append a blank record, put in a GUID into the GUID field using the code I gave you earlier:

Also notice: Everything you append gets the status of being new records. If you append data from the table this effectively duplicates it, as TABLEUPDATE inserts appended records, it doesn't update them.

Chriss