Hi all.
Im trying to do a dgv with fields coming from two tables as this:
Table1(Customers)
CostumerID (Int, Primary)
CostumerName (String)
Table2(Orders)
OrderID(int, Primary)
CustomerID (int)
OrderDate (Date)
I want my datagridview to display the fields as follows, mixing the fields from those tables.
OrderID, OrderDate, CustomerID(table2), CustomerName
i can do this by generating an access view, add to my dataset and drag it to my form. It displays fine. But the tableadapter doesnt generate the update, insert commands.and doesnt refresh the CustomerName when i write the Orders,CustomerID like access do. One thing the tables are related at the mdb file with integrity.
The view select command is
SELECT Table2.OrderID, Table2.OrderDate, Table2.CustomerID, Table1.CustomerName
FROM Customers INNER JOIN Orders ON Table1.CustomerID = Table2.CustomerID;
In access querys works fine filling the name field when we write the CustomerID but i dont know how to get this working on vb2005. I would like to know if theres a way to do this and if it is possible could i update the data .
Thank you very much.
binding datagridview with two tables
ly
i got it.
My problem was that if i do a datagridview with data coming from two tables update, delete, insert commands arent generated.
So i cant do this.
First create the Datatable Orders(with primary key) at my dataset on wich vs2005 will create propers update, insert, delete commands.
Then i modify the Orders select command and add the second table (Customers) and add the CustomerName field to my Orders select command.
Vs will ask if you want to generate the new updates.. commands based on this new select command. you have to choose NO.
Well my datagridview displays fine the fields coming from these two tables and updates the Orders one, but doesnt refresh CustomerName when i change the Orders CustomerID on the datagridview.
Someone knows how can i refresh the customers field
Thank you very much.
Jeff A Beck
1. Definie a scalar query which returns customer's name given its ID (query with parameter).
2. In the form where you use the data set, add RowChanged event handler to your data table, like:
AddHandler Me.OrdersDataGridView.RowChanged, _
New DataRowChangeEventHandler(AddressOf Row_Changed)
3. Write the code of "Row_Changed" event, somthing like:
e.Row("custname") = Me.OrdersDataGridView.GetCustName(e.Row("custid"))
.net classes has a lot of usefull events but there aren’t easy known and acceded. This kind of event should be accesible thru properties explorer. I guess isnt visible because data tables are inner classes of dataset.
Hope this helps.
philmee95
hocki101
The code is the one above. Im just is modifying the select command of the orders table adapter. Don forget to choose "Select all rows from Orders" at the query builder.
My problem is refreshing the data when the id changes its value. By filling again seems to work but its something wrong with the datagrid cursor. After fill, the cursor comes back to (0,0) cell but if i move it returns to the modified cell. Ill try to redraw the datagrid or save the cell position before fill.
elesser
Here is some code that fix the problem. It dont seem right to me but i cant do it another way.
Private Sub OrdersDataGridView_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles OrdersDataGridView.RowEnter
'updates only if there are changes
If Me.Ds2.HasChanges Then
Me.Validate()
Me.OrdersBindingSource.EndEdit()
'save the currentcell position
Dim CellPos As System.Drawing.Point
CellPos = Me.OrdersDataGridView.CurrentCellAddress
Try
Me.PedidosProvTableAdapter.Update(Me.Ds2.Orders)Catch ex As Exception
MsgBox(ex.Message)
End Try 'filling the table adapter again refreshes the data
Me.OrdersTableAdapter.Fill(Me.Ds2.Orders) 'restore cell position
Me.OrdersDataGridView.CurrentCell = Me.OrdersDataGridView.Rows(CellPos.Y + 1).Cells(CellPos.X)
End If End Sub
That fix the problem by filling all the data again. I guess theres any way to reduce the memory and network traffic but i cant know how.
If someone can do this more elegant please reply. Thanks.
John Penberthy
Just filling again works.
Private Sub OrdersDataGridView_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles OrdersDataGridView.RowValidated If Me.OrdersDataGridView.Columns(e.ColumnIndex).DataPropertyName = "IdCustomer" Then If Ds.HasChanges Then TryMe.OrdersTableAdapter.Update(Ds.Orders)
Me.OrdersTableAdapter.Fill(Ds.Orders)
Catch ex As Exception
End Try
End If
End If End Sub