VB.NET - APP - Updating a Dataset to Database issue

[jonbartlam]

Hi There
I'm not sure what exactly is going wrong here. I'm writing an application that retreives a table from a database (tbl_internalfaults) and updates it. (Actually, just the status column will need updating with 'CLOSED' where necessary.)

I have been trying to use the OLEDB command builder to build the statement to update the database however it is returning an error whenever I try to do this.

I already have a dataset full of data, 1 record of which will have changed from the database. This particular entry needs updating to the database. I've included my code below and marked where it goes wrong. Below the code is the error. Please bear with me if there is a problem with my overall code as I'm not used to writing applications that interact with databases.


CODE IS:

    Private Sub btnComplete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnComplete.Click
 
        Dim ds As DataSet = New DataSet
        Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=\\secplus16\Updates\Security Plus New Applications\IT Equipment Asset Log\Asset Log.mdb"
        Dim conn As OleDbConnection = New OleDbConnection(conStr)
        Dim sqlStr As String = "SELECT * FROM tbl_InternalFaults"
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
        Dim strSelectedLst As String
        Dim intNumRows As Integer
        Dim intRowCounter As Integer
 
 
        conn.Open()
        da.Fill(ds, "tbl_InternalFaults")
 
        strSelectedLst = lstFaults.SelectedItem
        ProcessSelected(strSelectedLst)
        intNumRows = ds.Tables("tbl_InternalFaults").Rows.Count
        For intRowCounter = 0 To intNumRows - 1
            If ds.Tables("tbl_InternalFaults").Rows(intRowCounter).Item(0).ToString = strSelectedLst Then
                ds.Tables("tbl_InternalFaults").Rows(intRowCounter).Item(4) = "Closed"
                Exit For
            End If
        Next intRowCounter
 
        If ds.HasChanges Then
            Dim cb As New OleDbCommandBuilder(da)
            ***ERROR HERE*** da.Update(ds, "tbl_InternalFaults")            ds.AcceptChanges()
        End If
        conn.Close()
 
    End Sub
 

ERROR MESSAGE IS:

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217900
Message="Syntax error (missing operator) in query expression '((Error ID = ?) AND ((? = 1 AND Label Number IS NULL) OR (Label Number = ?)) AND ((? = 1 AND Reported By IS NULL) OR (Reported By = ?)) AND ((? = 1 AND Site ID IS NULL) OR (Site ID = ?)) AND ((? = 1 AND Status IS NULL) OR (Status = ?)) AND ((? = 1 AND Pri'."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusE rrors(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.UpdateFromDataTab le(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at WindowsApplication1.frmHelpdesk.btnComplete_Click( Object sender, EventArgs e) in C:\Documents and Settings\jbartlam\Desktop\My Local Documents\Projects\Helpdesk\Helpdesk\frmHelpdesk.v b:line 90
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventAr gs 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.O nMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager. System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.Run MessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.Run MessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationCo ntext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.Run(String[] commandLine)
at WindowsApplication1.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.Run UsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context( Object state)
at System.Threading.ExecutionContext.Run(ExecutionCon text executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

Any help you can give me will be greatly received and I thank you for just looking at this.

For the record I'm using VB.NET 2008 Express on windows XP

I've been pulling my hair out for 3 days at this!

[Turkill]

The alternative to doing this could be using update commands if you have the rows indexed in some way. You could just call the update on the rows that need to be changed.

If (condition) then
   str = 'UPDATE table_name SET column_name  = 'CLOSED' WHERE id = ' & id
    ' Open the connection and call the update string to change the data.
end if