Question Change the value of specific cell in datagridview

[The]

I have a datagridview inside windows form.
The datagridview retrive the data from table Invoice. In that table, there is a field for customer id, C_ID. Now in the datagridview, what would be the easiest way to replace (show) the C_ID integer with the client name restored in the Customers table.

Thank you guys

 

[jmcilhinney]

The DataGridView doesn't retrieve anything. You presumably have a data adapter or table adapter on which you call Fill. That's where the retrieving gets done. The DataGridView simply displays whatever you provide it, with no thought or care for where it came from.

If you were going to access the data via the grid, you would use its Item property. You pass the column and row indexes of the cell as arguments and it returns the DataGridViewCell object at those coordinates. You can then do whatever you like with that cell, e.g. set its Value property.

The thing is, you wouldn't access the data via the grid. If you're not already, you should bind the data to the grid via a BindingSource, by assigning your DataTable or other list to the DataSource property of the BindingSource and assigning the BindingSource to the DataSource property of the grid. You then interact with the data exclusively via the BindingSource. You can index it to get the record at a specific index or use the Current property to get the record currently selected in the grid. You then use that in whatever way is appropriate for the type of object it is, e.g. if you bound a DataTable then it will be a DataRowView, so you can index it by column and set the contents directly, e.g.

Dim row = DirectCast(myBindingSource(rowIndex), DataRowView)

row(columnIndex) = newValue

 

[The]

Thanks for the clarification.
So, if I have a custom sql query using inner join:

SELECT Units.U_ID, Units.B_ID, Units.F_ID, Units.U_Number, Units.Rooms, Units.Default_Rent, Units.Current_Rent, Tenants.T_Name
FROM (Units INNER JOIN
Tenants ON Units.U_ID = Tenants.U_ID)

How can I make the DataGrideView use it as a source instead of the binding table?

 

[jmcilhinney]

Thanks for the clarification.
So, if I have a custom sql query using inner join:

How can I make the DataGrideView use it as a source instead of the binding table?

What your query looks like makes no difference. A DataTable is a DataTable, regardless of how you populate it. The data might not even be from a database. Once you have a DataTable, how you use it is the same regardless.

Are you perhaps asking how to incorporate that query into a typed DataSet with table adapters? By default, a typed DataSet will have one DataTable and table adapter per database table. You can still customise it though. If that's what you want then let us know and I can provide more information.

 

[JohnH]

The datagridview retrive the data from table Invoice. In that table, there is a field for customer id, C_ID. Now in the datagridview, what would be the easiest way to replace (show) the C_ID integer with the client name restored in the Customers table.

Another option is adding a data relation between the two tables and configure DGV column to use the related parent field as DisplayMember. There should be lots of parent-child binding samples to be found, and I also posted one here post 12: http://www.vbdotnetforums.com/winforms-grids/60918-sort-datagridview-values-displayed-combobox.html (this sample includes some specialized code for sorting on DisplayMember from parent table, but is still relevant).

 

[The]

Another option is adding a data relation between the two tables and configure DGV column to use the related parent field as DisplayMember. There should be lots of parent-child binding samples to be found, and I also posted one here post 12: http://www.vbdotnetforums.com/winforms-grids/60918-sort-datagridview-values-displayed-combobox.html (this sample includes some specialized code for sorting on DisplayMember from parent table, but is still relevant).

Thanks John
Thats exactly what I am looking for.
Here is my code, after considering your solution:

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Odbc
Imports System.Data.DataTable

Public Class Form2

    Public sqlconn As New OleDb.OleDbConnection
    Public connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\M_db.accdb"
    Public sqlquery As New OleDb.OleDbCommand

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.Db_Connect()
        GetData()
    End Sub

    Private Sub GetData()

        Try
            Dim SqlCmd As String = "Select * from Units" 
            Dim U_DataAdapter As New OleDb.OleDbDataAdapter(SqlCmd, sqlconn)
            Dim List As New DataSet
            U_DataAdapter.Fill(List, "Units")
            U_DataAdapter.Dispose()


            Dim SqlCmd2 As String = "Select * from Floors"
            Dim F_DataAdapter As New OleDb.OleDbDataAdapter(SqlCmd2, sqlconn)
            'Dim List As New DataSet
            F_DataAdapter.Fill(List, "Floors")
            F_DataAdapter.Dispose()

            Dim relation As New DataRelation("FloorUnits", _
                List.Tables("Floors").Columns("F_ID"), _
                List.Tables("Units").Columns("F_ID"))
            List.Relations.Add(relation)

            Dim FloorsBindingSource As New BindingSource()
            FloorsBindingSource.DataSource = List
            FloorsBindingSource.DataMember = "Floors"

            Dim ListBindingSource As New BindingSource()
            DataGridView1.DataSource = List
            DataGridView1.DataMember = "FloorUnits"
            DataGridView1.SetDataBinding(List, "Units.FloorUnits")

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

End Class

I am confused here. Can you please guide me? :apologetic:

 

[JohnH]

configure DGV column to use the related parent field as DisplayMember

Actually that is for separate binding, in sample it is used for a DataGridView ComboBox lookup column that has its own datasource (to look up all values in parent table). If you look in FormLocalDataset (FormParentChildData_Load) you'll see this code:

Me.DataGridView1.Columns("Helper_ParentName").Visible = True

Change that to True and this column will display, the data source for this DataGridViewTextBoxColumn is still the child table, but it is bound to the expression column that shows expression "Parent.ParentName" due to the data relation. So like the sample you have to add a DataColumn to your child table and set its Expression. Then you have to show this column in DataGridView instead of the column that shows the parent id.

 

[The]

Fabulous
Thanks a lot