Question Problem updating dataset with bound datagrid and textboxes

[zodg19]

I'm new here and to VB.net. I'm sure this is simple and I appologize.

I have a windows form that contains a DataGridView that is bound to a Dataset Table. There are also several textboxes that are also bound to fields in the dataset table. There is a button to save changes. The Datagridview is set to read only.

My problem is, if I edit the record through the textboxes and want to save the changes. I have to click on another record in the datagridview for the changes to be saved correctly. If I do not click on a different row then the dataset.HasChanges = false and no changes will be saved.

I've tried refreshing the datagridview and that populates the values from the textboxes into the datagrid, but it does not put them in the dataset.

Thanks for any help anyone can provide.

Private Sub FrmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        
        Dim DBLogin As String = "Data Source=" & DB_DSN & ";User Id=" & UN & ";Password=" & PW & ";"
        conn = New OracleConnection(DBLogin)
        
        DAReports = New OracleDataAdapter("select * from rptmgr_reports", conn)
        DAReports.Fill(ds, "RPTMGR_REPORTS")
        DAContacts = New OracleDataAdapter("select * from rptmgr_contacts", conn)
        DAContacts.Fill(ds, "RPTMGR_CONTACTS")

        DataGridReports.DataSource = ds.Tables(0)
        DataGridRecipients.DataSource = ds.Tables(1)
        ds.Tables(1).Columns(0).Unique = True
        ds.Tables(1).Columns(0).AutoIncrement = True
        ds.Tables(1).Columns(0).AutoIncrementSeed = _
         If(IsDBNull(ds.Tables(1).Compute("Max(ID)", Nothing)) _
            , 20001, ds.Tables(1).Compute("Max(ID)", Nothing) + 1)

        TxtBoxOrg.DataBindings.Add("text", ds.Tables(1), "ORGANIZATION")
        TxtBoxName.DataBindings.Add("text", ds.Tables(1), "NAME")
        TxtBoxEmail.DataBindings.Add("text", ds.Tables(1), "EMAIL")
        MTxtBoxPhone.DataBindings.Add("text", ds.Tables(1), "PHONE")
        MTxtBoxAltPhone.DataBindings.Add("text", ds.Tables(1), "ALTPHONE")

        Dim CBItems As ArrayList = New ArrayList()
        CBItems.Add(New cValue("Active", "A"))
        CBItems.Add(New cValue("Inactive", "I"))

        ComboBoxStatus.DataSource = CBItems
        ComboBoxStatus.DisplayMember = "Display"
        ComboBoxStatus.ValueMember = "Value"
        ComboBoxStatus.DataBindings.Add("SelectedValue", ds.Tables(1), "Status")

    End Sub

    Private Sub ButSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButSave.Click
        DataGridRecipients.Refresh()
        If ds.HasChanges.ToString() = False Then
            MsgBox("There are no changes to be made at this time")
        Else
                    Try
                        MessageBox.Show(DAContacts.UpdateCommand.CommandText)
                        DAContacts.Update(ds, "RPTMGR_CONTACTS")
                        MsgBox("Database updated successfully")
                    Catch ex As Exception
                         MessageBox.Show(ex.Message)
                    End Try        
         End If
    End Sub

 

[cjard]

It's much easier to use the forms designer to do your bindings. After you have added a dataset to your project, connected it to oracle and added some queries, you simply have to drag your table icons out of Data Sources window, and onto the form. The text boxes appear all correctly bound and with example code on how to load and save the data.

My guess in your current approach is that youre not telling whatever object is managing the current row, that it has to end the dit and commit changes in the textbox, to the underlying datatable. I've no idea what this will be with regards to the code you've put here, because youre taking the old route of adding bindings yourself, and not using a BindingSource. You might find it's something like textbox.BindingCOntext.CurrencyManager but as I don't use this method of databinding, I cannot accurately advise

When using the Microsoft recommended way of binding your data, this is simply 2 lines of code to save:

WhateverBindingSource.EndEdit()
WhateverTableAdapter.Update(myWhateverDataSet.Whatever)


To generate this I would:
Solution Explorer..New DataSet
Set the connection string
Right Click the surface.. Add.. TableAdapter
Enter the query: "select * from rptmgr_reports"
Finish
You'll get a table called RPTMGR_REPORTS and a tableadapter

Drag them to your form
Observe that the save code already exists, written by Visual Studio

For more information, see the DW3 or DW4 links in my signature, sections about creating a simple data application..

 

[zodg19]

cjard,

Thanks for the help. I agree that using the built in databinding would be a lot easier. I am currently using VB2010 Express, which I don't think allows this with Oracle. I hope I am wrong, but everything I've read seems to indicate this.

What you said about not stopping edit mode makes a lot of sense and I will look into that more.

Thanks again for the pointers.

Scott

 

[cjard]

cjard,

Thanks for the help. I agree that using the built in databinding would be a lot easier. I am currently using VB2010 Express, which I don't think allows this with Oracle. I hope I am wrong, but everything I've read seems to indicate this.

Ugh.. Well, you can still do your databinding, because the action of adding a dataset to your project causes databindable objects to appear in the data sources window regardless of how the data gets into the dataset. True that you cannot add tableadapters to an Express generated dataset that target oracle (probably) but you can still add a dataset and make a load of tables in it that represent your database tables.

To help you do this I made a table in my oracle db:

COLUMN1 CHAR(20)
COLUMN2 CLOB
COLUMN3 DATE
COLUMN4 FLOAT(126)
COLUMN5 INTERVAL DAY(2) TO SECOND(6)
COLUMN6 INTERVAL YEAR(2) TO MONTH
COLUMN7 LONG
COLUMN8 NUMBER
COLUMN9 TIMESTAMP(6)
COLUMN10 VARCHAR2(20)

Then dragged it to my dataset (and let it make a tableadapter)
I also copied just the datatable into another dataset.
The results are in the attached project

What you can do now:
1) as a test, add one of these datasets to your project (remember that they have different namespace names) - the one with tableadapter. Does it break? OK, then we cant use tableadapters targeting oracle DBs - use the other dataset. Not break? Generated code? Neat.. move on
2) you can now get a dataset in your project. Save it all, run the custom tool (right click DS in soln explorer) - this should cause things to appear in your datasources window (may have to change dataset namespace) - play with dragging them to a form
3) look at the XML itself of the DATASET1.XSD - you get an idea for how we might make a dataset manually

Observe these oracle views:

SELECT * FROM user_tables
SELECT * FROM user_tab_cols

With a few hours work, you should be able to write queries that use these views, to make XML for you:

SELECT 
  CASE WHEN data_type = 'DATE' THEN
    REPLACE('<xs:element name="%n" msprop:Generator_UserColumnName="%n" msprop:Generator_ColumnPropNameInRow="%n" msprop:Generator_ColumnVarNameInTable="column%n" msprop:Generator_ColumnPropNameInTable="%nColumn" type="xs:dateTime" minOccurs="0" />', '%n', column_name)
  END as ds_xml
FROM
  user_tab_cols
WHERE
  table_name = 'YOUR_TABLE'

That code frag makes the XML necessary to paste into a dataset for a date column.. add the other stuff, then you can:

SELECT * from my_ds_xml_maker_view WHERE table_name = 'MY_TABLE'

get a block of XML, paste it into a new, empty dataset (add a dummy table then replace its contents in the xml back end)


-> i did a table with all the common datatypes for you so you could know what XML is needed

So this is how you can quickly and easily make a dataset that matches your oracle database tables - youre effectively writing your own dataset generator because the provided express one doesnt do oracle


-

Now you will have to use DataAdapters you write yourself (or if tableadapters from step 1) work, then jsut write those into the back end xml too ) that will fill this datatable

dataAdapter.Fill(DataTable)

remmeber: the above command takes a datatable parameter, but your type specific datatable MY_TABLEDtaTable in the dataset is also jsut a datatable. When your Fill call finishes you will find the columns named will be filled with data - you get most/all (if tableadapters work) of the benefits of strongly typed datasets

----

There is of course an alternative to writing SQL that writes XML - just add the columns yourself by hand.. right click.. add column.. name it, datatype it, length etc.. This way is easier but manually doing it achieves the same end. Decide what is faster: write an SQL to write the XML for you or just doing it manually