By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,048 Members | 415 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,048 IT Pros & Developers. It's quick & easy.

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

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1.     Private Sub btnComplete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnComplete.Click
  2.  
  3.         Dim ds As DataSet = New DataSet
  4.         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"
  5.         Dim conn As OleDbConnection = New OleDbConnection(conStr)
  6.         Dim sqlStr As String = "SELECT * FROM tbl_InternalFaults"
  7.         Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
  8.         Dim strSelectedLst As String
  9.         Dim intNumRows As Integer
  10.         Dim intRowCounter As Integer
  11.  
  12.  
  13.         conn.Open()
  14.         da.Fill(ds, "tbl_InternalFaults")
  15.  
  16.         strSelectedLst = lstFaults.SelectedItem
  17.         ProcessSelected(strSelectedLst)
  18.         intNumRows = ds.Tables("tbl_InternalFaults").Rows.Count
  19.         For intRowCounter = 0 To intNumRows - 1
  20.             If ds.Tables("tbl_InternalFaults").Rows(intRowCounter).Item(0).ToString = strSelectedLst Then
  21.                 ds.Tables("tbl_InternalFaults").Rows(intRowCounter).Item(4) = "Closed"
  22.                 Exit For
  23.             End If
  24.         Next intRowCounter
  25.  
  26.         If ds.HasChanges Then
  27.             Dim cb As New OleDbCommandBuilder(da)
  28.             ***ERROR HERE*** da.Update(ds, "tbl_InternalFaults")            ds.AcceptChanges()
  29.         End If
  30.         conn.Close()
  31.  
  32.     End Sub
  33.  

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!
Aug 26 '08 #1
Share this Question
Share on Google+
1 Reply

P: 7
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.

Expand|Select|Wrap|Line Numbers
  1. If (condition) then
  2.    str = 'UPDATE table_name SET column_name  = 'CLOSED' WHERE id = ' & id
  3.     ' Open the connection and call the update string to change the data.
  4. end if
  5.  
Aug 26 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.