[RESOLVED] Currency Manager - Update dataset

[LuckyLuke82]

Hi,

This is how I Databind fields in one of my edit Form:

   Private da As New OracleDataAdapter
   Private ds As New DataSet

        Try

            Using cmd As New OracleCommand("MyStoredProcedure", OracleConn)

                'Parameters for stored procedure etc.
                ...

                'Fill Dataset  
                da.SelectCommand = cmd
                da.Fill(ds)

                'Set Currency manager
                cma = CType(BindingContext(ds.Tables(0)), CurrencyManager)

    End Using

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Doing this allows me to easily reset all changes made in those fields using currency manager, like this:

if ds.HasChanges then

  cma.CancelCurrentEdit()

End If

This all works fine, but I cannot Update Dataset now. I tried this but It's not working:

 cma.EndCurrentEdit()

 If ds.HasChanges Then
       cma.Refresh()
       da.Update(ds.Tables(0))
       MsgBox("updated")
  End If

Without currency manager I can update easily using Dataset in Datagrids (e.g. da.Update(ds.Tables(0))), but not with this binded fields. Am I doing something wrong or It just cannot be done without writting full Update query ? Or Currency manager is not intended to do this ?

[jmcilhinney]

That call to Refresh is of no use so you can get rid of that.

As for the Update call, you say that it's not working but you don't explain what that means. Is there an exception thrown? If not, is HasChanges True and, if so, what value does Update return?

Regardless, I'd suggest not using a CurrencyManager from .NET 2.0 onwards. Instead, bind your DataTable to a BindingSource, which you can add in the designer, and then bind that to your controls. It has EndEdit and CancelEdit methods that will internally call the corresponding CurrencyManager methods.

[LuckyLuke82]

Thanks for response. There are so many options regarding DB that I'm completely lost.

As for the Update call, you say that it's not working but you don't explain what that means. Is there an exception thrown? If not, is HasChanges True and, if so, what value does Update return?

No. A simple If ds.HasChanges doesn't work at all. If I use cma.EndCurrentEdit before that then ds recognizes changes, but then da.Update(ds.Tables(0)) thows an exception "Update requires a valid UpdateCommand when passed DataRow collection" - so I reckon this needs to reference to written query ?

Thanks, I'll try bindingsource instead, but again : Can I perform update easily (like da.Update...) OR do I have to write UPDATE query manually ?

[jmcilhinney]

You should use a BindingSource but that's not going to help with that last issue because that has exactly zero to do with the CurrencyManager.

Update requires a valid UpdateCommand when passed DataRow collection

That is an issue with your data adapter. Where exactly were you expecting the UpdateCommand to come from? Are you using a command builder or are you creating the action commands manually? If it's the latter then you have failed to do so. If it's the former then you have failed to retrieve PK information so no UpdateCommand can be auto-generated.

[LuckyLuke82]

That is an issue with your data adapter. Where exactly were you expecting the UpdateCommand to come from?

That is the part which I don't quite understand yet. Learning from Oracle documentation regarding VB.NET Binding they recommend using Datasets, and in the article provided they show how easily can be done UPDATE command using da.Update(ds.Tables(0)). My fields that are binded are done same, the only difference is that I use Stored Procedure, and that I use binded fields instead of Datagrid. Also, I bind fields to only 1 record in DB but I exclude PK in Datable because there is only 1 record. Is that my flaw ? Should I bind some Textbox to PK too, and just hide It in run-time?

That is an issue with your data adapter. Where exactly were you expecting the UpdateCommand to come from?

i'm expecting to Update If there are any changes made to Dataset. Since this are fields bounded to 1 record, no Inserts can be done, neither DELETE. So when I click button I would expect a simple da.Update(ds.Tables(0)) would work.

Are you using a command builder or are you creating the action commands manually?

Currently what I have is manually written UPDATE command, but I want to be able to do just da.Update(ds.Tables(0)).

This is the Oracle article I'm talking about If you care to look, maybe I just understand It wrong (chapter Enabling Updates to the Database, at very bottom of the page):
https://docs.oracle.com/cd/B28359_01...p.htm#CHDHHGCG

[jmcilhinney]

You are under some misconceptions. A data adapter does simplify things but it's not magic. You can call Update to save all the changes in a DataTable but you still have to tell it HOW to save those changes.

A data adapter is a combination of up to four commands. When you call Fill, the SelectCommand is executed to retrieve data. There needs to be a SELECT statement in the SelectCommand, either in the CommandText directly or in the sproc specified by the CommandText. If there's no SELECT statement then no data will be retrieved.

When you call Update, the InsertCommand, UpdateCommand and DeleteCommand are executed as required to insert new rows and/or update and/or delete existing rows. If you add any new rows to the DataTable then you need an InsertCommand to insert that row into the database. In your case, you're obviously modifying existing rows so you need an UpdateCommand to update the existing records. A DeleteCommand is required if you delete any rows.

Under certain circumstances, you can use a command builder to automatically generate those action commands for you based on the query. As you're using a sproc for the query. I'd suggest that you should be using sprocs for the action commands too.

[LuckyLuke82]

Thanks, I needed someone to finally explain this to me. Yes, I will use Stored Procedure, I only use that for queries. BUT in short, how would you explain then this Oracle article - does It Update using da.Update(ds.Tables(0)) because there is PK visible in Datagrid ( or Listbox or whatever they use in article) ?

[jmcilhinney]

how would you explain then this Oracle article

I previously said this:

Where exactly were you expecting the UpdateCommand to come from? Are you using a command builder or are you creating the action commands manually?

and this:

Under certain circumstances, you can use a command builder to automatically generate those action commands for you based on the query.

Now go back and look at steps 8 and 9 of that walk-through again.

[LuckyLuke82]

Ok, now I get you. Command builder is what you meant. But that still involves writting queries at some point. So no shortcuts. Thanks for all info. I'm allready changing my design to binding source.

[jmcilhinney]

But that still involves writting queries at some point. So no shortcuts.

But it is a shortcut, which is the point. It just can't be used in all circumstances. For instance, you use "SELECT * FROM MyTable" as the query in the SelectCommand for a table with a PK then a command builder can automatically generate the appropriate INSERT, UPDATE and DELETE statements automatically when you call Update. If the table has no primary key then UPDATE and DELETE statements cannot be generated and an INSERT statement can't be generated if the query involves multiple tables. I'm not sure whether a command builder can be used at all with a query in a sproc but, if it can, the same rules would apply to the contents of the sproc.