Delete Data from Access DB after use

[juniorsilver]

I was wondering if there is a code i could add to my applicatio that will delete a data entry from a database. I'm designing a voting application as my project and i want a situation where an access code is good for only one entry. i put my code for retrieving the code below.
Is there anything that i can add to the code that will delete the entry that was used to login immediately after authentication?

Private Sub btnCodeEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCodeEnter.Click
        Dim connect As String
        Dim query As String
        Dim dbProvider As String
        Dim dbsource As String

        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        dbsource = "Data Source =|DataDirectory|\Codes.accdb"

        connect = dbProvider & dbsource
        query = "Select Count(*) From tblCodes Where Codes = ?"
        Dim result As Integer = 0
        Using conn As New OleDb.OleDbConnection
            conn.ConnectionString = connect
            conn.Open()
            Using cmd As New OleDb.OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("", txtCode.Text)
                result = DirectCast(cmd.ExecuteScalar(), Integer)
            End Using
        End Using
        If result > 0 Then
            frmVotingPlatform2.Show()
            Me.Hide()
        ElseIf txtCode.Text = PlatformPassword Then
            frmAdminCntrl.Show()
            Me.Hide()
        Else
            MsgBox("Invalid code. Please enter correct code", vbExclamation)
            txtCode.Focus()
        End If
    End Sub

[hericles]

Create another command object that deletes the code from the table immediately after the insertion code is called. But make sure you wrap your insertion and deletion in a transaction to ensure if something goes wrong with the insertion the code remains valid for the user to try again, instead of getting deleted regardless of what happens to the insertion.

[adam_k]

I agree with hericles, you have to handle the situation where something goes wrong with the insert.
What is your database? If it supports stored procedures, I'd create one, feed the user name and the vote and have it handle the vote. This way even if the connection from the client to the db breaks both commands will either run or not. Also in the sproc add the same count as in your form, to make sure that between logging in and voting there was not a second login (which by now has bypassed your form's check).

[adam_k]

I've been browsing for any new interesting threads and read the tittle.
Access won't support sprocs, my bad.

[juniorsilver]

I've been browsing for any new interesting threads and read the tittle. Access won't support sprocs, my bad.

What database do you think will be best then? And how do i know which row to delete from the database...i'm a bit new to programming, and that code i used is not original to me, i used an already made code and tailored it to my purpose. Please any help will be really appreciated

[hericles]

You don't need to use procs of course, but they are a tidy way to deal with problems like this. If you do want to use a database that does have procs you will need to use MSSQL or MYSQL, they'revery common and MYSQL is free!

Where you have "if result > 0" means a code has been found and the user is being redirected. This is where you want to remove the code as it has been successfully used. using procs or not, you will need to rework the code a bit to get it flowing smoothly. Slapping in your deletion code at that point I mentioned above wouldn't be very neat if using procs or transactions.

Hope that helps,

[adam_k]

May I suggest you don't delete at all? I don't know your schema, but in my mind this needs 2 tables: users and votes. if you delete users you're loosing traceability (you can't trace who voted when and from where). But if you add a table - a log - you have the traceability and it is easy to block only the ones that did vote by verifying the username and that there is no record in the log table.
Also this way you can add a verification in your insert so that your security can't be bypassed (mulitple logins without voting) while you are not blocking the user from voting if they logged in by didn't click submit.

[juniorsilver]

May I suggest you don't delete at all? I don't know your schema, but in my mind this needs 2 tables: users and votes. if you delete users you're loosing traceability (you can't trace who voted when and from where). But if you add a table - a log - you have the traceability and it is easy to block only the ones that did vote by verifying the username and that there is no record in the log table. Also this way you can add a verification in your insert so that your security can't be bypassed (mulitple logins without voting) while you are not blocking the user from voting if they logged in by didn't click submit.

in this programme there is no user...just the access codes. The candidates are not supposed to know who voted for who so a voter is given an access code in the voters authentication stand, then he goes to the station (which is a computer) to cast vote.
The deleting will be another safe guard against multiple votes

[adam_k]

It's a no-brainer that you shouldn't log the actual vote, but that this "access code" has been used / when and from where (IP or computer name) if more than 1 stations are being used.
This saves the authentication stand from frustration when a voter goes back to them claiming that the access code they gave him doesn't work (when in fact he/she just used it) and the following delema - now what? Give them a form to see if this access code has been used and when. If it has been used - in the timeframe from issuance to error claim - then he has voted and can kiss a second vote goodbye.

Don't just think about the program's flow and error conditions. Think about the processes and it's flow and error handling.

[juniorsilver]

It's a no-brainer that you shouldn't log the actual vote, but that this "access code" has been used / when and from where (IP or computer name) if more than 1 stations are being used. This saves the authentication stand from frustration when a voter goes back to them claiming that the access code they gave him doesn't work (when in fact he/she just used it) and the following delema - now what? Give them a form to see if this access code has been used and when. If it has been used - in the timeframe from issuance to error claim - then he has voted and can kiss a second vote goodbye.

Don't just think about the program's flow and error conditions. Think about the processes and it's flow and error handling.

Thanks you have one hell of a point..logging the times a code was used...any hints on how to do will help. I have no idea how to go about it.

[adam_k]

You can either add a second step after you've confirmed it's a valid code to see if it exists in your log table - instead of the step to delete this code - or you can retrieve 2 fields with a join between your access codes and your log tables (this can be done in a combined field if you want to continue using the ExecuteScalar by concatenating the 2 results into 1 field and then split it in VB).