472,146 Members | 1,273 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

OLEDB update/insert from data array failing data adapter update

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 3841

Post your reply

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

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
5 posts views Thread by Sagaert Johan | last post: by
9 posts views Thread by Pam Ammond | last post: by
reply views Thread by Brian Hanson | last post: by
1 post views Thread by Rod | last post: by
reply views Thread by NicK chlam via DotNetMonster.com | last post: by
1 post views Thread by Ann Marinas | last post: by
13 posts views Thread by Terry Olsen | last post: by
reply views Thread by leo001 | last post: by

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.