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

OLEDB update/insert from data array failing data adapter update

P: 1
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
Share this question for a faster answer!
Share on Google+

Post your reply

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