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:
Expand|Select|Wrap|Line Numbers
- 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 unhandledAny help you can give me will be greatly received and I thank you for just looking at this.
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:
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!