[moman1981]

I have been working on this for a couple days now, and I am ready to throw my computer through the window.

Any assistance would be greatly appreciated. I am still rough around the edges with Visual Studio 2010.

When I run the project, it works just as it should. the fields populate with the selected items from the .accdb, and I can navigate just fine up and down the rows. The problem comes when I try to update the info. When I change a value and hit the update button I get the following ERROR:

System.Data.OleDb.OleDbException was unhandled
Syntax error (missing operator) in query expression '((PID = ?) AND ((? = 1 AND LOGIN IS NULL) OR (LOGIN = ?)) AND
((? = 1 AND LAST4SSN IS NULL) OR (LAST4SSN = ?)) AND
((? = 1 AND LASTNAME IS NULL) OR (LASTNAME = ?)) AND
((? = 1 AND FIRSTNAME IS NULL) OR (FIRSTNAME = ?)) AND
((? = 1 AND MI IS NULL) OR (MI ='.


Here is the full ERROR printout:

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217900
Message=Syntax error (missing operator) in query expression '((PID = ?) AND ((? = 1 AND LOGIN IS NULL) OR (LOGIN = ?)) AND ((? = 1 AND LAST4SSN IS NULL) OR (LAST4SSN = ?)) AND ((? = 1 AND LASTNAME IS NULL) OR (LASTNAME = ?)) AND ((? = 1 AND FIRSTNAME IS NULL) OR (FIRSTNAME = ?)) AND ((? = 1 AND MI IS NULL) OR (MI ='.
Source=Microsoft Access Database Engine
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at CSGLP_5.Form1.btnUpdate_Click(Object sender, EventArgs e) in C:\Users\Lucas\Documents\VisualBasic\CSGLP_5\CSGLP_5\Form1.vb:line 71
at System.Windows.Forms.Control.onclick(EventArgs e)
at System.Windows.Forms.Button.onclick(EventArgs e)
at System.Windows.Forms.Button.onmouseup(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
at System.Windows.Forms.Nativewindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at CSGLP_5.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

Public Class Form1
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CSGLPF.AssignedPersonnel' table. You can move, or remove it, as needed.
        'Me.AssignedPersonnelTableAdapter.Fill(Me.CSGLPF.AssignedPersonnel)
        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source = C:\Users\Lucas\Documents\VisualBasic\CSGLP_5\CSGLPersonnel_Front.accdb"

        
        con.ConnectionString = dbProvider & dbSource

        Try
            con.Open()


            sql = "SELECT * FROM AssignedPersonnel" ' order by LASTNAME asc"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "CSGLPF")
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

        con.Close()

        MaxRows = ds.Tables("CSGLPF").Rows.Count
        inc = -1
        'txtMaxRows.Text = MaxRows.ToString
    End Sub

    Private Sub NavigateRecords()
        Try
            '=====================ASSIGNED PERSONNEL, MEMBER INFORMATION=======================
            txtLast4.Text = ds.Tables("CSGLPF").Rows(inc).Item(2).ToString()
            txtTitle.Text = ds.Tables("CSGLPF").Rows(inc).Item(7).ToString()
            txtPayGrade.Text = ds.Tables("CSGLPF").Rows(inc).Item(8).ToString()
            txtLname.Text = ds.Tables("CSGLPF").Rows(inc).Item(3).ToString()
            txtFname.Text = ds.Tables("CSGLPF").Rows(inc).Item(4).ToString()
            txtMinitial.Text = ds.Tables("CSGLPF").Rows(inc).Item(5).ToString()
            txtGender.Text = ds.Tables("CSGLPF").Rows(inc).Item(9).ToString()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        If inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc <> -1 Then
            '=====================DATA UPDATES=======================
            '=====================ASSIGNED PERSONNEL, MEMBER INFORMATION=======================
            ds.Tables("CSGLPF").Rows(inc).Item(2) = txtLast4.Text
            ds.Tables("CSGLPF").Rows(inc).Item(7) = txtTitle.Text
            ds.Tables("CSGLPF").Rows(inc).Item(8) = txtPayGrade.Text
            ds.Tables("CSGLPF").Rows(inc).Item(3) = txtLname.Text
            ds.Tables("CSGLPF").Rows(inc).Item(4) = txtFname.Text
            ds.Tables("CSGLPF").Rows(inc).Item(5) = txtMinitial.Text
            ds.Tables("CSGLPF").Rows(inc).Item(9) = txtGender.Text

            '=====================PUSH TO DATABASE =======================
            Try
                da.Update(ds, "CSGLPF")
                MsgBox("Data updated")
            Catch ex As Exception
                MsgBox(ex.Message.ToString)
            End Try
        End If
    End Sub


    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        ElseIf inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc = 0 Then
            MsgBox("First Record")
        End If
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If
    End Sub

End Class

[demausdauth]

I dont' work with DataAdapters and the built in commands, but just by reading up on it at MSDN - DataAdapter (damn IE) (Firefox to the rescue on the edit. ) I think I see the problem or at least a problem.

According to the example at the link above you need to set the DataAdapter's Update Command from the OleCommandBuilder.

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        If inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc <> -1 Then
            '=====================DATA UPDATES=======================
            '=====================ASSIGNED PERSONNEL, MEMBER INFORMATION=======================
            ds.Tables("CSGLPF").Rows(inc).Item(2) = txtLast4.Text
            ds.Tables("CSGLPF").Rows(inc).Item(7) = txtTitle.Text
            ds.Tables("CSGLPF").Rows(inc).Item(8) = txtPayGrade.Text
            ds.Tables("CSGLPF").Rows(inc).Item(3) = txtLname.Text
            ds.Tables("CSGLPF").Rows(inc).Item(4) = txtFname.Text
            ds.Tables("CSGLPF").Rows(inc).Item(5) = txtMinitial.Text
            ds.Tables("CSGLPF").Rows(inc).Item(9) = txtGender.Text

            '=====================PUSH TO DATABASE =======================
            Try
                da.UpdateCommand = builder.GetUpdateCommand()
                da.Update(ds, "CSGLPF")
                MsgBox("Data updated")
            Catch ex As Exception
                MsgBox(ex.Message.ToString)
            End Try
        End If
    End Sub

I'll be honest, since I don't work much with the DataAdapter, I don't know if this will address your issue or not. I just noticed the difference between what you were doing and what the example code showed.

This post has been edited by demausdauth: 07 October 2011 - 09:45 PM

[moman1981]

Thank you demausdauth I appreciate the effort, but It did not work.

[smohd]

How is not working? still the same error? anything happen?

[moman1981]

I am still receiving the same error as listed above

[smohd]

Try to do the following changes in your update and let me know if it works or not:

 ElseIf inc <> -1 Then
   'lines that feed the ds
   ds.AcceptChanges()'accept changes made to it
   Dim change As DataSet = ds.GetChanges()'get all changes
   Dim commandbuild As New OleDbCommandBuilder(da)'command builder
   If change IsNot Nothing Then 'if there is any change
      da.Update(change, "CSGLPF")'update
   End If

This post has been edited by smohd: 08 October 2011 - 06:56 AM

[moman1981]

smohd,

Thank you. That seemed to work to update the DataSet, but when I close the program and re-run it, the changes are not there.

[smohd]

You mean data are not saved to the database? what you see in your database after refreshing the database

[moman1981]

yes, the data is not being saved in the database. it looks like it is only being saved in the data set and when the program i closed and then re-opened, the original data is still there. So the data is not getting from the data set to the linked database.

[smohd]

If you can post your updated code and see what you have done, because we update data adapter and not dataset

[moman1981]

UPDATED CODE

Imports System.Data.OleDb

Public Class AssignedPersonnel
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub btnMainMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMainMenu.Click
        Me.Hide()
        'MaintainPersonnel.Show()
    End Sub

   

    Private Sub AssignedPersonnel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source = C:\Users\Lucas\Documents\VisualBasic\CSGLP_3\CSCSU GL Personnel 2011.accdb"

       
        con.ConnectionString = dbProvider & dbSource

        Try
            con.Open()

            sql = "SELECT * FROM AssignedPersonnel" ' order by LASTNAME asc"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "CSGLPAP")

            
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

        con.Close()
        
        MaxRows = ds.Tables("CSGLPAP").Rows.Count

        inc = -1


        txtMaxRows.Text = MaxRows.ToString
    End Sub

    Private Sub NavigateRecords()
        '=====================ASSIGNED PERSONNEL, MEMBER INFORMATION=======================
        txtLast4.Text = ds.Tables("CSGLPAP").Rows(inc).Item(2).ToString()
        txtTitle.Text = ds.Tables("CSGLPAP").Rows(inc).Item(7).ToString()
        txtPayGrade.Text = ds.Tables("CSGLPAP").Rows(inc).Item(8).ToString()
        txtLname.Text = ds.Tables("CSGLPAP").Rows(inc).Item(3).ToString()
        txtFname.Text = ds.Tables("CSGLPAP").Rows(inc).Item(4).ToString()
        txtMinitial.Text = ds.Tables("CSGLPAP").Rows(inc).Item(5).ToString()
        txtGender.Text = ds.Tables("CSGLPAP").Rows(inc).Item(9).ToString()


    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        If inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc <> -1 Then
            '=====================DATA UPDATES=======================
            '=====================ASSIGNED PERSONNEL, MEMBER INFORMATION=======================
            ds.Tables("CSGLPAP").Rows(inc).Item(2) = txtLast4.Text
            ds.Tables("CSGLPAP").Rows(inc).Item(7) = txtTitle.Text
            ds.Tables("CSGLPAP").Rows(inc).Item(8) = txtPayGrade.Text
            ds.Tables("CSGLPAP").Rows(inc).Item(3) = txtLname.Text
            ds.Tables("CSGLPAP").Rows(inc).Item(4) = txtFname.Text
            ds.Tables("CSGLPAP").Rows(inc).Item(5) = txtMinitial.Text
            ds.Tables("CSGLPAP").Rows(inc).Item(9) = txtGender.Text

            '=====================PUSH TO DATABASE =======================
            Try
                ds.AcceptChanges() 'accept changes made to it
                Dim change As DataSet = ds.GetChanges() 'get all changes
                Dim commandbuild As New OleDbCommandBuilder(da) 'command builder
                If change IsNot Nothing Then 'if there is any change
                    da.Update(change, "CSGLPAP") 'update
                End If

                MsgBox("Data updated")
            Catch ex As Exception
                MsgBox(ex.Message.ToString)
            End Try
        End If
    End Sub
End Class

[smohd]

What happen if you comment this line? ds.AcceptChanges()
Also try to put break and debug it to see if it enter in both if conitions

This post has been edited by smohd: 08 October 2011 - 09:47 AM

[moman1981]

If I remove the "da.AcceptChanges()" statement, then I get the same error that started this mess.

[smohd]

To be honest that error is beyond my knowledge. I used that code many times to update the data adapter and all changes are saved directly to the database. this is because data adapter is connected directly with the data source. But I believe someone will be here to help you. All the best but dont give up, try to edit it, I have used it tone of times and it works fine

[moman1981]

Thank you for all your help. I am at least able to update the dataset and that is a step further than what I was a couple of hours ago.

Thank you.