Update unable to find...

[hog]

This one is driving me nuts as I have identical code in multiple forms which work fine, but for some reason this one does not!

 

Try

           ' empty equipment dataset prior to refreshing

           Me.DataSetAssets1.Clear()

           ' regenerate item dataset to reflect changes

           Me.OleDbAdapterAssets.Update(Me.DataSetAssets1)

           ' fill the datasets with refreshed data

           Me.OleDbAdapterAssets.Fill(Me.DataSetAssets1)

Catch objException As Exception

           ShowError("Location:   Class frmAssets" & ControlChars.CrLf & ControlChars.CrLf & _
                     "Procedure:  ResetDataset" & ControlChars.CrLf & ControlChars.CrLf & _
                     "Error Text: " & objException.Message)

End Try

 

An error occurs at the .Update line which reports the following:

 

Update unable to find TableMapping['Table'] or DataTable 'Table'.

 

If I generate and preview the data adapter in design mode it works OK?

[Madz]

What about Data set which has been generated, do it contains the

[hog]

The dataset Me.DataSetAssets1 has one table in it, tblAsset

[archer_coal]

re:

 

is the form in question identical to the others in properties? Shared member?

Try running the code without the update string and see if it executes. If it does then it is NOT a matter of the program not finding the table or table mapping. I'm assuming your using the same modal dataset to access it from multiple forms?

[Nerseus]

Why would you call Clear then Update? I don't see how the Update would do anything since you just cleared the DataSet? Maybe you want Clear then Fill and leave out the Update line...

 

-ner

[hog]

the selectcommand text is set elsewhere and this code is called to refresh the combobox that's bound to the dataset. There if a user selects they only want to see live records the clear method empties the dataset and the update reloads with just the live records. Likewise if the user want to see live and dead records the same process happens. And again if new records are appended then the reload takes place.

[hog]

OK I have narrowed down to when the error occurs. It happens in the Windows Form Designer Generated Code when InitializeComponent() is called.

 

The section of code I posted above is in a RadioButton CheckedChanged event, which InitalizeComponent() is obviously calling?

 

This happens on all the other forms I've coded this way but they are OK??

 

I have deleted all sections of related code, the dataset and the data adapter and re entered it and it is still happening.

 

Any ideas?

[jfackler]

Show us the instantiation of OleDbAdapterAssets.

[hog]

not sure if this is what you want?

 

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

Me.OleDbAdapterAssets.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblAsset", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("assetid", "assetid"), New System.Data.Common.DataColumnMapping("active", "active"), New System.Data.Common.DataColumnMapping("model", "model"), New System.Data.Common.DataColumnMapping("serial", "serial"), New System.Data.Common.DataColumnMapping("supplierid", "supplierid"), New System.Data.Common.DataColumnMapping("type", "type")})})

 

I thought maybe the first mention of Table above was to blame, but it is not as all other forms are identical to this, apart from actaul table and field names

[jfackler]

When you call the Fill(DataSet) method, the dataadapter creates a Data Table named Table and populates it with the rows returned from the data source.

 

When you call the Update(DataSet) method, the dataadapter updates the data source form a Data Table named Table in the specified DataSet.

 

When you call the Clear method of a DataSet, you empty all the tables in the DataSet.

 

The Table mentioned in the error message has been emptied by your clear method before you then try to use it to update your datasource in the snippet of code you have shown us.

 

I'm not following why you implement them in the way have shown us, or how/why it would work in any Form.

 

 

 

Jon

[jfackler]

 

The section of code I posted above is in a RadioButton CheckedChanged event, which InitalizeComponent() is obviously calling?

 

This happens on all the other forms I've coded this way but they are OK??

 

On these forms i.e. those that appear to not be generating the error, is the RadioButton CheckedChanged event firing?

 

Might explain why it only happens on the Form in question: it may be the only Form firing this event.

 

Jon

[hog]

OK this might explain why I do it this way.

 

This section fires when a user clicks a radio button to say they want to see live only or live and archived items:

 

Private Sub Radiobutton_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles radLiveOnly.CheckedChanged, radLiveAndArchived.CheckedChanged

       ' only do this if form is not refreshing

       If Not blnRefreshingForm Then

           Dim ctlSender As RadioButton = DirectCast(sender, RadioButton)

           ' ensure we are pointing at correct data

           Me.OleDbConnAssets.ConnectionString = gconnConnection

           ' determine who called this event and set selectcommand text accordingly

           Select Case ctlSender.Name

               Case "radLiveOnly"

                   Me.OleDbAdapterAssets.SelectCommand.CommandText = "SELECT assetid, supplierid, model, type, '[' + type + ']' " & _
                                                                     "+ ' ' + model + ' ' + serial AS description FROM tblAsset WHERE (active = - 1) ORDER BY type"

               Case "radLiveAndArchived"

                   Me.OleDbAdapterAssets.SelectCommand.CommandText = "SELECT assetid, supplierid, model, type, '[' + type + ']' " & _
                                                                     "+ ' ' + model + ' ' + serial AS description FROM tblAsset ORDER BY type"

           End Select

           ' refresh the suppliers combo box

           ResetDataset()

       End If

End Sub

Private Function ResetDataset()

       Try

           ' empty asset dataset prior to refreshing

           Me.DataSetAssets1.Clear()

           ' regenerate asset dataset to reflect changes

           Me.OleDbAdapterAssets.Update(Me.DataSetAssets1)

           ' fill the dataset with refreshed data

           Me.OleDbAdapterAssets.Fill(Me.DataSetAssets1)

       Catch objException As Exception

           ShowError("Location:   Class frmAssets" & ControlChars.CrLf & ControlChars.CrLf & _
                    "Procedure:  ResetDataset" & ControlChars.CrLf & ControlChars.CrLf & _
                   "Error Text: " & objException.Message)

       End Try

End Function

 

therefore though this code a combobox on the form will display either all live or live and archived depending on which radio button they click.

 

this method works perfectly fine on five other forms?

[jfackler]

Unless you are updating your datasource, comment out the update line and your code should work fine.

[hog]

I am updating the datasource.

[jfackler]

But you are trying to update it with an empty table since you have cleared all the tables in the dataset.

 

Try commenting out the update line.*nudge*

[hog]

I don't think you follow what I'm doing?

 

this code works as is in five other forms, so I cannot see why it won't work here. If I comment out the update line I get an error on the Fill line saying the select statement is not set. Yet it is set!

[hog]

Further explained. Lets say the original sql returned 2 records, if I don't use .clear first as shown then each time the .fll is called the record count is doubled.

[quwiltw]

I think no one is disputing the Clear() method call. It's the call to Update immediately afterwards that seems pointless because you just cleared all the data that you are requesting the update for. If you feel you need this line because it has some strange side effect on your select command, I'd still suggest you comment it out and dig deeper to find the root of the problem.

 

As for the "code works as is on five other forms"... I can't count the number of times I've said "... but it's the exact same code" only to remember that little difference hours of debugging later. If it's truly the same code perhaps you should consider inheriting from a base form that can do this work for you?

[hog]

OK perhaps I should explain further as reading my past posts this bit is not mentioned...oops!

 

There is a combobox on the form that lists all items live and archived.

 

The user can choose to see live only or live and archived by using the radio buttons.

 

There is also the need to refresh the data in the combobox to read from the database again as there is all probability that another user may have archived or appended new items to the list.

 

Therefore....the update method reads the database again to ensure it has an up to date listing of items in the database.

 

So from this perspective surely the update method is record?

[hog]

More food for thought....

 

OK so I get this error when the form opens. When I click OK to acknowledge the error and allow the app to continue the form acts exactly as the others.

 

I can use the radio buttons to filter the combobox and the call to ResetDataset works. If I add additional records to the database then the call to ResetDataset works perfect (just as in all the other forms).

 

Therefore there is something going on in the Windows Designer code that is causing the grief!

[APaule]

Sorry hog, but it is really hard to believe, that your code works in any form.

First of all, if you run a Clear against a dataset, you remove the table's contents of the dataset!

Then, if you perform an Update, what do you expect to happen? There are no more records to handle.

originally posted by hog

 

Therefore....the update method reads the database again to ensure it has an up to date listing of items in the database.

Who told you that an update will reread the data? The Update method does definitely NOT refill the dataset (this is managed by the Fill-method). It will just perform the INSERT-, UPDATE- and DELETE-commands of the DataAdapter.

 

So, what you have to do, is to clear the contents of the DataAdapter, then refill it (with Fill!!!). All bound controls should immediately show the new contents.

[hog]

Sorry folkes we seem to be going around in circles here.

 

 

Sorry hog, but it is really hard to believe, that your code works in any form.

First of all, if you run a Clear against a dataset, you remove the table's contents of the dataset!

Then, if you perform an Update, what do you expect to happen? There are no more records to handle

 

 

My code below, previously posted shows how this definiately works on this and all other forms! I've added some more comments to try to make it clearer for you.

 

 

Private Sub Radiobutton_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles radLiveOnly.CheckedChanged, radLiveAndArchived.CheckedChanged

       ' only do this if form is not refreshing

       If Not blnRefreshingForm Then

           Dim ctlSender As RadioButton = DirectCast(sender, RadioButton)

           ' ensure we are pointing at correct data

           ' this sets the connection object on the form to the current data source as chosen by the user elsewhere..

           Me.OleDbConnAssets.ConnectionString = gconnConnection

           ' determine who called this event and set selectcommand text accordingly

           ' this will assign the required sql to the oledbadapter on the form

           Select Case ctlSender.Name

               Case "radLiveOnly"

                   Me.OleDbAdapterAssets.SelectCommand.CommandText = "SELECT assetid, supplierid, model, type, '[' + type + ']' " & _
                                                                     "+ ' ' + model + ' ' + serial AS description FROM tblAsset WHERE (active = - 1) ORDER BY type"

               Case "radLiveAndArchived"

                   Me.OleDbAdapterAssets.SelectCommand.CommandText = "SELECT assetid, supplierid, model, type, '[' + type + ']' " & _
                                                                     "+ ' ' + model + ' ' + serial AS description FROM tblAsset ORDER BY type"

           End Select

           ' refresh the suppliers combo box

           ResetDataset()

       End If

End Sub

Private Function ResetDataset()

       Try

           ' empty asset dataset prior to refreshing so the dataset is empty

           Me.DataSetAssets1.Clear()

           ' regenerate asset dataset to reflect changes
           ' this definitely refreshes the dataset with the new data

           Me.OleDbAdapterAssets.Update(Me.DataSetAssets1)

           ' fill the dataset with refreshed data

           ' this fills the dataset with the new data

           Me.OleDbAdapterAssets.Fill(Me.DataSetAssets1)

       Catch objException As Exception

           ShowError("Location:   Class frmAssets" & ControlChars.CrLf & ControlChars.CrLf & _
                    "Procedure:  ResetDataset" & ControlChars.CrLf & ControlChars.CrLf & _
                   "Error Text: " & objException.Message)

       End Try

End Function

 

So there you have it. This does work exactly how I would expect it to.

 

You keep saying don't use clear, don't use update well:

 

when the ResetDataset function works,

 

if I omit .Clear the dataset gets duplicte records in it.

 

if I omit .Update I get an error on the .Fill line saying Selectcommand not set.

 

Strange that on the form the selectcommand is set as the default startup value.

 

Once I ok and ignore the error which occurs in the InializeComponent section all works OK.

 

All works OK on every other form using this setup....can't say anything else...it works as I understand it to work?

[hog]

 

if you perform an Update, what do you expect to happen?

 

 

DOOOOOOOHHHH!

 

yes I accept that the update line is a complete waste of time.

 

However when the form loads I get the error re selectcommand not set, even thought it is hard coded into the form as a starting point. Thereafter the code works a treat!

[jfackler]

Hog,

The multiple responses to your post have emphasized that the update method is, as I previously described, a utility to return values to the data source. (Please reread that sentence and the previous posts, I think you misunderstand the update method.) Having again reviewed your code, I would suggest you explore your blnRefreshingForm. My guess would be the Selectcommand is not set because the If block is not running NOT because of anything to do with the update method. Check the blnRefreshingForm value as you step through the entire code. Not just with the initial load but in subsequent cycles through your code as well. The error that occurs on the InitializeComponent probably has to do with CommandText not having a default value anywhere else in your code.

Having said all that,

So there you have it. This does work exactly how I would expect it to.

argues that you are happy with the error message and expect your end users to click ok and ignore the error message. I expect they may doubt the validity of the information they are presented when they have to click through an error message to get that data.

Several folks have tried to explain that the clear method is fine, in fact appropriate since you will get redundancy in your values if you don't employ the clear method, and the fill is appropriately used in your code but, it's time to review the update methods use and realize this is not its intended use.

 

A good working definition of insanity is: continuing to do the same thing over and over again, expecting different results.

 

Time to seek a solution instead of continuing to defend the problem.

 

Jon

[hog]

 

argues that you are happy with the error message and expect your end users to click ok and ignore the error message. I expect they may doubt the validity of the information they are presented when they have to click through an error message to get that data.

 

[/code]

 

Erm....no? I am developing the app and would not release it like this????

 

 

 

Time to seek a solution instead of continuing to defend the problem

 

 

Now you rerally are misunderstanding me!

 

This is frustrating as it would appear that unless I supply the whole application how can I make you understand?

 

The reson for the blnRefreshingForm test is there to stop all the controls .changed events firing etc when the form is having it's data refreshed after a user selects a record.

 

The other forms show this as false when InitializeComponent is called.

 

I'm not depending anything? However if I have identical methods working all the way through my application that all work correctly then why would I ignore that fact that this one does not work?

 

The fact you think I would let this application in this state out to the users shows you don't follow me. Also I have already accepted that the use of the Update method was not required and understand why. This does not mean that every other piece of my applications code is in question, have you never made a mistake like this?

 

Lastly, and I'm not having a pop at anyone whatsoever as this is all meant to a friendly helpful site, I will explain in the simplest terms I can.

 

In form design:

 

dataconnection set to a valid data source

dataadapter set to valid selectcommand text

dataset setup correctly

 

At runtime:

 

dataconnection set to users default

dataadapter selectcommand text set to require value

dataset is filled

 

The error I get is when the form is being initailised. It says the selectcommand text is not set. But as the selectcommand text is setup at design time how can this be? The selectcommand text only gets changed when a user selects a filter.

 

Lastly lasty :-) this method works perfectly well on every other form in the application and does so on this form if I ignore the inialisation error.