A lot of people ask how to update a record from a bound grid using a dialogue. The process is so easy it's laughable, but most people don't realise because they don't understand how powerful data-binding is. Following are instructions on how to update a row from a DataGridView bound to a DataTable using a dialogue. I've also attached a working sample but I strongly suggest that you follow the instructions and create your own project to get a better feel for it.
1. Create a new Windows Forms project.
2. Add a DataGridView and a BindingSource to the form.
3. Double-click the title bar of the form to create a Load event handler and add the following code:
Now run the project and observe the data you added to the DataTable displayed in the bound grid via the BindingSource.
4. Add a new form to the project.
5. Add a Label to the form and set its Text property to "ID:".
6. Add a TextBox to the form, set its Name property to "idText" and its ReadOnly property to True.
7. Add a Label to the form and set its Text property to "Name:".
8. Add a TextBox to the form and set its Name property to "nameText".
9. Press F7 to open the code window and type "public sub new" and press Enter. That will generate a default constructor like so:
VB.NET Code:
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
End Sub
10. Add a class level variable like so:
VB.NET Code:
Private data As DataRow
11. Change the constructor from the default above to the following:
VB.NET Code:
Public Sub New(ByVal data As DataRow)
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
Me.data = data
Me.idText.Text = data("ID").ToString()
Me.nameText.Text = CStr(data("Name"))
End Sub
That means that to create an instance of this form you need to supply a DataRow, the ID and Name fields of which will be displayed in the TextBoxes.
12. Go back to the first form's code window.
13. Select DataGridView1 from the drop-down list at the top-left and CellDoubleClick from the top-right. That will create an empty handler for the grid's CellDoubleClick event.
14. Add the following code to the empty event handler:
VB.NET Code:
Dim row As DataRow = DirectCast(Me.BindingSource1.Current, DataRowView).Row
Using dialogue As New Form2(row)
dialogue.ShowDialog()
End Using
Now run the project and double-click any cell in the grid and observe the data for that record displayed in a dialogue. Note that you can click the column headers to sort the data and double-clicking will still always display the correct data. That's one of the advantages of using a BindingSource.
15. Go back to the design window for Form2.
16. Add a Button, set its Name property to "okButton", its Text property to "OK" and its DialogResult property to OK.
17. Add a Button, set its Name property to "cancelOperationButton" and its Text property to "Cancel".
18. Select the okButton for the form's AcceptButton property.
19. Select the cancelOperationButton for the form's CancelButton property.
20. Double-click the okButton to create an empty Click event handler.
21. Add the following code to the empty event handler:
VB.NET Code:
Me.data("Name") = Me.nameText.Text
That's it. You've finished the whole thing. Now you can run the project and double click a record to open it for editing in the dialogue. You can change the value of the Name property and press the OK button and observe the grid update automatically. Note also that if you edit the name field in the dialogue and press Cancel no changes are made to the grid. Finally, note that you can also add new rows and then edit them the same way.
Last edited by jmcilhinney; Oct 31st, 2008 at 02:18 AM.
Using dialogue As New Form2(row)
dialogue.ShowDialog()
End Using
Hi jmcilhinney I tred your program but i got an error. On my quoted phrase above. How can i translate it to vb.net 1.0 platform?It returns error to me.
Hi jmcilhinney I tred your program but i got an error. On my quoted phrase above. How can i translate it to vb.net 1.0 platform?It returns error to me.
Please don't ever post that you get an error without posting what the error message is. The error message is provided for a reason: to help diagnose the issue. If you want us to diagnose the issue for you then it stands to reason that you should pass on the error message.
Fortunately, in this case, I know what the problem is. Don't assume that that will always be the case:
Ok jm...thanks...this is my error on the code area..
Firstly, that is completely not the error I assumed it was, which just goes to show why you need to post your error message.
Secondly, you still haven't posted your error message. Look in the Errors window or just mouse over the wavy blue line. Otherwise we can only guess and, frankly, that's a waste of time.
I have an application that is getting the data source from an MS Access table. So I tried just creating a test application doing basically all that you have here except that in setting up the bindingsource I connect to the MS Access table to get the data.
I've got up to point 14 so far and it runs okay without errors EXCEPT that when I double click on and the dialog pops up, it isn't showing the data from the row I've clicked on, but rather always showing the data from the first row in the table. I can't see what I've done differently to your code that could be causing this.
Here is the code for the main form:
Code:
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.TblLogBindingSource.DataSource = Me.EmailDataSet.tbl_Log
Me.DataGridView1.DataSource = Me.TblLogBindingSource
Me.Tbl_LogTableAdapter.Fill(Me.EmailDataSet.tbl_Log)
End Sub
Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
Dim row As DataRow = DirectCast(Me.TblLogBindingSource.Current, DataRowView).Row
Using dialogue As New Dialog1(row)
dialogue.ShowDialog()
End Using
End Sub
End Class
And here is the code for the dialog:
Code:
Imports System.Windows.Forms
Public Class Dialog1
Private data As DataRow
Public Sub New(ByVal data As DataRow)
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
Me.data = data
Me.RepName.Text = data("Report_Name").ToString()
Me.RepDate.Text = CStr(data("Date_Sent"))
Me.RepRecip.Text = CStr(data("Email_Recipient"))
End Sub
Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
Me.DialogResult = System.Windows.Forms.DialogResult.OK
Me.Close()
End Sub
Private Sub Cancel_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel_Button.Click
Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
Me.Close()
End Sub
Private Sub Dialog1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'EmailDataSet.tbl_Log' table. You can move, or remove it, as needed.
Me.Tbl_LogTableAdapter.Fill(Me.EmailDataSet.tbl_Log)
End Sub
End Class
I have an application that is getting the data source from an MS Access table. So I tried just creating a test application doing basically all that you have here except that in setting up the bindingsource I connect to the MS Access table to get the data.
I've got up to point 14 so far and it runs okay without errors EXCEPT that when I double click on and the dialog pops up, it isn't showing the data from the row I've clicked on, but rather always showing the data from the first row in the table. I can't see what I've done differently to your code that could be causing this.
Where exactly are you double-clicking? If you're clicking on the row headers then I think you'll need to set the SelectionMode of the grid appropriately.
It was set to "RowHeaderSelect". Although I just went in and tried both "FullRowSelect" and "CellSelect" and it didn't change anything. It still just showed the values from the first row instead of the ones I've just selected.
It was set to "RowHeaderSelect". Although I just went in and tried both "FullRowSelect" and "CellSelect" and it didn't change anything. It still just showed the values from the first row instead of the ones I've just selected.
Can you zip up your project, including the MDB file, so I can take a look? Make sure you delete the bin and obj folders before zipping.
I couldn't easily do it (zip the whole thing up) as I was referring to a database in a different location and everything. Initially I tried creating a copy of it to the directory and changing things, but then I thought I'd just start another one from scratch.
This time it is all working properly. So I'm still not 100% sure what was wrong last time, but I've got it working properly in my second attempt.
Thankyou for all your help though! This was just playing around with a test database, but I think I've now worked out all of what I need to fix the proper application.
Easily resolved by dis-enabling DGV1's add/edit/delete permissions.
Why wouldn't you just check if DBNull values are being passed?
Code:
If IsDBNull(data("Name")) Then
Me.nameText.Text = String.Empty
Else
Me.nameText.Text = CStr(data("First Name"))
End If
You'd also need to change the code from
Code:
Using dialogue As New Form2(row)
dialogue.ShowDialog()
End Using
To
Code:
Using dialogue As New Form2(row)
dialogue.ShowDialog()
If row.RowState = DataRowState.Detached AndAlso dialogue.DialogResult = Windows.Forms.DialogResult.OK Then
dt.Rows.Add(row)
End If
End Using
With regard to the last two posts, if you wanted to add new rows to the grid and its underlying table using a dialogue then you would configure the grid not to display that last row by setting its AllUserToAddRows property to False. You'd then display your dialogue when the user pressed a Button or the like.
While adding new rows, it is best to do it in the following order.
1. BindingSource1.AddNew
2. BindingSource1.MoveLast
3. Then open the edit form
There's no need for the second step. AddNew returns a reference to the new DataRowView. You can either use that or get the DataRow from its Row property. The one point to note is that you'll need to call EndEdit on the BindingSource to commit the new row to the DataTable if the user OKs the dialogue.
thanks alot for this JM. it really helped me. and it is great to learn these things. I am busy on a project that is like an accounting game for kids at a school. basically I list pupils in a datagridview, I have stuff like their account balance etc. I have a deposit button and a withdrawal button as well in my datagridview. when I click the deposit button I use you code to bring up a new form, enter an amount, click ok, and the amount gets added to the balance. for withdrawals I use the same form, but the amount gets minused. such a pleasure coding it knowing it is the best method. I trust you methods 100%
Hello,
I am trying your example in VS 2010 b2, and I get this error on line
Code:
Dim row As DataRow = DirectCast(Me.BindingSource1.Current, DataRowView).Row
The error message is:
Code:
Unable to cast object of type 'VB$AnonymousType_0`5[System.String,System.String,System.String,System.String,System.String]' to type 'System.Data.DataRowView'.
The only difference with your code is that the datasource of my bindingsource is a LINQ query, like this:
Code:
Dim qryAllContacts = From contact In Db.Contacts
Select contact.ContactLastName,
contact.Address,
contact.ContactDate
The datagridview SelectionMode is set to "FullRowSelect".
Is there something I am missing, or some difference due to .NET 4.0. or to the LINQ behaviour?
Thank you very much in advance.
Hello,
I am trying your example in VS 2010 b2, and I get this error on line
Code:
Dim row As DataRow = DirectCast(Me.BindingSource1.Current, DataRowView).Row
The error message is:
Code:
Unable to cast object of type 'VB$AnonymousType_0`5[System.String,System.String,System.String,System.String,System.String]' to type 'System.Data.DataRowView'.
The only difference with your code is that the datasource of my bindingsource is a LINQ query, like this:
Code:
Dim qryAllContacts = From contact In Db.Contacts
Select contact.ContactLastName,
contact.Address,
contact.ContactDate
The datagridview SelectionMode is set to "FullRowSelect".
Is there something I am missing, or some difference due to .NET 4.0. or to the LINQ behaviour?
Thank you very much in advance.
Axplains
If you haven't bound your BindingSource to a DataView then the items aren't DataRowViews, so you can't cast the Current item as that type. You have to cast the item as the type it is. In your case your item is an anonymous type, so you can't cast at all. I would suggest that you actually define a type for items. That way you can cast your items as that type and access its properties directly, which you can't do with an anonymous type.
That would be an issue preventing me using LINQ at all, if many cases like that show up... or is there another way to accomplish that?
By "define a type for items" you mean that I declare something like:
Code:
Public Class Contact
Public ContactLastName As String
Public Address As String
Public ContactDate as Date
End Class
at class level? Instead of "
Code:
Private data As DataRow
"?
And how would I pass it to the second form?
Thanks a lot for your patience, still a newbie experimenting with LINQ and VS 2010.
I don't mean at the class level because this type is a class itself, so it belongs in its own code file, just like other classes.
This is no impediment to using LINQ. You simply adjust your Select clause to create instances of an explicit type rather than an anonymous type, e.g. this:
vb.net Code:
Select New Person With {.FirstName = row.Field(Of String)("FirstName"), .LastName = row.Field(Of String)("LastName")}
instead of this:
vb.net Code:
Select New With {.FirstName = row.Field(Of String)("FirstName"), .LastName = row.Field(Of String)("LastName")}
The only difference is that you specify the type of the new object you want to create instead of letting LINQ define an an anonymous type on demand.
You pass these objects to the dialogue exactly as I've shown. Continuing with my example above, the Current property of the BindingSource will now return a Person object, so you cast it as that type and then pass that object to the dialogue. The dialogue has to be designed to accept a Person object rather than a DataRow object. You first decide on the type and then you write the rest of the code around that. The code follows exactly the same pattern no matter the type; only the details change.
Oops, I already stumbled on that.
In the first form, I modified my LINQ query this way to instance an explicit type:
VB Code:
qryAllContacts = From c In Db.Contacts
Select New Contact With {.ContactLastName = c.ContactLastName,
.Address = c.Address,
.ContactDate = c.ContactDate}
Me.BindingSource1.DataSource = qryAllContacts
It is different from your example, because "row" is still not declared at this point, and your syntax would give an error: in your example, "row" is only declared in the DataGridView "CellDoubleClick" event.
For example, if I write:
VB Code:
qryAllContacts = From c In Db.Contacts
Select New Contact With {.ContactLastName = row.Field(Of String)("ContactLastName")}
I get errors like:
"Name 'row' is either not declared or not in the current scope".
Anyway, with my syntax, on this line:
VB Code:
Me.BindingSource1.DataSource = qryAllContacts
I get this error:
"Explicit construction of entity type 'Myprogram.Contact' in query is not allowed."
If I create a separate "Contact" class, I get errors in it, like:
"'ContactLastName' is already declared as 'Public Property ContactLastName As String In this Class'"
I think this is because the "Contacts" table is already mapped to the relative object in the ".dbml" file.
I am probably missing something else about the LINQ query definition.
Thanks again if you can help me learning...
Last edited by axplains; Jan 27th, 2010 at 04:26 AM.
I'm having the same problem that kettlch had encountered.
Here is my code for form1
Code:
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.BindingSource1.DataSource = dt
Me.DataGridView1.DataSource = Me.BindingSource1
connStr.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;" & _
"Data Source = C:\Leads Project\Leads Client System.accdb"
connStr.Open()
dataAdapter = New OleDb.OleDbDataAdapter("Select * From LeadsClientSystem", connStr)
commandBuilder = New OleDb.OleDbCommandBuilder(dataAdapter)
End Sub
Code for Form2
Code:
Public Sub New(ByVal data As DataRow)
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
Me.data = data
Me.txtCompanyName.Text = (data("Company_Name").ToString)
Me.txtArea.Text = (data("Area").ToString)
Me.txtBusinessAddress.Text = (data("Business_Address").ToString)
Me.txtNatureOfBusiness.Text = (data("Business_Nature").ToString)
Me.txtYearsInBusiness.Text = (data("Years_in_Business").ToString)
Me.txtCustomerType.Text = (data("Customer_Type").ToString)
Me.txtEmailAddress.Text = (data("Email_Address").ToString)
Me.txtFaxNumber.Text = (data("Fax_Number").ToString)
Me.txtFirstName.Text = (data("Contact_First_Name").ToString)
Me.txtLastName.Text = (data("Contact_Last_Name").ToString)
Me.txtPhoneNumber.Text = (data("Contact_Number").ToString)
Me.txtPosition.Text = (data("Contact_Person_Position").ToString)
Me.txtID.Text = (data("ID").ToString)
Me.txtWebsite.Text = (data("Website").ToString)
Me.txtOtherBusinessInfo.Text = (data("Other_Business_Info").ToString)
End Sub
Then at my Datagrid, I added a button for editing. Which when pressed, brings up the edit form.
Code:
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
Dim dgv As DataGridView = CType(sender, DataGridView)
'EDIT BUTTON
Dim id As String
id = dgv(2, e.RowIndex).Value.ToString
If e.ColumnIndex = 9 Then
If id = "" Or id = String.Empty Then
Exit Sub
Else
Dim row As DataRow = DirectCast(Me.bs.Current, DataRowView).Row
Using dialogue As New frmCashAdvanceRegistrationvb(row)
dialogue.ShowDialog()
End Using
End If
Now on the Edit Form.
Code:
Private data As DataRow
Public Sub New(ByVal data As DataRow)
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
data = data
txtDate.Text = data("Name").ToString
txtWhere.Text = data("Id").ToString
txtWhy.Text = data("Address").ToString
All is good up to here. Good and Running.
Until i pressed the Ok Button.
On the OK button, to save the changes. I used this code:
Private data As DataRow
Public Sub New(ByVal data As DataRow)
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
data = data
txtDate.Text = data("Name").ToString
txtWhere.Text = data("Id").ToString
txtWhy.Text = data("Address").ToString
You're just assigning the parameter value back to the parameter. You never assign anything to the 'data' member variable and that's why it's Nothing when you try to use it later. You need to qualify the member variable to distinguish it from the parameter/local variable with the same name:
Code:
Private data As DataRow
Public Sub New(ByVal data As DataRow)
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
Me.data = data
txtDate.Text = data("Name").ToString
txtWhere.Text = data("Id").ToString
txtWhy.Text = data("Address").ToString
I have created this example on my computer and it is working great. I do have a simple question that I don't seem to understand. Under the okButton on the 2 form where :
Code:
Me.data("Name") = Me.nameText.Text
Why does the private datarow variable "data" from the 2 form save the value back to the datagridview on the 1 form? I have been studying class code and client code and I would have thought the Client code would have to somehow call the datarow?
Why does the private datarow variable "data" from the 2 form save the value back to the datagridview on the 1 form?
The grid on the first form is bound to a DataTable that contains DataTows. When you open the dialogue, it is one of the DataRows that is passed in and assigned to that data field. It is the same DataRow in both foems so any change made in one for will be reflected in the other. That's the whole point of this thread.
Consider it like this. Let's say that you are a parent and you have several children and you dress them all in blue shirts. One of your children gets marrieds and their spouse dresses them in a red shirt. If you had a family dinner, would you be surprised to see one of your children wearing a red shirt? Would you ask how your daughter- or son-in-law dressing a person in a red shirt could possible affect your family? Of course not, because the person who is your child and the person who is their spouse is the same person. You're just referring to that same person in two different ways.
The DataRow in this example is exactly the same. It is part of the DataTable in the first form and it is also referred to by the data field in the second. That's how objects work in the real world and that's how objects work in OOP. It's almost like it was designed that way on purpose.
Last edited by jmcilhinney; Jan 4th, 2021 at 11:38 PM.
I had the same interrogation mostly because you wrote byval in the new sub :
Code:
ByVal data As DataRow
But I think the mistake in my understanding was to mix the object datarow by itself and the contains of the datarow (the values).
So you pass the container of the values, not just the values. Am I understanding it correctly ?
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
I got it! The problem that I was having was I set a watch on the data object and the row object. I followed the values all the way through until the hand off back to the first form. At that time the data object and row objects went out of scope and the new values just appeared in the datagridview. I did not see it until I went back and added the Me.BindingSource1.Current to my watch list. Then I could see the values in the data and Me.BindingSource1.Current at the same time. Again thank you for the help.
I had the same interrogation mostly because you wrote byval in the new sub :
Code:
ByVal data As DataRow
But I think the mistake in my understanding was to mix the object datarow by itself and the contains of the datarow (the values).
So you pass the container of the values, not just the values. Am I understanding it correctly ?
ByVal means that a copy of the contents of the original variable is created and passed to the method. If the parameter is a reference type, i.e. a class, then the contents of the variable is a reference. If you create a copy of a reference then you have two references that both refer to the same object. Passing a method parameter by value is no different than assigning to another variable:
vb.net Code:
Dim c1 As New SomeClass
Dim s1 As SomeStructure
Dim c2 = c1
Dim s2 = s1
In that code, the c1 variable contains a reference to a SomeClass object and the s1 variable contains a SomeStructure object. When those two variables are assigned to two other variables, copies of their contents are made. The c2 variable contains a copy of the reference contained in the c1 variable, so there are two references referring to the same object, i.e. there is only one SomeClass object. The s2 variable contains a copy of the object contained in the s1 variable, so there are two SomeStructure objects. That is how reference types and value types work and that is exactly how passing method parameters by value works.