473,287 Members | 1,399 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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
1 2335
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

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

Similar topics

3
by: Tc | last post by:
Hi, I was curious, I am thinking of writing an application that loads a dataset from a database that resides on a server. The question I have is this, if multiple copies of the app will be...
5
by: junglist | last post by:
Hi guys, I've been trying to implement an editable datagrid and i have been succesful up to the point where i can update my datagrid row by row. However what used to happen was that once i updated...
10
by: jaYPee | last post by:
does anyone experienced slowness when updating a dataset using AcceptChanges? when calling this code it takes many seconds to update the database SqlDataAdapter1.Update(DsStudentCourse1)...
14
by: Lars Netzel | last post by:
A little background: I use three Datagrids that are in a child parent relation. I Use Negative Autoincrement on the the DataTables and that's workning nice. My problem is when I Update these...
2
by: Niels Jensen | last post by:
I have a some code which imports information from a text file to a dataset. Depending op what is being extracted from the text file, the dataset has 5 tables which can be written to. During this...
13
by: Doug Bell | last post by:
Hi, I thought I had this sorted this morning but it is still a problem. My application has a DataAccess Class. When it starts, it: Connects to a DB (OLE DB) If it connects it uses an...
6
by: Mike Wilson | last post by:
Dear Group, I have a heirarchical set of database tables, say - "order" and "order_type" and want to display a series of orders in a grid control, and in place of the order_type foreign key...
4
by: Geoff | last post by:
Hi I'm hoping somebody can help me with the following problem that has occurred to me. Suppose I have two tables in an SQL Server database. Let's call these tables A and B. Assume that A has...
0
by: Johnny | last post by:
I have a PocketPC mobile application that gets its data from the Sql Server database via a web service. The web service returns a dataset that I need to load into the SqlCe database on the mobile...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.