473,554 Members | 3,024 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 3955

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

Similar topics

16
16982
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...
5
13549
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.
9
4744
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...
4
3063
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,...
0
302
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...
1
1217
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...
0
5630
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...
1
1040
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...
13
8844
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...
0
7512
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...
0
7783
Oralloy
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. ...
0
8023
jinu1996
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...
0
6129
agi2029
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...
0
5144
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3536
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2009
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
1
1117
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
828
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.