473,700 Members | 2,691 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

OLEDB update/insert from data array failing data adapter update

1 New Member
I am trying to update an Access table using OLEDB in VB.Net 2005. I can add rows but I cannot update them.

Code to instantiate the Access database and table...
Expand|Select|Wrap|Line Numbers
  1.         Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db.Name)
  2. conn.Open()
  3. Dim oda As New OleDb.OleDbDataAdapter("select " & sqlCols & " from [" & txtOutputTable.Text.Trim & "];", conn)
  4. Dim cb As New OleDb.OleDbCommandBuilder(oda)
  5. cb.QuotePrefix = "["
  6. cb.QuoteSuffix = "]"
  7. oda.UpdateCommand = cb.GetUpdateCommand
  8. oda.InsertCommand = cb.GetInsertCommand
  9. oda.DeleteCommand = cb.GetDeleteCommand
  10. Dim ds As New DataSet
  11. oda.Fill(ds, txtOutputTable.Text.Trim)
<sqlCols> contains the column names I want to update/insert delimited by [ and ]. This may or may not include all columns in the table but that's the point. I want to facilitate doing whatever a user asks for, potentially augmenting existing data with new colums for the same key. This is why I only specify the columns I need to work with in data adapter. The key is date and hour of the day (1 - 24). By the time the code gets here that table structure contains all needed columns.

The program then goes off to another source and builds a data array based upon time range and data items. Date and hour are the first two columns and the remaining data fleshes out the rest of the columns. This data may need to update the table contents or be inserted. The data array def is as follows:
Expand|Select|Wrap|Line Numbers
  1. Dim da(iHrs, iCols) As Object
Code to build dataset for update from the data array:
Expand|Select|Wrap|Line Numbers
  1. For i = 0 To da.GetUpperBound(0)
  2.     ds.Tables(0).BeginLoadData()
  3.     Dim dr(da.GetUpperBound(1) + 1) As Object
  4.     dr(0) = CDate(da(i, 0).ToString.Substring(0, 11))
  5.     dr(1) = da(i, 0).ToString.Substring(12, 2)
  6.     For j = 1 To da.GetUpperBound(1)
  7.         dr(j + 1) = da(i, j)
  8.     Next
  9.     ds.Tables(0).LoadDataRow(dr, False)
  10.     ds.Tables(0).EndLoadData()
  11. Next
Code to update the data adapter...
Expand|Select|Wrap|Line Numbers
  1. Try
  2.     oda.Update(ds, txtOutputTable.Text.Trim)
  3. Catch ex As Exception
  4.     MsgBox(ex.Message)
  5. End Try
The oda.Update fails saying it's trying to create duplicate values in the index, primary key, or relationship. The primary key is the first two columns of each dr. There are no relationships so I have to assume the update is defaulting to insert all the time instead of choosing update where required. I've been working under the premise that if I define the appropriate data adapter commands, OLEDB figures out what needs to be updated or inserted and "magic happens" (I know, very naive of me). Obviously this is not the case but I do not know how to fix it short of going back to dao and doing FindFirst's or Seek's and proceeding accordingly.
Sep 25 '07 #1
0 3972

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

Similar topics

by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums must be UPDATED, if not, they must be INSERTED. Logically then, I would like to SELECT * FROM <TABLE> WHERE ....<Values entered here>, and then IF FOUND UPDATE <TABLE> SET .... <Values entered here> ELSE INSERT INTO <TABLE> VALUES <Values...
by: Sagaert Johan | last post by:
I need some example I have a dataset populeted through a dataadapter fill method i can add rows to my dataset , but i have an exception when calling the dataadpters update method. I already spent 7 hours to solve this, yet i still am no step further. I might be missing something around the Updatecommand property of the oleDBDataAdapter.
by: Pam Ammond | last post by:
I need the code to update the database when Save is clicked and a text field has changed. This should be very easy since I used Microsoft's wizards for the OleDBAdapter and OleDBConnection, and DataSet; and all I'm doing is showing one record in text fields, allowing the user to modify the text fields, and then updating the database again when the user clicks the Save button. The fields already show the correct data record since I have...
by: Will Pittenger | last post by:
I have a project where the backend is Access. I have learned (slowly) to use OLEDB to access my data. However, to save changes, I need to generate a SQL statement. This seems clunky. I am used to MFC recordsets. The OleDB system will let me add the row to the rowset, but then does nothing with it. I have tried calling BeginEdit/EndEdit, AcceptChanges (from several different classes), System.Data.DataTable.Rows.Add, and...
by: Brian Hanson | last post by:
Hi, I have an unusual problem that just showed its ugly head at a pretty bad time. I have an asp.net (VB) app that takes data from an Excel sheet and puts it into SQL Server. I get the data out of Excel using OleDB, and suddenly, some of the data was not being extracted from Excel. I use OleDb for the extract into a DataTable and from there an SqlClient.SqlCommand to put it into SQL Server.
by: Rod | last post by:
I am writing my third ASP.NET application. Unfortunately for me, I get to work on these only once every few months (which doesn't help in trying to remember how to do everything). Anyway, I've got some textboxes on an ASPX page and a submit button. I have a data adapter on the ASPX page, along with the generated DataSet. I allowed the DataAdapter to generate the Select, Update, Delete SQL strings (it is running against a SQL Server 2000...
by: NicK chlam via DotNetMonster.com | last post by:
this is the error i get System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.Common.DbDataAdapter.Update(DataRow dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at TryThis.Form1.save() in C:\Documents and Settings\Nick\My Documents\...
by: Ann Marinas | last post by:
Hi all! I'm developing an application that imports a CSV file then stores the imported data to an XML file through a dataset and an Access database. I've used the connection and data adapter objects. I don't have any problems in saving the imported data in a dataset and xml file, but whenever I'm saving it to the access database, I always get this error: ===================================== System.Data.OleDb.OleDbException: Parameter...
by: Terry Olsen | last post by:
I'm using OleDb to connect with an Access Database. I have anywhere from 10 to over 100 records that I need to either INSERT if the PK doesn't exist or UPDATE if the PK does exist, all in a single transaction. Does anyone have an SQL statement I can throw at it that would accomplish this? If I can't figure out how to do it, I'm going to have to send two discreet SQL commands for each record which will take infinitely longer than a...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.