Update unable to find...

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 tables 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.
Click to expand...
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.
 
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 tables 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! Ive added some more comments to try to make it clearer for you.


Code: 
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 dont use clear, dont 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....cant say anything else...it works as I understand it to work?
Click to expand...
Click to expand...
 
if you perform an Update, what do you expect to happen?
Click to expand...

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!
 
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.
Click to expand...
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 dont employ the clear method, and the fill is appropriately used in your code but, its 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
 
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
Click to expand...

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 its data refreshed after a user selects a record.

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

Im 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 dont 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 Im 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.
Click to expand...
 
Hog,

Why dont you put "Me.DataSetAssets1.Clear()" before the "Select Case ctlSender.Name" and see whether it works.

- zy_abc
 
So your current error is "Select command is not setup"? Set a breakpoint on the line thats causing the error and check the SelectCommand to see what it shows. If it is empty, youll have to look back (you can use the CallStack) to see what code was called prior to your initialize and see where you think the Select Command was being filled OR where it might be getting cleared.

Without seeing your code, its very difficult to guess what might be clearing out the Select Command.

-ner
 
Nerseus, I have tried this already. I have put breakpoints everywhere and the code does not stop. However I narrowed it down to the this bit:

Code: 
   Public Sub New()
        MyBase.New()

        This call is required by the Windows Form Designer.
        InitializeComponent()
        Add any initialization after the InitializeComponent() call

    End Sub

If I put a break point at MyBase.New() that line works. The next line InitializeComponent() gets called and the churns away for a bit then the error is displayed. The error trap Im using displays it as being in ResetDataset. The selectcommandtext is blank, but further down in the windows designer code is this:

Code: 
        OleDbSelectCommand1
        
        Me.OleDbSelectCommand1.CommandText = "SELECT assetid, supplierid, model, type, [ + type + ] +   + model +   + s" & _
        "erial AS description FROM tblAsset ORDER BY type"
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnAssets

Once I click OK on my error handler form the form opening is displayed and everything works fine.

I cannot get the step through code to step into InitializeComponent() so I can see what is happening.
 
I feel that the real error is somewhere else. Lets say we have here the proof for a typical MS error message. ;) Just telling that there is an error, but the message does not tell you the real problem.:p
So lets try something else:
There is a little chance that your problem lies within your sql-statement. You use a field name called "type". This should be a reserved word in Access and also SQL Server. So, replace all occurrences of "type" with "[type]" and then try again.
I have yet seen a lot of problems with reserved words in sql-statements, so maybe this explains that irregular behaviour.
 
OK thanks for this, however I did not hold out much hope as the selectcommandtext is the same as the other forms. But at this stage I would try sticking my you know what in a jam jar full of angry wasps if I thought it would help.....

So I replace all entries of type with [type] to get the same result. The code stops at my breakpoint at InitializeComponent. I F11 the line executes but does not move past the cursor point at InitializeComponent. I get the error again selectcommandtext not initialized before call to .fill. I click OK the everything works ok??

I have double checked and the oledataadapter on the form has the selectcommand text set correctly. I have looked in the windows desiger generated code and the entry is there?

As the error is happening inside a procedure called InializeComponent that was put there by the IDE I cannot see what code is inside this procedure to see where it is going wrong?

My bloody brain is well and trully hurting now!
 
:confused: Why cant you see the code inside InitializeComponents?

Look out for the line
Code: 
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
There it starts. Maybe this is a good place to look for the real error. It could be that the order of initializing all your components is wrong for any reason. I had that problem once. This may happen, even if the code is generated automatically. So much for automation. :p
 
OK, you have confirmed for me that this is the InializeComponent() procedure, but the cursor stays at the line that calls it. It does not move into the block of code you show above. This is what I meant by not being able to see where exactly it goes wrong :-(
 
OK....I HAVE FIXED IT :-)

Can anyone explain why this error plagued me until I manually moved all code to do with the dataset, oledbdataadapter and connection into the beginning of the InializeComponent procedure?

This means I have modified what the windows designer did to fix the problem? All the other forms work fine with the windows designer placed code, so why would this form be different?

Have I as APaule says possibly discovered an MS bug??
 
Back
Top