update dataset error

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


'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!
jackjohnson44Author Commented:
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.
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.

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

jackjohnson44Author Commented:
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?
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.

jackjohnson44Author Commented:
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.
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?

jackjohnson44Author Commented:
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
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.