correct way to update a table?

[c@lle]

Hello,
what is the correct way to update records in following table:
let's say for example that we have 'article 6' with products A, B, C en D.

That would gave me:
id article product order
...
9 art6 A 1
10 art6 B 2
11 art6 C 3
12 art6 D 4
...


Now I display this to the user and he can change the order and/or add, delete products that are linked to the article.

Now my question: what is the best way to update the table?
Can I first delete all the concerning records (thus all the lines with 'art6') and then add the new order and products?

Or should I write a function that checks for existing lines and change the order? In this way the autonumbering id is not growing so fast. I will have to delete the products I removed and add the ones I added.

So when the user selects now 'B,D,A,F,Z' how can I save this in my table?

Please any suggestions.
Thanks.

[Chris]

c@lle, may be you can use the UPDATE SQL statement, which enable to to SET a field value to to given new value with the given condition in WHERE clause.

[c@lle]

chris, I can loop thru the list that the user has created and than update my database. But what do I have to do with the product that are removed? Loop again and check it?

[Chris]

c@lle, I'm still can not understand abt the following data

9 art6 A 1
10 art6 B 2
11 art6 C 3
12 art6 D 4

perhaps, you can explan more abt it?

[c@lle]

well, that are some records in my table.
let's take the first line: 9 art6 A 1

9 = autonumbering ID
art6 = link to keyfield of other table
A = product name
1 = order

I show a form with information of article 6, so I get this data and show it in a listbox. Then the user can add and/or delete products and change the order.

After that this is done, I want to update the table. Now my question is : can I first delete all the concerning records (thus the consequence is that my ID will grow very fast, is this a problem??) by "delete from tbl where art='art6'"
Then I can loop thru the listbox and add the lines.

Is it good to always first delete the records and then add them, or should I first update the records that are already in the table, add the new ones and delete the removed ones?

[Chris]

Hey! c@lle, listbox may not be able to do that. Perhaps, you need to use Listview. For the sample project, you can download from here sample

Althernative, you can update the database on every change the user have made and need not to wait till the end of the update process than you only start performing the batch update. Which whill be advantage for Server-Client application.

[c@lle]

thanks but the link is not working (not allowed to link from outside the site)

[Ramu]

Hi,

The best solution is Delete the Existing records which matches article6 and then insert it once again.

Because, it is difficult to keep track. i.e.,

Assume that the user inserts one record, deletes one record and modifies one record.

In this case U have to add a flag to ListBox which contains A-Add M-Modified D-Deleted. While saving, based on the flag U have to execute an Insert Statement, Update Statement and Delete Statement. This is a tough Job.

If the Id of this table is referred in someother table, then U cannot delete and insert. If this table is independant, then U can verywell delete and insert

-- Ram

[c@lle]

Is it a problem if I constantly delete and add record for the ID?

What happens with the autonumbering ID in "sql server 7.0" ? If it has reached a maximum, will it select empty record ID's or what?

[Chris]

C@lle, i juz email the sample project to your email.

Originally posted by c@lle
thanks but the link is not working (not allowed to link from outside the site)