Public Class Form1
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb;Jet OLEDB:Database Password=MyDbPassword;")
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
fillDataGridViewComboBoxColumn(0, "items", "title", "id")
End Sub
#Region " DataGridViewComboBoxColumn "
Private Sub fillDataGridViewComboBoxColumn(ByVal columnIndex As Integer, ByVal table As String, ByVal display As String, ByVal value As String, Optional ByVal where As String = "")
Using da As New OleDb.OleDbDataAdapter("SELECT [" & display & "],[" & value & "] FROM [" & table & "]", cnn)
If where.Trim <> "" Then da.SelectCommand.CommandText &= " " & where
Dim dt As New DataTable
da.Fill(dt)
dt.Rows.Add("** New item **", 0)
Dim cbColumn As DataGridViewComboBoxColumn = DirectCast(DataGridView1.Columns(columnIndex), DataGridViewComboBoxColumn)
cbColumn.DataSource = dt
cbColumn.ValueMember = "id"
cbColumn.DisplayMember = "title"
End Using
End Sub
Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
Try
If e.ColumnIndex = 1 AndAlso Not IsNumeric(DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value) Then
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value = 0
MsgBox("Number only")
Exit Sub
End If
If e.ColumnIndex = 2 AndAlso Not IsNumeric(DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value) Then
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value = 0
MsgBox("Number only")
Exit Sub
End If
DataGridView1.Item(3, e.RowIndex).Value = DataGridView1.Item(1, e.RowIndex).Value * DataGridView1.Item(2, e.RowIndex).Value
If DataGridView1.Item(3, e.RowIndex).Value = 0 Then
DataGridView1.CurrentRow.Cells(3).Style.BackColor = Color.LightPink
Else
DataGridView1.CurrentRow.Cells(3).Style.BackColor = Color.LightGreen
End If
Catch ex As Exception
End Try
End Sub
Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
If TypeOf e.Control Is System.Windows.Forms.DataGridViewComboBoxEditingControl Then
AddHandler TryCast(e.Control, ComboBox).SelectedIndexChanged, AddressOf DataGridComboBox_SelectedIndexChanged
End If
End Sub
Private Sub DataGridComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Try
Dim dataGridComboBox As ComboBox = CType(sender, ComboBox)
If dataGridComboBox.SelectedIndex = -1 Then Exit Sub
If dataGridComboBox.SelectedValue = 0 Then
Using frm As New Form2
If frm.ShowDialog = Windows.Forms.DialogResult.OK Then
Try
Using cmd As New OleDb.OleDbCommand("INSERT INTO [items] ([title], [barcode]) VALUES (@title, @barcode)", cnn)
cmd.Parameters.AddWithValue("@title", frm.TextBox1.Text.Trim)
cmd.Parameters.AddWithValue("@barcode", frm.TextBox2.Text.Trim)
If cnn.State <> ConnectionState.Open Then cnn.Open()
Dim r As Integer = cmd.ExecuteNonQuery
cnn.Close()
If r > 0 Then
fillDataGridViewComboBoxColumn(0, "items", "title", "id")
dataGridComboBox.SelectedIndex = dataGridComboBox.FindString(frm.TextBox1.Text.Trim)
End If
End Using
Catch ex As Exception
If cnn.State <> ConnectionState.Closed Then cnn.Close()
End Try
Else
dataGridComboBox.SelectedIndex = -1
End If
End Using
End If
Catch ex As Exception
End Try
End Sub
#End Region
End Class