Solved

update dataset error

Posted on 2007-03-30
9
366 Views
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.
0
Comment
Question by:jackjohnson44
  • 4
  • 4
9 Comments
 
LVL 6

Expert Comment

by:kerryw60
ID: 18824654
Sample:

'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)
     daAdapter.Fill(dtFleet)



'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.
dtFleet.Rows.Add(drDataRow)
daAdapter.Update(dtFleet)


'IF Updating an existing row, simply locate your row, make your change and
daAdapter.Update(dtFleet)


>>>>>>>>>>>>>>>>>>>>

Hope this helps...
Good Luck!
0
 

Author Comment

by:jackjohnson44
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.
0
 
LVL 34

Expert Comment

by:Sancler
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.

Roger
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:jackjohnson44
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?
0
 
LVL 34

Expert Comment

by:Sancler
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
            Debug.WriteLine(e.Command.CommandText)
            For Each p As OleDb.OleDbParameter In e.Command.Parameters
                Debug.WriteLine(p.Value)
            Next
        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.

Roger
0
 

Author Comment

by:jackjohnson44
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.
0
 
LVL 34

Expert Comment

by:Sancler
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?

Roger
0
 

Author Comment

by:jackjohnson44
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
            m_taClaimants.Update(ds.Claimants)
    End Sub
0
 
LVL 34

Accepted Solution

by:
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
         Debug.WriteLine(dr.RowState)
         Debug.WriteLine(dr(0))
      Next
   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.

Roger
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio hangs on running project 6 57
Access/Visual Basic Question 3 41
VB.Net Data Class 1 18
PowerShell: ForEach-Object Export to CSV 4 31
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

735 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