Question Find RowIndex on filtered datagridview

Mimosa777

Active member
Joined
Aug 28, 2014
Messages
28
Programming Experience
1-3
Hello everyone,
I have an access db with 2 tables (CUSTOMERS and CARS) with Parent-Child relationship (1 to many). Here is an example of my tables :
VB.NET:
Table CUSTOMERS
ID Name   
1 XXX   
2 YYY   
3 ZZZ   
    
TABLE CARS    
ID Customer_ID Brand Model Color
1 1                 BMW      X6       Black
2 2                Mazda     3         Red
3 2                Ford      Fusion    white
In a windows form, I have one datagridview(dgCustomers) binding to the Customers table using a dataset and data adapter. Then, I have that second datagridview(dgCars) also binding to the CARS table using dataset and data adapter. dgCars is fill based on a rowfilter on CUSTOMER_ID as you can see in my code :
VB.NET:
Private Sub dgCustomers_SelectionChanged(sender As Object, e As EventArgs) Handles dgCustomers.SelectionChanged
            ds.Tables("Cars").DefaultView.RowFilter = "Customer_ID = " + dgCustomers.CurrentRow.Cells("ID").Value.ToString
            dgCars.DataSource = ds.Tables("Cars")
    End Sub
So, when I want to update some cells in dgCars datagridview, I select the row and all cells info are captured in textboxes. On a button_click, data are updated in the DB accordingly and here is my code :
VB.NET:
Dim rowCount As Integer
Dim i As Integer
rowCount = dgCars.CurrentRow.Cells.Count
For i = 1 To rowCount - 1
ds.Tables("Cars").Rows(dgCars.CurrentRow.Cells(i).RowIndex).Item("Brand") = tbBrand.Text
ds.Tables("Cars").Rows(dgCars.CurrentRow.Cells(i).RowIndex).Item("Model") = tbModel.Text
ds.Tables("Cars").Rows(dgCars.CurrentRow.Cells(i).RowIndex).Item("Color") = tbColor.Text
Next
daCars.Update(ds, "Cars")
So whenever i select a row in dgCars to update, the rowindex of that currentrow is always 0 (if the customers has only one car, then 1 if he has 2 cars, etc..) which i understand.
My problem with my code is that everytime i update let say the Ford, all the changes are updated on the BMW because of the rowindex in the table that is 1
so how can i fix this so that the rowindex of my currentcell is the rowindex of the table and not the rowindex of the dgCars. If i had no filter on dgCars, i would not have this problem.
I hope i was clear enough - thank you all for your help on this. (Still learning :)
 

Mimosa777

Active member
Joined
Aug 28, 2014
Messages
28
Programming Experience
1-3
Found the solution - basically I needed to use the dataItemBound proprety and cast properly. for those who are interested :
VB.NET:
For i = 1 To rowCount - 1
   With DirectCast(dgCars.CurrentRow.DataBoundItem, DataRowView).Row
       .Item("Brand") = tbBrand.Text
       .Item("Model") = tbModel.Text
       .Item("Color") = tbColor.Text
   End With
Next
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Actually, what you should be doing is using a BindingSource in the first place. The BindingSource was created to pool all operations related to data-binding in one place. You bind the DataTable to a BindingSource and you bind the BindingSource to the grid. You then use the Current property of BindingSource to get the item bound to the current row. The Current property also works if you have bound your data to a single set of individual controls too, which your method wouldn't work for.

The code you've posted doesn't really make sense though. You appear to be looping through the rows of the grid yet you use the grid's current row in every iteration. Surely you want to either affect each row of the grid using the loop or just affect the current row and not use a loop at all.

By the way, there's no need to get DataRow behind the DataRowView. You can edit the data via the DataRowView directly. How do you think data entered into the grid gets into the DataTable?
Dim row = DirectCast(myBindingSource.Current, DataRowView)

row("Brand") = tbBrand.Text
row("Model") = tbModel.Text
row("Color") = tbColor.Text
 

Mimosa777

Active member
Joined
Aug 28, 2014
Messages
28
Programming Experience
1-3
Actually, what you should be doing is using a BindingSource in the first place. The BindingSource was created to pool all operations related to data-binding in one place. You bind the DataTable to a BindingSource and you bind the BindingSource to the grid. You then use the Current property of BindingSource to get the item bound to the current row. The Current property also works if you have bound your data to a single set of individual controls too, which your method wouldn't work for.

The code you've posted doesn't really make sense though. You appear to be looping through the rows of the grid yet you use the grid's current row in every iteration. Surely you want to either affect each row of the grid using the loop or just affect the current row and not use a loop at all.

By the way, there's no need to get DataRow behind the DataRowView. You can edit the data via the DataRowView directly. How do you think data entered into the grid gets into the DataTable?
Dim row = DirectCast(myBindingSource.Current, DataRowView)

row("Brand") = tbBrand.Text
row("Model") = tbModel.Text
row("Color") = tbColor.Text

Hi there,

i realised that the for loop was of no use so i took it out already. As you mentionned, i just want to affect the current row and not use a loop at all.

Now regarding the databinding source, i tried to do what im doing right now using binding source but i could not figure out the proper way to do it. Seems much simpler than what im doing right now using datasets, filtering one datagridview, etc.. I will look into that again in some documentation and try harder.

Thanks jmc for your advices.

Cheers :stung:
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Using a BindingSource does not mean not using a DataSet. It's just a bridge between a data source, which may be a DataSet or may be something else, and the UI that provides a single place for all data-binding operations. You still retrieve that data in exactly the same way, into a DataTable that may or may not be in a DataSet, and you still save the data in the same way. You use the BindingSource whenever you want to access the bound data though, for navigation, inserting, updating and deleting.
 

Mimosa777

Active member
Joined
Aug 28, 2014
Messages
28
Programming Experience
1-3
Hi,

so I've been updating my code to use binding source as you suggested which I also believe will make it lot easier for me to update my data in the future.
everything looks good when I select a row in datagrid, values are automatically showing in textboxes using databindings. but i'm having an issue with the update. When I change something in the textboxes, I can see the change right away in the datagrid which is great, but after closing my app and opening it again, the changes are gone. Please look at my code and let me know what im doing wrong :
VB.NET:
Dim con As New OleDb.OleDbConnection
Dim daCustomers As OleDb.OleDbDataAdapter
Dim sqlC As String
Dim bs_customer As New BindingSource()
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\Amine\Documents\Servauto Software\DB\dbServauto.mdb"
        con.Open()
        sqlC = "SELECT * from tblCustomers"
        daCustomers = New OleDb.OleDbDataAdapter(sqlC, con)
        daCustomers.Fill(ds, "Customers")
        bs_customer.DataSource = ds
        bs_customer.DataMember = ds.Tables(0).TableName
        con.Close()
        dgCustomers.DataSource = bs_customer
End sub
and here is the code for my save button and NOT working at this time :
VB.NET:
Private Sub btnSaveClient_Click(sender As Object, e As EventArgs) Handles btnSaveClient.Click
        Dim cb As New OleDb.OleDbCommandBuilder(daCustomers)
        daCustomers.Update(ds, "Customers")
end sub
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Firstly, while what you have done will work, you shouldn't really be creating a new command builder when you save. Create the command builder when you create the data adapter.

Secondly, while this may not solve your issue, you really should be calling EndEdit on the BindingSource to commit any pending edit to the underlying DataTable before saving. If that doesn't solve your issue then you should test the value returned by Update to see whether changes were actually saved or not. If that value is not zero then changes were saved and you're just not looking for them properly.

By the way, hard-coding a connection string like yours is bad. That folder is most likely not going to exist if you deploy to another machine so what happens then? If you're using a local data file then the norm is to add it to your project and let the IDE copy it to the output folder with the EXE. You can then use "|DataDirectory|" for the folder path in your connection string and it will just work no matter where the app is, as long as the data file is in the same folder. Even then though, it's still wise to put the connection string in the config file and retrieve it from there. That way, the user can modify the connection string without having to rebuild the app if they ever want to move the data file.
 

Mimosa777

Active member
Joined
Aug 28, 2014
Messages
28
Programming Experience
1-3
I got it, I got it - thank you jmcilhinney :)
with some changes regarding the data adapter update and the bindingsource.endedit(), im now capable of using databinding to update my data:
VB.NET:
[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Private
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Button1_Click(sender [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2], e [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]EventArgs[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Button1.Click[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
        
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] cb [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] OleDb.[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]OleDbCommandBuilder[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](daCustomers)[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
        
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] row = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]DirectCast[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](binding_source.Current, [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]DataRowView[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2])[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
        row(
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"Name"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].TextBox1.Text[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
        row(
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"FirstName"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].TextBox2.Text[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
        
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].binding_source.EndEdit()[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
        daCustomers.Update(tablecust)

    
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
 
Last edited:

Mimosa777

Active member
Joined
Aug 28, 2014
Messages
28
Programming Experience
1-3
Firstly, while what you have done will work, you shouldn't really be creating a new command builder when you save. Create the command builder when you create the data adapter.

Secondly, while this may not solve your issue, you really should be calling EndEdit on the BindingSource to commit any pending edit to the underlying DataTable before saving. If that doesn't solve your issue then you should test the value returned by Update to see whether changes were actually saved or not. If that value is not zero then changes were saved and you're just not looking for them properly.

By the way, hard-coding a connection string like yours is bad. That folder is most likely not going to exist if you deploy to another machine so what happens then? If you're using a local data file then the norm is to add it to your project and let the IDE copy it to the output folder with the EXE. You can then use "|DataDirectory|" for the folder path in your connection string and it will just work no matter where the app is, as long as the data file is in the same folder. Even then though, it's still wise to put the connection string in the config file and retrieve it from there. That way, the user can modify the connection string without having to rebuild the app if they ever want to move the data file.

By the way, I read the documentation about connection strings and config file and will definitely going to use that instead of keeping it hardcoded as is - thank you again for those tips - always learning new things from you :)
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
By the way, I read the documentation about connection strings and config file and will definitely going to use that instead of keeping it hardcoded as is - thank you again for those tips - always learning new things from you :)

Various wizards will generate a connection string and store it in the config file automatically. If you're creating a connection string manually, I'd suggest adding it via the Settings page of the project properties. That will add it to the config file too but allow you to access it more easily in code via My.Settings.
 

Mimosa777

Active member
Joined
Aug 28, 2014
Messages
28
Programming Experience
1-3
I got it work now - thank you jmc - I finally used the generate connection string - I just add the db into my project and use |DataDirectory|. Im working on the app.config and will call my connection from there as you suggested.
thank you
 
Top Bottom