I am having a problem updating an access database with new information from an updated dataset. the database consists of 4 columns: User_ID, Login_Name, Password, Access. The following code makes a connection to the database, fills a dataset with the data from the database, reads a txt file with user name and passwords, and should update the dataset and then the database. However, I keep getting an Syntax error on the INSERT INTO statement on the datest.Update(dsUser,"User") line. I have found multiple posts on here showing the correct syntax of how to update a database, but I can't seem to get it to work. Any help would be appreciated.

Public constringtest As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\test.mdb"
    Public contest As New OleDb.OleDbConnection
    Public datest As OleDb.OleDbDataAdapter
contest.ConnectionString = constringtest

        strSQL = "SELECT * FROM tbl_User"
        datest = New OleDb.OleDbDataAdapter(strSQL, contest)
        datest.Fill(dsUser, "User")
Dim inFile As IO.StreamReader
        Dim strUserArray() As String
        Dim newRow As DataRow
        Dim txtFile As String = "\class.txt"
        Dim newID As Integer

        If IO.File.Exists(Application.StartupPath & txtFile) Then
            newID = dsUser.Tables("User").Rows(dsUser.Tables("User").Rows.Count - 1).Item("User_ID")

            inFile = IO.File.OpenText(Application.StartupPath & txtFile)
            Do Until inFile.Peek = -1
                newID = newID + 1
                strUserArray = inFile.ReadLine.Split(" ")
                MessageBox.Show(strUserArray(0) & strUserArray(1))
                newRow = dsUser.Tables("User").NewRow()
                newRow("User_ID") = newID
                newRow("Login_Name") = strUserArray(0)
                newRow("Password") = strUserArray(1)
                newRow("Access") = "student"
                Dim cb As New OleDb.OleDbCommandBuilder(datest)
                datest.Update(dsUser, "User")
                datest.Fill(dsUser, "User")

As a suggestion, I would try to move your OleDbCommandBuilder way up to the top, and use it to Fill() your dataset before you do any work, and use it again when you want to Update().

This article has been dead for over six months. Start a new discussion instead.