Undo an update in Access database

Question

0.00/5 (No votes)

See more:

Hi guys! My project is an installment payment program (VB.NET 2008) and I use MS Access as database. When the user enrolls a new customer, it will automatically generate amounts per term in the table based on how many terms is entered in the enrollment window. The code of this works fine.
Another form (Premiums Form) asks the user to input the premium paid by the customer, and the code of the save button looks something like this:

Private Sub btnPremSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPremSave.Click

  sqlQrySave = "UPDATE MyTable SET custnam = '" & cboCustNam.Text & "'," _
  & "premno = '" & txtPremNo.Text & "', premamt = & '" & txtPremAmt.Text & "'," _
  & "WHERE contno = " & txtContNo.Text & ""

  cmdSave = New OleDbCommand(sqlQrySave, connSave)
  cmdSave.ExecuteNonQuery()

End Sub

In Premiums Form I have an UNDO button to revert the changes in the table, in case of erroneous updates. The customer might pay higher than what is dealt, so in the "premamt" column where there were original values saved before clicking this SAVE button, I should be able the return the original values, and discard the recently saved. Is there an easy way to do this?

Thank you in advance for the help! :-D

Posted 23-Jan-11 4:41am

awmantonio

Updated 23-Jan-11 4:49am

Abhinav S

v2

Add a Solution

3 solutions

Solution 1

I assume this is for homework and that no-one in the real world will ever use this code ? It would still be good for you to learn to write safe code that is not open to injection attacks, either way.

Your database does not have undo built in. You have to either get rid of the button, or track your changes so you can run the SQL needed to undo the change. Remember that there is no guarentee that the objects changed in your query had the same value to start with.

Posted 23-Jan-11 4:44am

Christian Graus

Comments

awmantonio 23-Jan-11 10:58am

Thanks for the reminder but this is not for a homework, and besides if I know a better way to make a "safe" code using Access database other than this, I have done already. And about injection attacks? This program will be used in one computer only and will not be connected to internet nor network.

About the tracking of changes you are mentioning, that's the help I am seeking about here. So it would be a big help if someone can give their answers more specifically. Thanks for the reply anyways! :-)

Solution 3

Well, as Christian has said, you need some way to track changes. One way to do this is to have payments in a separate table, often called PaymentHistory or something similar. that way you can have as many 'undos' as there are payments.

Also as Christian stated you should try to use Parameterized Queries to avoid injection attacks.

Posted 23-Jan-11 5:04am

Henry Minute

Comments

awmantonio 23-Jan-11 11:11am

I see. I will try your suggestions, but I see the answer of Mr. Abhinav more appropriate in my project since I will not implement multiple UNDOs. Thanks for the brief & straightforward help Mr. Henry. :)

awmantonio 23-Jan-11 11:24am

Also thank you for mentioning about the Parameterized Queries, I checked it out and will use in my projects. Great help! :)

Henry Minute 23-Jan-11 11:25am

You're welcome :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2011-01-23T04:51:00

Solution 2

One way to do this would be store the default values in a another table, and in case the users revert back to the these values, fetch them from the table and then display them.

Posted 23-Jan-11 4:51am

Abhinav S

Comments

Christian Graus 23-Jan-11 10:53am

What if you make two changes to the same table and want to press undo twice ? You'd need a new DB for every change.

Abhinav S 23-Jan-11 11:09am

I guess the OP needs a sort of an audit table. Store the changed value everytime and if the user reverts back, revert back by one step. A lot of work though.

awmantonio 23-Jan-11 11:14am

I understand Mr. Abhinav, clearly stated. Thanks for your help! :)

Abhinav S 23-Jan-11 12:48pm

You are welcome.