Delete and Insert instead of Update records in ASP

[longmatch]

I have a form used to save multiple records instead of one when it is submitted. I have problems when updating my records using this form. because the record number may be different after updating. I need to insert more records or delete records if the record no is different. I tried to delete all the records, and insert the new records when update button is clicked. Unfortunately it does not work for some reason. Not sure how to solve this problem. I am hoping experts in this forum can give me the answer.

Thanks

Longmatch

 

[rac2]

You need to be clear in your design whether you want to update existing records or add and delete records.

When you say the record number changes, it makes me wonder whether you are updating the key column. That is a mistake if you need to handle the same row again.

Post more information about your database tables and how you are coding the update process.

 

[longmatch]

Dear rac2:

My database's structure is
ID; PK
StudentID
Grade (first, second or third year)
rotationNO
rotationname
rotationLength

Different students may have different number of rotations (or records in database), which depend upon the Rotationlength. some have 15 rotations, others may have only 10. I saved the data in each row into a separate record. When I update the rotation data, the number of rotation will probably change. To my knowledge, we can only modify the data in the database using Update. We can not add or delete records using it. Do you have good solution for this?

Thanks

Longmatch

 

[Veep]

Rotation data should be stored in a seperate table. Think about the classic Orders and OrderDetails. You can have one order (student) and many details (Rotation data). You only need to store the Rotation Table foreign key in the Student table.

 

[longmatch]

Actually I have a student table, and rotation table as well.
The student table has all the information about the resident such as name, id number, grade. the rotation table has the rotation name and their duration. I need to store all the rotation information for three years for a given students. In my current table, studentID, Grade, and rotation number are the composite PK. Not sure how to design the tables to make it more reasonable and efficient.

thanks

Longmatch

 

[rac2]

Could you expand on what happens that leads you to say, "Unfortunately it does not work for some reason". Is there an error message?

You are the programmer; are you working with a database administrator who controls the database?

Are you using ADODB? Are you using the Command object with SQL statements to INSERT and DELETE; or are you using the Recordset methods?

Are you using SQL Server, Access, or another RDBMS?

Does your application involve a current view and a history view? If you are keeping a history then adding records and updating old records with a mark that indicates they are no longer current would be the way to handle changes.

Myself, in some situations, I will delete all existing records and insert new ones to reflect the effects of a change. For example, a brand may have a number of features, some brands have more features, some have fewer. I keep track only of the features the brand has. Changes involve inserting rows for new features and dropping rows for discontinued features.

Another pattern is to have a fixed set of records for everyone, even if some of them are not used by an individual. For example, in one application I have a set of services that may or may not be purchased by a particular customer. All customers have the same set of rows for the whole set of services. Each row shows whether the customer receives that service. Such a structure can be changed by updating.

In low volume conditions, either approach will work just fine.