Update Datagridview

[penno90]

Hi all

i am new to the forum and am really hoping that someone can help me with a problem that has been driving me nuts for two weeks now!

I am trying to make a little app with vb.net with an access database backend.

I have a form with a datagridview showing customers. When a user double clicks a row a new form opens with details of the selected customer.
In this form the user can edit details and then save.
Upon saving I have written the code to update the datagridview in the first form.
This is working fine, but ONLY if the datagridview form is set as the Startup form of my application. I have no idea why this is????

Here is the code to save my edit details form and update my datagridview form

Private Sub btnSaveandClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveandClose.Click

        Me.Validate()
        Me.TblCustBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.JamestestDataSet)
        DGVForm.TblCustTableAdapter.Fill(DGVForm.JamestestDataSet.tblCust)
        Me.Close()

End Sub

This is the code to open the edit details form

Private Sub TblCustDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles TblCustDataGridView.CellDoubleClick

        Dim F2 As New Form2()
        F2.Show()

        F2.TblCustBindingSource.Position = Me.TblCustBindingSource.Position

    End Sub

If the datagridview form is not set as the Startup form I have to close it and then reopen it for it to show the changes that I have made in the edit form.

Any help/ideas/suggestions greatly appreciated!!!

 

[JaedenRuiner]

Hi all
I have a form with a datagridview showing customers. When a user double clicks a row a new form opens with details of the selected customer.
In this form the user can edit details and then save.
Upon saving I have written the code to update the datagridview in the first form.
Any help/ideas/suggestions greatly appreciated!!!

I'm not precisely sure where the issue is at the moment, but granted, I've got A Details View and DGV View on the same form for the same tables recurrently, and they are always insync with each other.
As a Personal recommendation, based on what I gathered, is that you have a Main Form that Shows your table in a DataGridView. Then you DblClick on a row and a "Dialog" opens allowing you to edit that particular row.

First: I would suggest treating the Second form as a Dialog instead of just another form, and what I often do in that situation is Shadow the ShowDialog(owner as System.Windows.Forms.IWin32Window) function and add my own additional parameters, for this case I would recommend the additional parameter to be of the strongly typed "DataRow" that you select from the BindingSource on your DataGridView.

a Simple Code Design:

[u]MainForm[/u]
Private Sub dgv_RowHeaderMouseDoubleClick(<...snip...>)
  dim frm2 as new MyRowEditorForm()
  dim row as JamestestDataset.tblCustRow =  _
     DirectCast(TblCustBindSource.Current, DataRowView).Row
  if frm2.ShowDialog(me, row) = DialogResult.OK then
     me.TblCustBindingSource.EndEdit()
     Me.TableAdapterManager.TableAdapter_tblCust.Update(row)
  else
     Me.tblcustbindingsource.CancelEdit()
     Me.JamestestDataSet.RejectChanges() 'this you would use the variable
                   ' name for your dataset, not necessarily the type name
  end if
end sub

[u]MyRowEditorForm[/u]
public Shadows Function ShowDialog(owner <..snip..>, ByRef row as JamestestDataSet.tblCustRow) as DialogResult
  dim res as DialogResult
  Me.MyDetailBindSource.DataSource = Row
  res = MyBase.ShowDialog(owner)
  return res
end Function

What that does is basically directly work with the row you've selected instead of filling a whole 'nother dataset in the second form. when working with datasets i personally have found them to be somewhat persnickety, especially when dealing with multiple instances of a dataset pointing to the same database the concurrency is a pain to maintain. This way, you are keeping one dataset (in the mainform with the grid) and then you are only passing the necessary data to the "Details Form" for editing. If the user clicks save, you return dialogresult.ok, and the Grid Form handles the update of only that 1 row that was changed.

Give it a try, and if it doesn't work, well, i'll need more code to investigate the problem.

Cheers

 

[penno90]

Hi Jaeden

Thanks so much for your reply. My real problem is that data is only updated to my DGVform upon closing my edit form if it is set as the Startup form of my project.

Since I want to use another form as my startup form this is really annoying!

I really appreciate your advice and the code you posted. I have started trying to use your method too and see how i get on but I seem to have a couple of issues...

MyRowEditorForm
public Shadows Function ShowDialog(owner <..snip..>, ByRef row as JamestestDataSet.tblCustRow) as DialogResult
  dim res as DialogResult
  Me.MyDetailBindSource.DataSource = Row
  res = MyBase.ShowDialog(owner)
  return res
end Function

what should go in the (owner <..snip..> ?

also on my datagridview form I am getting the following errors

Value of type 'DGVtest.Form1' cannot be converted to 'DGVtest.bd1DataSet.ClientesRow'.

and

Too many arguments to 'Public Shadows Function ShowDialog(ByRef row As bd1DataSet.ClientesRow) As System.Windows.Forms.DialogResult'.

my code for form one is

[CODE]Public Class Form1

    Private Sub ClientesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.Windows.Forms.IWin32Window) Handles ClientesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ClientesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Bd1DataSet)

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Bd1DataSet.Clientes' table. You can move, or remove it, as needed.
        Me.ClientesTableAdapter.Fill(Me.Bd1DataSet.Clientes)

    End Sub


    Private Sub ClientesDataGridView_RowHeaderMouseDoubleClick1(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles ClientesDataGridView.RowHeaderMouseDoubleClick
        Dim frm2 As New Form2()
        Dim row As bd1DataSet.ClientesRow = _
           DirectCast(ClientesBindingSource.Current, DataRowView).Row
        If frm2.ShowDialog(Me, row) = DialogResult.OK Then
            Me.ClientesBindingSource.EndEdit()
            Me.TableAdapterManager.ClientesTableAdapter.Update(row)
        Else
            Me.ClientesBindingSource.CancelEdit()
            Me.Bd1DataSet.RejectChanges() 'this you would use the variable
            ' name for your dataset, not necessarily the type name
        End If
    End Sub
End Class

[/CODE]


and edit form

Public Class Form2
    Public Shadows Function ShowDialog(ByRef row As bd1DataSet.ClientesRow) As DialogResult
        Dim res As DialogResult
        Me.ClientesBindingSource.DataSource = row
        res = MyBase.ShowDialog(Owner)
        Return res
    End Function


    Private Sub ClientesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClientesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ClientesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Bd1DataSet)

    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Bd1DataSet.Clientes' table. You can move, or remove it, as needed.
        Me.ClientesTableAdapter.Fill(Me.Bd1DataSet.Clientes)

    End Sub
End Class

very new to VB so sorry if i am missing something obvious?

 

[JaedenRuiner]

what should go in the (owner <..snip..> ?

My Apologies. I have done this so many times, but ShowDialog() is a base Method of the Form Class, so when I shadow it, I usually use the Code Completion to check that before overriding it. I listed it before in my post but not in the code window.

also on my datagridview form I am getting the following errors
Value of type 'DGVtest.Form1' cannot be converted to 'DGVtest.bd1DataSet.ClientesRow'.
...
Too many arguments to 'Public Shadows Function ShowDialog(ByRef row As bd1DataSet.ClientesRow) As System.Windows.Forms.DialogResult'.

The above is about your definition of ShowDialog, since you only left one parameter in your declaration:

    Public Shadows Function ShowDialog(ByRef row As bd1DataSet.ClientesRow) As DialogResult
        Dim res As DialogResult
        Me.ClientesBindingSource.DataSource = row
        res = MyBase.ShowDialog(Owner)
        Return res
    End Function

Trying to call the function with two Parameters:

If frm2.ShowDialog(Me, row) = DialogResult.OK Then

Will cause those errors. If you Only Execute ShowDialog(row) with your definition, it would work, however, in your ShowDialog you are passing a parameter to the MyBase.ShowDialog(Owner) that doesn't exist in that function so you would get an error there as well. To fix both issues as you have it now simply update the definition of ShowDialog to:

public Shadows Function ShowDialog(owner as System.Windows.Forms.IWin32Window, _ 
              ByRef row As bd1DataSet.ClientesRow)
end function

Form 1 looks standard, but looking at Form2 (the Edit Form) it isn't exactly what I would expect. It appears you are trying to replicate the same behavior as with form 1.
so we need some details here:

• Form1 (the DGVForm) Loads the Dataset and can Navigate the Records with a Navigator Strip. Will Form2 also navigate from row to row, or does it only work with ONE row at a time?
• The Automatic Set ups written in by VB can be helpful, but they often times do get in the way.

If you look at the two functions below:

Public Class Form2
    Private Sub ClientesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles ClientesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ClientesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Bd1DataSet)

    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Bd1DataSet.Clientes' table. 
        'You can move, or remove it, as needed.
        Me.ClientesTableAdapter.Fill(Me.Bd1DataSet.Clientes)
    End Sub
End Class

You will realize they are superfluous, because you are already filling the Dataset in DGVForm. Form2 (EditForm) should be very Simple in design:
TextBox, ComboBox, NumericUpDown, MaskedTextBox, Button, and 1 binding source.

A habit that I use frequently (because it is quicker than designing the form manually) is:

1. Create the Form.
2. Drag the "Details" view from the DataSources over to the form.
   • This not only creates all the controls for the individual columns of the recordset, but also creates...
   • TypedRecordset (I think you have it as Bd1Dataset)
   • TableAdapterManager
   • Typed TableAdapter (ClientesTableAdapter)
   • Binding Source
3. Delete TypedRecordset, Adapter, and Adapter Manager

What I use this method here for is to Initialize all the DataBindings on my Controls (textboxes and comboboxes etc) and when I DELETE the TypedRecordSet, TableAdapterManager, and Typed TableAdapter, the bindings remaining to the BindingSource, which are automatically resolved when you assign your ClientesRow to the BindingSource.DataSource in ShowDialog()
The Reason:
When You load up the table in the DGVForm you already have all the Dataset material loaded and manipulated the way you want it. When you want to edit that data, by way of the showdialog(Me, MyRow) method I described earlier, you are directly passing all the data the EditForm needs in that Single Row, and by setting the DataSource of the EditForms' BindingSource you link in all the Textboxes and other UI Controls back into that Singular Row. You can Change, and effect everything, and when the Save(OK)/Cancel button is clicked they can return the DialogResult value back to the DGVForm which thjen handles all the Saving/Updating of the Dataset: The Edit form has no business trying to update the dataset, that is controled in the DGVForm.
That Being Said:

My real problem is that data is only updated to my DGVform upon closing my edit form if it is set as the Startup form of my project.

I think this problem may be resolved by handling the suggestions above. However, If you do wish to also "Navigate" on the Edit form, not just edit the singular record, then it gets more complicated, so hopefully that is not the case.

Keep me Posted.

 

[penno90]

Hey Jaeden

I made the changes and got it working!

I did figure that the dataset, table adaptor etc were unnecessary on Form2. I just knocked up that program last night to try your code and left them there cos I thought they wouldn't do any harm.

I had been planning to 'Navigate' on the edit form, but this is really not important if it's going to be complicated. I had been basing my application on Microsoft's Office Accounting, where they have a customers datagridview form and when you double click a record it opens an edit form. This edit form is NOT a dialog though and records can be navigated. Also the form can be opened independently of the the datagridview form from the main window of the program. Still, I guess trying to copy this in my first VB project is over ambitious and the functionality is not even that necessary for my needs.

However, what i do want to be able to do is have another datagridview in Form2 to show Orders for the selected customer, and then be able to open from that OrderDetails. Is this something that you think would be possible using you method? If so I will try some more experimentation here!!

Thanks again for your help. I think I understand the code now and really appreciate that you took the time to explain it in a way for a newbie to understand!

Cheers

James

 

[JaedenRuiner]

No Problem.

In fact, if you want the navigation and/or the ability to display "Child" tables to the current row, that's a simple change. Instead of passing the Row to ShowDialog, Pass the Entire DataSet.

The DataBindings to the individual controls are set as they were before, so for the specific "customer" table, you don't have to worry about that working, Except you would have to update the ShowDialog as Follows:

public Shadows Function ShowDialog(owner as System.Windows.Forms.IWin32Window, _ 
              ByRef ds As bd1DataSet, byval position as integer)
        Dim res As DialogResult
        Me.ClientesBindingSource.DataSource = [u]ds[/u]
        Me.ClientesBindingSource.DataMember = [u]"ClientesTableName"[/u]
        if position < Me.ClientesBindingSource.Count then 
           Me.ClientesBindingSource.Position = position
        End if
        res = MyBase.ShowDialog(Owner)
        Return res
end function

'To Call The Function From DGVForm

EditForm.ShowDialog(Me, Dataset, ClientesBindingSource.Position)

Now, with that, the Datasource is the DataSet, and the DataMember is the TableName. There would be the initial navigation Limitation, meaning that now that you are looking at the entire table you have to tell your form which row you are at. That's the Position value which you pass the Position fromt he DGV Binding Source.

To Add the Child DGV on the Edit Form, you would need to two three things:

1. First, Find the Relationship Name that links Customers to Orders, and it will most likely be something like FK_Order_customers or something like that. If you haven't defined a relation ship between the two table in your DataSet, do that, and name the foreign Key FK_* where * is something significant to the relationship.
2. Add a BindingSource, Bind_ChildOrders or something like that
3. Add A DataGridView and set it's DataSource to Bind_ChildOrders

Now in your ShowDialog() Method you would add these lines:

'add these after the ClientesBindingSource lines
DataGridViewOrders.AutoGenerateColumns = True
Bind_ChildOrders.DataSource = ClientesBindingSource
Bind_ChildOrders.DataMember = "FK_RelationShipNameBetweenCustomersAndOrders"
res = ShowDialog(Owner)

If you've defined your relationship correctly in the DataSet Designer, the understanding of the programmatic links are simple:

• ClientesBindingSource Gets its Source From the dataset, selecting the specific Table
• Bind_ChildOrder gets its Source From ClientesBindingSource, selecting specifically the Relationship, which from Parent to Child is always bound per row. So the Current Row will only result in X # of child records.

Play around with it is definitely the best advice I can give...that and Keep a browser open to MSDN, and as many VB sites as you can.

Cheers

 

[penno90]

HI Jaeden

Have just started playing around with my project tonight and wondered if I could pick your brain a couple more times?!

I got my edit form working with the navigation which is awesome. I just wanted to know though since the line

Me.TableAdapterManager.ClientesTableAdapter.Update(row)

no longer works i am updating with

Me.TableAdapterManager.UpdateAll(Me.Bd1DataSet)

Presumably this is a slight waste of resources updating the whole dataset instead of just the edited row. Is there another I should be doing this?

Also, although I can navigate and also delete records in my edit form now I cannot add records. When I click the addnew button on the binding navigator it brings up a fresh record and i can input information, but when i close the edit form the record is not added to the DGV form or the database.

Once i have got this nailed i'll have a go with the child forms!

Cheers

james

 

[cjard]

Me.TableAdapterManager.ClientesTableAdapter.Update (row)

no longer works i am updating with

What does "no longer works" mean?

if you only update a single row, you'll lose other edits to other rows in the table.. I've done an attachment example of a way you can achieve what youre doing.. Not the way i'd do a parent/child relation in an app but..

 

[penno90]

Hi cjard

Thanks so much for your code. It's always nice to see another way of doing things. I seem to have run into some trouble with the save button on the binding navigator on the editorForm though. When I click it I get the error message-

Unable to cast object of type 'System.Windows.Forms.BindingSource' to type 'TestDataTable'.

on the following line

Me.TestTableAdapter.Update(DirectCast(TestBindingSource.DataSource, TestDataSet.TestDataTable))

If I change the line to

Me.TableAdapterManager.UpdateAll(TestDataSet)

it seems to work ok.

The other issue is when I addnew on the editor form and then close back to the datagridview form the new line is added but, in order for the details of the line to show I have to click onto another line and then back to the new one.

Do you have any idea why this is happening?

 

[cjard]

Sorry, the line with the error is a remnant code from my previous answer to this thread..

Instead of telling you the answer, i will teach why the error occurs.

EditorForm takes a BindingSource as a parameter, the same BS as the parent DGV uses. EditorForm's own BindingSource is set to use the supplied BindingSource, in the constructor. BIndingSources can hence be chained:

EditorForm:IDTextBox --> EditorForm:BindingSource --> GridForm:BindingSource --> DataSet

Now, because the bindingsource on editorform is using a bindingsource as its data source, it is NOT using a TestDataTable directly. See the above mapping..

BS-->BS-->TDT

So we cannot cast the second BS into a TDT, and this is what the error message is advising us. Instead, we must cast it into a BS, then retrieve the real TDT out of its DataSource


Are you confused?

Here's another example:

Your name is Joe. You have a friend, Sam. Sam has a friend, James.

We cannot say:
Cast You.Friend Into James

Because your friend is not James. Your friend is Sam. We must say:

Cast (You.Friend Into Sam).Friend Into James

Now you have access to james!


-

What's all this deal with Casting? Well .Datasource can be many different types of things: a DataRow, A DataTable, A DataView, An IBindingList.. too many possibilities.. So MS just made it as generic as possible, so it's of type Object. When we say:

Dim o as Object = New Button

It works, but o, even though it is a button, is wearing the clothes of its parent type.. object. So if we say o. and wait for Intellisense menu, all we see is what is available on an Object, not a Button. You and I both know its really a button inside an object wrapper, but VB doesnt know.

Dim b as Button = DirectCast(o, Button)
...gives us the Button.. It unwraps the object/takes off the parent clothes.

Hence:

BindingSourceA.DataSource is actually BindingSourceB, and looks like an Object
BindingSourceB.DataSource is actually the TestDataTable we really want, but again, it looks like an Object

Can you now have a go at the code to fix the error?

 

[cjard]

The other issue is when I addnew on the editor form and then close back to the datagridview form the new line is added but, in order for the details of the line to show I have to click onto another line and then back to the new one.

Do you have any idea why this is happening?

It's some sort of visual corruption by the DataGridView itself. You can get the data so show simpyl by dropping another window over the relevant line and then moving it away. As to why it happens, I'm not sure. Can I suggest though, that you keep things simple and related. If your editorform is for editing an existing entry, dont make it able to add new entries. Eventually youre going to fill your UI up with so many controls, the user wont know what to click where, to achieve their goal. Keep it simple

 

[JaedenRuiner]

As a little addendum to cjard, look into the TypeOf operator.

I do a lot of nested Binding Sources as he listed: bs1->bs2->dataset

and for many of the situations that use them sometimes bs1 points to dataset and other times bs1 points to bs2 so, for example, i'm creating a Sort/Filter for the active BindingSource, but i'm not sure if the bindingsource is direct or indirect so I do this:

Public Sub FilterMenu_Click(byval Sender As Object, byval e as EventArgs) _
                   handles filtermenuitem.click
dim bind as BindingSource = bind_tbls
if TypeOf bind.Datasource is BindingSource then 
         bind = DirectCast(bind.datasource, BindingSource)
end if
bind.filter = <filtercode here>
end sub

The TypeOf operation is a wonderful help to understanding Types at run-time.

 

[penno90]

Messieurs Jaeden and Cjard

Thanks so much for your help, after a couple of nights feeling bemused, flummoxed and confused I do believe I have finally cracked it!

I have made a kind of Frankenstein's monster, splicing together bits of both of your codes. Unfortunately, I couldn't figure out your hints Cjard, but realized that as Jaeden had said...

The Edit form has no business trying to update the dataset, that is controled in the DGVForm.

So I have stuck with his solution of using a dialog for the edit form but Cjard's technique of passing the binding source. DGV form is as follows...

Private Sub ClientesDataGridView_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ClientesDataGridView.CellDoubleClick
        Dim frm4 As New Form4(ClientesBindingSource)
If frm4.ShowDialog() = DialogResult.OK Then
            Me.ClientesBindingSource.EndEdit()
Me.ClientesTableAdapter.Update(Me.Bd1DataSet.Clientes)
            Me.ClientesDataGridView.Refresh()
        Else
            Me.ClientesBindingSource.CancelEdit()
            Me.Bd1DataSet.RejectChanges()
 Me.ClientesDataGridView.Refresh()
        End If
End Sub

and edit form...

Public Sub New(ByVal toEdit As BindingSource)
InitializeComponent()
ClientesBindingSource.DataSource = toEdit
ClientesBindingSource.Position = toEdit.Position

By the way, calling DataGridView.Refresh() resolves the issue of freshly added lines not appearing properly in the DGV too.

So that's it for now. thanks again, and expect a new thread soon when i hit the next the hurdle!

cheers

james