0

I want to edit a specific record in an access database but I keep on getting errors

this is the database I want to edit: Access database

these are flashcards that the user has created and stored in an access database. What I want is that the user is able to edit the difficulty so it appears more/less often

This is the module:

Module Module1
Public Function runSQL(ByVal query As String) As DataTable
    Dim connection As New OleDb.OleDbConnection("provider=microsoft.ACE.OLEDB.12.0;Data Source=flashcard login.accdb") 'Establishes connection to database
    Dim dt As New DataTable 'Stores database in table called dt
    Dim dataadapter As OleDb.OleDbDataAdapter

    connection.Open() 'Opens connection
    dataadapter = New OleDb.OleDbDataAdapter(query, connection)
    dt.Clear() 'Clears datatable
    dataadapter.Fill(dt) 'Fills datatable
    connection.Close()
    Return dt
End Function
End Module

And here is the button that the user can press to edit the database:

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

The difficulty column in the database should be able to be edited by the user through the value they entered in txtDifficulty.text

Idk
  • 27
  • 5
  • What are the errors and why is there a single apostrophe in the UPDATE statement? – Karen Payne Nov 06 '21 at 17:35
  • Its says its a syntax error and I'm probably wrong but isn't that the way you edit a database? – Idk Nov 06 '21 at 17:58
  • What would be an easier way to edit a record inside a database? – Idk Nov 06 '21 at 17:59
  • The syntax error appears to be the single apostrophe, remove the apostrophe. Also, did you intend to set difficulty for all row? If not you need a WHERE condition. – Karen Payne Nov 06 '21 at 18:26
  • Ah yeah removing the apostrophe removes the syntax problem so thank you! But it changes the whole column to that difficulty. I'm guessing that I am going to need a where statement but the problem I have is that the user can create as much flashcards as they want so how would I write the where statement? – Idk Nov 06 '21 at 18:56

1 Answers1

0

Good to hear I found the problem with the apostrophe.

I am going to need a where statement but the problem I have is that the user can create as much flashcards as they want so how would I write the where statement?

An INSERT statement does not have a WHERE clause but an UPDATE does and is usually by a primary key.

Look at how I add a new record ignoring mHasException and specifically using parameters. In this case a List is used but with little effort an array of DataRow can be passed instead.

Here is an example for updating a record with a DataRow.

To get other code samples for ms-access see the following repository.

In closing, in the above repository I don't get into all possibilities yet there should be enough there to get you going. And when reviewing code I flip between Add for adding a parameter and AddWithValue while Add is the recommend way but both are shown to see differences. see also Add vs AddWithValue.

Karen Payne
  • 4,341
  • 2
  • 14
  • 31
  • Thank you for responding, When updating a record I received no error but the database never actually updated. – Idk Nov 07 '21 at 14:42
  • If the database is shown as a item in Solution Explorer then check `Copy to Output Directory` which by default is copy always, change to copy if newer then see if you see updates. See [this article](https://social.technet.microsoft.com/wiki/contents/articles/53248.visual-studio-copying-files-to-debug-or-release-folder.aspx) – Karen Payne Nov 07 '21 at 15:51