Datagridview with masktextbox and Combobox
Good evening to all
I will try to be as clear as possible.
I have a database in Access that I want to display it in a Datagridview which consists of a key, date, time, and 2 more Comboboxes filled again from other tables in the same Access
In the date and time fields I've put a mask to be easier to place them.
My problem is this.
If I change a value in Combobox I have in Datagridview if not the first time the second definitely stop showing the masks in the areas of time and date.
Conversely, if not tampered Comboboxes then all the masks and the time and date work fine.
Below you will find the tables and the code I use.
Thanks so much for your time.
The comments are welcomed
Table1 ID Col1 Date Time Col4
1 1 08/04/2013 14:00 1
2 2 09/04/2013 14:05 2
3 3 10/04/2013 14:10 3
4 4 11/04/2013 14:15 4
5 5 12/04/2013 14:20 5
6 6 13/04/2013 14:25 6
7 7 14/04/2013 14:30 7
Table2 tbl2_ID tbl2_Desc
1 Desc1
2 Desc2
3 Desc3
4 Desc4
5 Desc5
6 Desc6
7 Desc7
Table3 tbl3_ID tbl3_Desc
1 DescA
2 DescB
3 DescC
4 DescD
5 DescE
6 DescF
7 DescG
And the code i use is this
Code:
Imports System.Data.OleDb
Public Class Form1
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source = C:\Test.mdb")
Dim cmd As OleDbCommand
Dim ds As New DataSet
Dim da As OleDbDataAdapter
Dim cmb1 As New DataGridViewComboBoxColumn()
Dim cmb2 As New DataGridViewComboBoxColumn()
Private Sub Bkpdwn_D_List_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Call fill_cbos()
Call fill_dataGrid()
maskedTextBox1 = New MaskedTextBox
With maskedTextBox1
.Mask = "00/00/0000"
.Visible = False
End With
maskedTextBox2 = New MaskedTextBox
With maskedTextBox2
.Mask = "00:00"
.Visible = False
End With
DataGridView1.Controls.Add(maskedTextBox1)
DataGridView1.Controls.Add(maskedTextBox2)
End Sub
Private WithEvents maskedTextBox1 As MaskedTextBox
Private WithEvents maskedTextBox2 As MaskedTextBox
Private Sub Bkpdwn_D_DataGridView_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit
If (e.ColumnIndex = 2) Then
Dim rect1 As Rectangle = Me.DataGridView1.GetCellDisplayRectangle(e.ColumnIndex, e.RowIndex, True)
Me.maskedTextBox1.Size = rect1.Size
Me.maskedTextBox1.Location = rect1.Location
If (Me.DataGridView1.CurrentCell.Value IsNot Nothing) Then
Me.maskedTextBox1.Text = Me.DataGridView1.CurrentCell.FormattedValue.ToString()
End If
Me.maskedTextBox1.Visible = True
End If
If (e.ColumnIndex = 3) Then
Dim rect2 As Rectangle = Me.DataGridView1.GetCellDisplayRectangle(e.ColumnIndex, e.RowIndex, True)
Me.maskedTextBox2.Size = rect2.Size
Me.maskedTextBox2.Location = rect2.Location
If (Me.DataGridView1.CurrentCell.Value IsNot Nothing) Then
Me.maskedTextBox2.Text = Me.DataGridView1.CurrentCell.FormattedValue.ToString()
End If
Me.maskedTextBox2.Visible = True
End If
End Sub
Private Sub Bkpdwn_D_DataGridView_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
If (e.ColumnIndex = 2) Then
If maskedTextBox1.Visible = True Then
If maskedTextBox1.Text <> " / /" Then
If IsDate(maskedTextBox1.Text) Then
Me.DataGridView1.CurrentCell.Value = maskedTextBox1.Text
Me.maskedTextBox1.Visible = False
Else
MsgBox("Wrong Date")
maskedTextBox1.Text = ""
e.Cancel = True
End If
Else
Me.DataGridView1.CurrentCell.Value = ""
Me.maskedTextBox1.Visible = False
End If
End If
End If
If (e.ColumnIndex = 3) Then
If maskedTextBox2.Visible = True Then
If maskedTextBox2.Text <> " :" Then
If IsDate(maskedTextBox2.Text) Then
Me.DataGridView1.CurrentCell.Value = maskedTextBox2.Text
Me.maskedTextBox2.Visible = False
Else
MsgBox("Wrong Time")
maskedTextBox2.Text = ""
e.Cancel = True
End If
Else
Me.DataGridView1.CurrentCell.Value = String.Empty
Me.maskedTextBox2.Visible = False
End If
End If
End If
End Sub
Private IsHandleAdded1 As Boolean
Private IsHandleAdded2 As Boolean
Private Sub Bkpdwn_D_DataGridView_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
If (Not IsHandleAdded1 And Me.DataGridView1.CurrentCell.ColumnIndex = 2) Then
Dim tx As TextBox = CType(e.Control, TextBox)
AddHandler tx.KeyPress, AddressOf Me.tx_KeyPress
IsHandleAdded1 = True
End If
If (Not IsHandleAdded2 And Me.DataGridView1.CurrentCell.ColumnIndex = 3) Then
Dim tx As TextBox = CType(e.Control, TextBox)
AddHandler tx.KeyPress, AddressOf Me.tx_KeyPress
IsHandleAdded2 = True
End If
End Sub
Private Sub tx_KeyPress(ByVal sender As Object, ByVal e As KeyPressEventArgs)
If (Me.DataGridView1.CurrentCell.ColumnIndex = 2) Then
e.Handled = True
Me.maskedTextBox1.Focus()
If (Char.IsNumber(e.KeyChar)) Then
Me.maskedTextBox1.Text = e.KeyChar.ToString()
Else
Me.maskedTextBox1.SelectAll()
End If
End If
If (Me.DataGridView1.CurrentCell.ColumnIndex = 3) Then
e.Handled = True
Me.maskedTextBox2.Focus()
If (Char.IsNumber(e.KeyChar)) Then
Me.maskedTextBox2.Text = e.KeyChar.ToString()
Else
Me.maskedTextBox2.SelectAll()
End If
End If
End Sub
'--------------------------------'Subroutins'--------------------------------'
Private Sub fill_dataGrid()
Dim Datagridsql As String = ""
Try
Datagridsql = "select * from Table1"
cmd = New OleDbCommand(Datagridsql, con)
If con.State = ConnectionState.Open Then con.Close()
da = New OleDbDataAdapter(cmd)
ds = New DataSet()
da.Fill(ds, "Table1")
DataGridView1.DataSource = ds.Tables("Table1")
DataGridView1.Columns(0).HeaderText = "ID"
DataGridView1.Columns(0).Visible = False
DataGridView1.Columns(1).HeaderText = "Col1"
DataGridView1.Columns(1).Width = 100
DataGridView1.Columns(2).HeaderText = "Date"
DataGridView1.Columns(2).Width = 100
DataGridView1.Columns(3).HeaderText = "Time"
DataGridView1.Columns(3).DefaultCellStyle.Format = "hh:mm tt"
DataGridView1.Columns(3).Width = 100
DataGridView1.Columns(4).HeaderText = "Col4"
DataGridView1.Columns(4).Width = 100
DataGridView1.Columns.RemoveAt(1)
DataGridView1.Columns.Insert(1, cmb1)
With cmb1
.DataPropertyName = "Col1"
.HeaderText = "Column-1"
.Width = 100
End With
DataGridView1.Columns.RemoveAt(4)
DataGridView1.Columns.Insert(4, cmb2)
DataGridView1.AutoGenerateColumns = False
With cmb2
.DataPropertyName = "Col4"
.HeaderText = "Column-4"
.Width = 100
End With
da = Nothing
ds = Nothing
con.Close()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, Me.Text)
End Try
End Sub
Private Sub fill_cbos()
Dim cmd As OleDbCommand
Dim ds As New DataSet
Dim da As OleDbDataAdapter
cmd = New OleDbCommand("select * from Table2", con)
If con.State = ConnectionState.Open Then con.Close()
da = New OleDbDataAdapter(cmd)
ds = New DataSet()
da.Fill(ds, "Table2")
With cmb1
.DataSource = ds.Tables("Table2")
.DisplayMember = "tbl2_Desc"
.ValueMember = "tbl2_ID"
End With
cmd = New OleDbCommand("select * from Table3", con)
If con.State = ConnectionState.Open Then con.Close()
da = New OleDbDataAdapter(cmd)
ds = New DataSet()
da.Fill(ds, "Table3")
With cmb2
.DataSource = ds.Tables("Table3")
.DisplayMember = "tbl3_Desc"
.ValueMember = "tbl3_ID"
End With
End Sub
End Class
Re: Datagridview with masktextbox and Combobox
Unfortunately, using controls other than the ones already provided to the DataGridView is not as simple as using Controls.Add. There is a whole song and dance about it. Here is a sample implementation. And here is another article that shows how to implement a NumericUpDown control as a DataGridView column. It provides a lot of explanation but its in C# though.
Re: Datagridview with masktextbox and Combobox
Thanks for your time and for your answer.
I will check the first post that create a calendar to see how they use the mask.
