Solved

update dataset error

Posted on 2007-03-30
9
362 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now