update dataset error

Posted on 2007-03-30
Last Modified: 2013-12-17
I am having trouble updating my dataset.

I have several datasets that I select by passing in a GUID, getting the ds back, binding it to a grid, textbox, or anything, then calling update and everything works well.

On one form I fill the ds with a subquery
something like
select * from table1 where foreignkey in (select foreignkey from table2 where primarykey=1)

I also have a generic fill statement, built by visual studio.

I fill my ds, then call update, no errors, but nothign happens.

Does it have something to do with the way it is filled?  I am using one method to fill and another to update.  Is this a problem?  I really don't do too much , but pass in a foreign key.

I don't even know how to debug this.  I can step through, but nothing happens.
Question by:jackjohnson44
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4

Expert Comment

ID: 18824654

'Form Level Declarations:
    'ADO.NET Data Connection
    Private cnConnection As New OleDb.OleDbConnection
    'Data Adapter
    Private daAdapter As New OleDb.OleDbDataAdapter
    'Command Builder
    Private cbCommandBuilder As New OleDb.OleDbCommandBuilder
    'Data Table
    Private dtFleet As New DataTable

'Form Load (or where ever you wish to create your dataset)
     Dim daAdapter As New OleDb.OleDbDataAdapter
     daAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM FleetMaster", cnConnection)

'IF adding a new row to your dataset
Dim drDataRow as DataRow
drDataRow = dtFleet.NewRow()
drDataRow("FieldName") = myTextBox.Text.ToString()
dtFleet.Columns("myPrimaryKey").AllowDBNull = True     'Allows AutoNumber in Access for Primary Key and tells VB to ignore.

'IF Updating an existing row, simply locate your row, make your change and


Hope this helps...
Good Luck!

Author Comment

ID: 18826464
Hi Kerry, not at all, but thanks though.

I know how to update a dataset.

It usually works except with this particular instance.

I call the update command, view the dataset first through the vs dataset viewer thing, my changes are there, update is called, no errors, no update.

I want to know how to figure out what is happening.

I tried to step though the code, which shows the dataset with my changes, the call to update, and the database never gets updated.

Your code also uses a sql statement and builds the ds onthe fly.  I actually made the ds in the designer, with a tableadapter with a .update and .fill command.
LVL 34

Expert Comment

ID: 18826903
The wizards are wonderful ;-)  Except when it comes to debugging ;-(

I suggest you have a look at the tableadapter's update command and its parameters.  You can get to see these in a few ways, but the easiest for copying and pasting is as follows.

In Solution Explorer, make sure Show All Files is clicked.  Expand the tree for your dataset.  Open its Designer.vb file.  In the lefthand drop down at the top select the tableadapter concerned.  In the righthand dropdown at the top, click on InitAdapter.  That should display the sub which sets up the Insert, Update and Delete commands.

What you need to check are (a) that there is an update command and (b) if there is, that it's using the right commandtext and parameters.  If you're not sure about (b) copy and paste the relevant code into a post here.  To be fully comprehensible it ought to be seen "in context".  But if there's something odd about it that is often possible to spot even out of context and, in that case, we might be able to suggest where you should concentrate your own digging.

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.


Author Comment

ID: 18827330
Thanks Roger,
I checked the update command, and it is there.  I will post it over the weekend.  Is there a way to see the sql string that get's executed during the update?  Meaning with the actual values instead of the sql with parameters?
LVL 34

Expert Comment

ID: 18828272
In the .Designer.vb file for the dataset concerned, click in the lefthand dropdown on _adapter under the TableAdapter concerned.  Then in the righthand combo click on RowUpdating.  You can then code something into the sub.  For example

        Private Sub _adapter_RowUpdating(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatingEventArgs) Handles _adapter.RowUpdating
            For Each p As OleDb.OleDbParameter In e.Command.Parameters
        End Sub

If you're using SQL objects you'll obviously need to replace the OleDb refs with SQL.  Unfortunately I don't know of a way actually to reproduce the commandtext including the values, but the above would give the necessary pieces for that jigsaw.


Author Comment

ID: 18844641
Thanks Roger,
I am not sure why, but I tried your suggestion and that piece of code was never entered.  I also checked status of the ds and it did have changes.  Any other ideas?  I also put a trace on the db and nothing happened during the update.  It seems like it does nothing.
LVL 34

Expert Comment

ID: 18845126
I tried to step though the code, which shows the dataset with my changes, the call to update, and the database never gets updated.

Will you please post the code you stepped through before you wrote that?

I also checked status of the ds and it did have changes.  

And the code by which you did that?  And any other code that is executed between that and when the .Update method is called?


Author Comment

ID: 18846030
sure, but there isn't too much going on.  This is on my dataaccess layer by the way.  Is there something more that you wanted me to post?  It produces no errors, and the update does not seem to call the database, I had a trace on it (the db) and nothing happened.

    Public Sub UpdateClaimantClass(ByVal ds As ClaimantsDS)
            debug.print ds.HasChanges  'If I highlight the ds, I can see the dataset viewer with the new datarow (my name is different than it was when I pulled from the database), this is also where the haschanges flag was true
    End Sub
LVL 34

Accepted Solution

Sancler earned 500 total points
ID: 18847136
What I suggest you do is (temporarily) add code to that sub to check what changes are in the specific DataTABLE we are interested in.

   Dim dra() As DataRow = ds.Claimants.GetChanges
   If Not dra Is Nothing Then
      For Each dr As DataRow In dra
         'some code to allow examination of the row/s, for example
   End If

That should show whether what you are seeing in the dataset viewer is reflected in the table to which you are applying the dataadapter.  It it's not, we'll need to find out why.  If it is, we'll then need to consider why the dataadapter isn't working on it.


Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP.NET Calendar Control 5 54
What is needed to become a DBA? 7 56
Manage big list of parameter list 8 24
CSS styling problem 3 28
It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question