×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

UPDATING PROBLEM IN SQL TABLES

UPDATING PROBLEM IN SQL TABLES

UPDATING PROBLEM IN SQL TABLES

(OP)
Hi to every one
I want to change my local table of accounting application into SQL .
I make a Database and connection in it via odbc and also make a remote view for retrieve data from sql.
I can read any data from sql very fine and no problem.
But when I need to update some data, every thing confusing .
Example
We have one table with one million record in sql. we have sales number field that is not unique.
when i open a remote view for sales number = 100 , I get cursor with 60 records. after that I copy this data to temporary table for editing.
then user make a change and do some edit on it.
Now how can i update this cursor .
when i try to delete all record in remote view , I get error connection busy.
when i try to tableupdate() i get error
I make a remote view updatable with all field but I most miss somethings.
maybe I have not uniqe field or timestamp field. I dont know.
I need your help.
Thank you.

RE: 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

RE: UPDATING PROBLEM IN SQL TABLES

(OP)
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.

RE: UPDATING PROBLEM IN SQL TABLES

Hello Farzad,

Quote (farzad321)

if user want to cancel what i must do ?

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.

Quote (farzad321)

can i use a field with time stamp instead?

I think you mean rowversion (http://www.sqlines.com/sql-server/datatypes/timest....)
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

RE: UPDATING PROBLEM IN SQL TABLES

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

RE: UPDATING PROBLEM IN SQL TABLES

(OP)
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?
IF we need to use a unique field , how can make that? A serial number ? what is data type? binary or integer. what is the correct an best way.
please explain me.
sorry for Poor English.

RE: UPDATING PROBLEM IN SQL TABLES

First, in summary of all the following explanations, what you should do, is this:

CODE

ALTER yourtable ADD COLUMN GUID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID();
ALTER TABLE yourtable ADD CONSTRAINT PK_yourtable PRIMARY KEY NONCLUSTERED (GUID); 
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:

CODE

oGUID = CreateObject("scriptlet.typelib")
cGUID = substr( oGUID.GUID, 2, 36 ) 

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

Chriss

RE: UPDATING PROBLEM IN SQL TABLES

(OP)
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

RE: UPDATING PROBLEM IN SQL TABLES

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:

CODE

oGUID = CreateObject("scriptlet.typelib")
cGUID = substr( oGUID.GUID, 2, 36 ) 

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close