How to edit a specific entity inside an access database-CodePudding

I made an access database which stores all the flashcards that the user has created.
Here it is:

Access database

When the user creates a flashcard, the difficulty is set to 3 and the user is able to change the difficulty through txtDifficulty

 Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
    Dim sql As String
    sql = "UPDATE flashcards set difficulty = " & TxtDifficulty.Text 'Changes difficulty to user input       
    runSQL(sql) 'runs SQL
End Sub

The problem with the above code is that it edits every single flashcard inside the database rather than just the individual flashcard.

Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
    Dim sql As String
    sql = "UPDATE flashcards set difficulty = " & TxtDifficulty.Text 'Changes difficulty to user input
    sql = sql & "WHERE Front = " & txtFront.Text
    runSQL(sql) 'runs SQL
End Sub

I edited the code to the above which I thought that it will only edit where Front in the database is equal to txtfront.text which is the textbox where the flashcard appears to the user but this just produces an error.

I was wondering how to only edit the specific flashcard that the user is on. Im guessing it might be something to do with the unique identifier in the access database but im not sure how to implement it

CodePudding user response：

Your idea to "only edit where Front in the database is equal to txtfront.text" is a good one, but concatenating values into an SQL query is fraught with potential problems.

If you pass the values to the query as SQL parameters then you don't need to worry about putting quotes around values, which is one of the problems for the code in the question.

You can write the SQL query over more than one line to make it easier to read: extra whitespace in the query is OK, but no whitespace (another of the problems for the code in the question) is a problem, usually a syntax error.

If you keep the database operation in a separate method, it keeps each part of the code in its own method, which makes it easier to be sure that each part is doing what it should and also easier to maintain.

Sub UpdateDifficultyLevel(front As String, difficulty As Integer)

    Dim sql = "UPDATE flashcards SET difficulty = ?
                WHERE Front = ?"

    Using conn As New OleDbConnection("yourConnectionStringHere"),
           cmd As New OleDbCommand(sql, conn)

        cmd.Parameters.Add("@difficulty", OleDbType.Integer).Value = difficulty
        cmd.Parameters.Add("@front", OleDbType.VarWChar).Value = front

        conn.Open()
        cmd.ExecuteNonQuery()

    End Using

End Sub

Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click

    Dim difficulty As Integer

    If Integer.TryParse(TxtDifficulty.Text, difficulty) Then
        Dim front = txtFront.Text
        UpdateDifficultyLevel(front, difficulty)
    Else
        ' tell user that the difficulty must be a number
    End If

End Sub

The parameters are represented by question marks in the query, and you have to add the parameters in the same order as they are used. Giving a name to each parameter ("@front", etc.) is just to make it easier to see which one is which (the name would actually be used if another database, such as SQL Server, was used).

The Using statement makes sure that any "unmanaged resources" are released after they've been used.

You might also need to use the UserID in the update query, but I could not gauge that from the question.