Datagrid add and edit features

  • Thread starter Thread starter Tim McKinney
  • Start date Start date
T

Tim McKinney

I have a read-only datagrid that I use to display a heirarchal model
of three tables (The example I am setting up uses the Customer,
Orders, and Order Detail tables from the northwind database). I load
the three tables into a single dataset and then create the
relationships.

What I want to do is use a separate grid that would display the
selected record from the datagrid only, but still would be bound to
the dataset. The first grid would be used for navigating, while the
second grid would be used for modifying. The goal is that the second
grid would not be as 'busy', displaying only the information to be
modified.

Any help would be greatly appreciated. Thanks.
 
Hi Tim,

Thanks for posting in the community.

First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you wants to use two datagrids on
a winform, and use Datagrid A (readonly to display the data) and Datagrid B
to display just the current select on in Datagrid A for modification. The
datasource your are using is a heirarchal tables.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

I think you may try to bind the datagrid A on the Dataset. But for the
datagrid B you need to bind it to a new dataview, since the dataview should
create from the datatable, you need to reset the datasource of datagrid B
when you inspect the child table if the datagrid B.
In the DataView, you may use the RowFilter to display just the current
selected one.

Here I write a demo code for you.
<Code>
Dim v As DataView
Dim ds As Dataset1
Public str() As String
Public columnName() As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim sda1 As New SqlDataAdapter("SELECT * FROM Customers",
Me.SqlConnection1)
Dim sda2 As New SqlDataAdapter("SELECT * FROM Orders",
Me.SqlConnection1)
Dim sda3 As New SqlDataAdapter("SELECT * FROM [Order Details]",
Me.SqlConnection1)
ds = New Dataset1
sda1.Fill(ds.Customers)
sda2.Fill(ds.Orders)
sda3.Fill(ds.Order_Details)
DataGrid1.DataSource = ds
DataGrid1.DataMember = "Customers"
AddHandler ds.Customers.RowChanged, AddressOf dt_RowChanged
AddHandler ds.Orders.RowChanged, AddressOf dt_RowChanged
AddHandler ds.Order_Details.RowChanged, AddressOf dt_RowChanged
End Sub

Private Sub dt_RowChanged(ByVal sender As Object, ByVal e As
System.Data.DataRowChangeEventArgs)
If Not (Me.columnName Is Nothing Or Me.str Is Nothing) Then
Dim i As Integer
Dim strbld As New StringBuilder
For i = 0 To columnName.Length - 1
If i = 0 Then
strbld.Append(columnName(i) + "='" + str(i) + "'")
Else
strbld.Append(" AND " + columnName(i) + "='" + str(i) +
"'")
End If
Next
v.RowFilter = strbld.ToString
End If
End Sub

Private Sub DataGrid1_DataSourceChanged(ByVal sender As Object, ByVal e
As System.EventArgs) Handles DataGrid1.DataSourceChanged
Dim dt As DataTable
Dim cr As CurrencyManager
If Me.DataGrid1.DataMember = "" Then
Exit Sub
End If
cr = CType(BindingContext(Me.DataGrid1.DataSource,
Me.DataGrid1.DataMember), CurrencyManager)
dt = CType(cr.Current, DataRowView).Row.Table
v = New DataView(dt)
DataGrid2.DataSource = v
Dim dc() As DataColumn = dt.PrimaryKey
Dim i As Integer
i = 0
Dim strbld As New StringBuilder
ReDim columnName(dc.Length - 1)
ReDim str(dc.Length - 1)
For i = 0 To dc.Length - 1
columnName(i) = dc(i).ColumnName
str(i) = CType(cr.Current, DataRowView).Row.Item(columnName(i))
If i = 0 Then
strbld.Append(columnName(i) + "='" + str(i) + "'")
Else
strbld.Append(" AND " + columnName(i) + "='" + str(i) + "'")
End If
Next
v.RowFilter = strbld.ToString
End Sub

Private Sub DataGrid1_MouseUp(ByVal sender As Object, ByVal e As
System.Windows.Forms.MouseEventArgs) Handles DataGrid1.MouseUp
Dim ds As Dataset1
ds = Me.DataGrid1.DataSource
Dim dt As DataTable
Dim cr As CurrencyManager
cr = CType(BindingContext(Me.DataGrid1.DataSource,
Me.DataGrid1.DataMember), CurrencyManager)
Dim drv As DataRowView
drv = CType(cr.Current, DataRowView)
dt = drv.Row.Table
Dim dc() As DataColumn = dt.PrimaryKey
Dim i As Integer
i = 0
Dim strbld As New StringBuilder
ReDim columnName(dc.Length - 1)
ReDim str(dc.Length - 1)
For i = 0 To dc.Length - 1
columnName(i) = dc(i).ColumnName
str(i) = CType(cr.Current, DataRowView).Row.Item(columnName(i))
If i = 0 Then
strbld.Append(columnName(i) + "='" + str(i) + "'")
Else
strbld.Append(" AND " + columnName(i) + "='" + str(i) + "'")
End If
Next
v.RowFilter = strbld.ToString
End Sub
End Class
</Code>

If you have any concern on this issue,please post here.

My test works on the dataset xml schema as below.
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="Dataset1" targetNamespace="http://tempuri.org/Dataset1.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified" xmlns="http://tempuri.org/Dataset1.xsd"
xmlns:mstns="http://tempuri.org/Dataset1.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Dataset1" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Customers">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerID" type="xs:string" />
<xs:element name="CompanyName" type="xs:string" />
<xs:element name="ContactName" type="xs:string" minOccurs="0" />
<xs:element name="ContactTitle" type="xs:string" minOccurs="0" />
<xs:element name="Address" type="xs:string" minOccurs="0" />
<xs:element name="City" type="xs:string" minOccurs="0" />
<xs:element name="Region" type="xs:string" minOccurs="0" />
<xs:element name="PostalCode" type="xs:string" minOccurs="0" />
<xs:element name="Country" type="xs:string" minOccurs="0" />
<xs:element name="Phone" type="xs:string" minOccurs="0" />
<xs:element name="Fax" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Orders">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="CustomerID" type="xs:string" minOccurs="0" />
<xs:element name="EmployeeID" type="xs:int" minOccurs="0" />
<xs:element name="OrderDate" type="xs:dateTime" minOccurs="0" />
<xs:element name="RequiredDate" type="xs:dateTime" minOccurs="0" />
<xs:element name="ShippedDate" type="xs:dateTime" minOccurs="0" />
<xs:element name="ShipVia" type="xs:int" minOccurs="0" />
<xs:element name="Freight" type="xs:decimal" minOccurs="0" />
<xs:element name="ShipName" type="xs:string" minOccurs="0" />
<xs:element name="ShipAddress" type="xs:string" minOccurs="0" />
<xs:element name="ShipCity" type="xs:string" minOccurs="0" />
<xs:element name="ShipRegion" type="xs:string" minOccurs="0" />
<xs:element name="ShipPostalCode" type="xs:string" minOccurs="0" />
<xs:element name="ShipCountry" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Order_x0020_Details">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderID" type="xs:int" />
<xs:element name="ProductID" type="xs:int" />
<xs:element name="UnitPrice" type="xs:decimal" />
<xs:element name="Quantity" type="xs:short" />
<xs:element name="Discount" type="xs:float" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Dataset1Key1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Customers" />
<xs:field xpath="mstns:CustomerID" />
</xs:unique>
<xs:unique name="Dataset1Key2" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Orders" />
<xs:field xpath="mstns:OrderID" />
</xs:unique>
<xs:unique name="Dataset1Key3" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Order_x0020_Details" />
<xs:field xpath="mstns:OrderID" />
<xs:field xpath="mstns:ProductID" />
</xs:unique>
<xs:keyref name="CustomersOrders" refer="Dataset1Key1">
<xs:selector xpath=".//mstns:Orders" />
<xs:field xpath="mstns:CustomerID" />
</xs:keyref>
<xs:keyref name="OrdersOrder_x005F_x0020_Details" refer="Dataset1Key2">
<xs:selector xpath=".//mstns:Order_x0020_Details" />
<xs:field xpath="mstns:OrderID" />
</xs:keyref>
</xs:element>
</xs:schema>

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Tim,

Thanks for posting in the community.

Did my suggestion help you>?
If you have any concern on this issue,please post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Peter,

Sorry for the late reply. I'm on the road this week and haven't had
access to an internet connection.

Thanks very much for the help. I'm fairly new to .Net (coming from VB6),
and didn't realize the dataview had additional properties not found in
the dataset. So, I was able to use your code to create a filtered
recordset, that was just what I was looking for. Thanks again...
greatly appreciated.
 
Back
Top