1: Imports System.Data.SqlClient
2:
3: Public Class ControlInDataGrid
4: Inherits System.Windows.Forms.Form
5:
6: Windows Form Designer generated code
7:
8: #Region "Variables Declaration"
9: Private WithEvents cboSupplierID As ComboBox
10: Private WithEvents cboCategoryID As ComboBox
11:
12: Private dsProducts As DataSet
13: Private daProducts As SqlDataAdapter
14: Private cbProducts As SqlCommandBuilder
15: Private cnNorthwind As New SqlConnection( _
16: "Database=Northwind;Server=localhost;" & _
17: "Integrated Security=SSPI")
18: #End Region
19:
20: #Region "Procedures"
21: Sub CreateControlInDataGrid()
22: 'membuat combobox untuk datagrid
23: cboSupplierID = New ComboBox
24: cboSupplierID.Dock = DockStyle.Fill
25: cboSupplierID.DropDownStyle = ComboBoxStyle.DropDownList
26: cboSupplierID.Cursor = Cursors.Arrow
27:
28: cboCategoryID = New ComboBox
29: cboCategoryID.Dock = DockStyle.Fill
30: cboCategoryID.DropDownStyle = ComboBoxStyle.DropDownList
31: cboCategoryID.Cursor = Cursors.Arrow
32: End Sub
33:
34: Sub MyDataGridStyle()
35: 'merubah tampilan datagrid /
36: 'menempatkan combobox ke datagrid
37: Me.DataGrid1.DataSource = Me.dsProducts.Tables(0)
38: Dim MyGridStyle As New DataGridTableStyle
39: Me.DataGrid1.TableStyles.Clear()
40: With MyGridStyle
41: .AlternatingBackColor = Drawing.Color.Gainsboro
42: .SelectionBackColor = Drawing.Color.Wheat
43: .SelectionForeColor = Drawing.Color.Red
44: .GridLineColor = Drawing.Color.BlueViolet
45: .GridLineStyle = DataGridLineStyle.Solid
46: .PreferredRowHeight = 22
47: .BackColor = Drawing.Color.WhiteSmoke
48: .MappingName = dsProducts.Tables(0).TableName.ToString
49: End With
50: Dim col1 As New DataGridTextBoxColumn
51: With col1
52: .ReadOnly = True
53: .MappingName = "ProductID"
54: .HeaderText = "ID"
55: .NullText = "_"
56: .Width = 70
57: End With
58: Dim col2 As New DataGridTextBoxColumn
59: With col2
60: .MappingName = "ProductName"
61: .HeaderText = "Name"
62: .NullText = "_"
63: .Width = 100
64: End With
65: Dim col3 As New DataGridTextBoxColumn
66: With col3
67: .MappingName = "SupplierID"
68: .HeaderText = "Supplier ID"
69: .TextBox.Controls.Add(cboSupplierID)
70: .NullText = "_"
71: .Width = 80
72: End With
73: Dim col4 As New DataGridTextBoxColumn
74: With col4
75: .MappingName = "CategoryID"
76: .HeaderText = "CategoryID"
77: .TextBox.Controls.Add(cboCategoryID)
78: .NullText = "_"
79: .Width = 80
80: End With
81: Dim col5 As New DataGridTextBoxColumn
82: With col5
83: .MappingName = "UnitPrice"
84: .HeaderText = "Price"
85: .NullText = "_"
86: .Width = 100
87: .Format = Format(.TextBox.Text, "Rp.##,00")
88: End With
89:
90: MyGridStyle.GridColumnStyles.AddRange(New _
91: DataGridColumnStyle() {col1, col2, col3, col4, col5})
92: Me.DataGrid1.TableStyles.Add(MyGridStyle)
93: End Sub
94:
95: Sub FillDataToCombo()
96: If cnNorthwind.State <> ConnectionState.Open _
97: Then cnNorthwind.Open()
98: 'isi data supplierid ke combo
99: Dim cmd As New SqlCommand
100: cmd.CommandType = CommandType.Text
101: cmd.Connection = cnNorthwind
102: cmd.CommandText = "Select SupplierID From Suppliers" & _
103: "Order By SupplierID"
104:
105: Me.cboSupplierID.Items.Clear()
106: Dim dr As SqlDataReader = cmd.ExecuteReader
107: If dr.HasRows Then
108: While dr.Read
109: cboSupplierID.Items.Add(dr("SupplierID"))
110: End While
111: End If
112: dr.Close()
113:
114: 'isi data categoryid ke combo
115: Me.cboCategoryID.Items.Clear()
116: cmd.CommandText = "Select CategoryID From Categories" & _
117: " Order By CategoryID"
118: dr = cmd.ExecuteReader
119: If dr.HasRows Then
120: While dr.Read
121: cboCategoryID.Items.Add(dr("CategoryID"))
122: End While
123: End If
124: dr.Close()
125: cnNorthwind.Close()
126: End Sub
127:
128: Sub CreateDataSource()
129: 'create dataset
130: If cnNorthwind.State <> ConnectionState.Open _
131: Then cnNorthwind.Open()
132: daProducts = New SqlDataAdapter("Select ProductID," & _
133: "ProductName,SupplierID,CategoryID,UnitPrice " & _
134: "From Products", cnNorthwind)
135: cbProducts = New SqlCommandBuilder(daProducts)
136: dsProducts = New DataSet
137: daProducts.Fill(dsProducts, "products")
138: cnNorthwind.Close()
139: End Sub
140: #End Region