By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,058 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,058 IT Pros & Developers. It's quick & easy.

DataGridView Formatting and Filtering

P: n/a
I have a DataGridView which displays numeric (Int32) data from an underlying
database. I want the numbers to be displayed in numeric format "#,###"
(with commas). I want to also limit the user so they can only input
numerical values. Is there any way to do this? Currently my formatting
works but if the user enters a value manually the formatting is not applied
to the new value. Also I haven't found a way to allow only numerical input.
Code is below.

So for example if the underlying value is 13200 once the data is read into
the DataGrid it's displayed as 13,200. If I manually change it and type in
13200 it stays that way, without the comma.

Thanks,
Ryan
Me.DataGridViewDetails.Columns.Add("Number", "Number")

With Me.DataGridViewDetails.Columns("Number")

..DefaultCellStyle.Format = "#,###"

..DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight

End With
Jan 4 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
I'm halfway there. Apparently the formatting is not being applied because
the datagrid is viewing the data entered as a string. I added the following
code to the DataGridView_CellValueChanged event and it works as expected.
Now to prevent the user from entering anything other than numerical data,
and ideas how to do this? Thanks.
Private Sub DataGridViewDetails_CellValueChanged(ByVal sender As
System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
Handles DataGridViewDetails.CellValueChanged

Dim myNewValue As Integer =
Me.DataGridViewDetails.Rows(e.RowIndex).Cells(e.Co lumnIndex).Value

Me.DataGridViewDetails.Rows(e.RowIndex).Cells(e.Co lumnIndex).Value =
myNewValue

End Sub

"Ryan" <Ty****@newsgroups.nospamwrote in message
news:eT**************@TK2MSFTNGP03.phx.gbl...
>I have a DataGridView which displays numeric (Int32) data from an
underlying database. I want the numbers to be displayed in numeric format
"#,###" (with commas). I want to also limit the user so they can only
input numerical values. Is there any way to do this? Currently my
formatting works but if the user enters a value manually the formatting is
not applied to the new value. Also I haven't found a way to allow only
numerical input. Code is below.

So for example if the underlying value is 13200 once the data is read into
the DataGrid it's displayed as 13,200. If I manually change it and type
in 13200 it stays that way, without the comma.

Thanks,
Ryan
Me.DataGridViewDetails.Columns.Add("Number", "Number")

With Me.DataGridViewDetails.Columns("Number")

.DefaultCellStyle.Format = "#,###"

.DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight

End With


Jan 4 '07 #2

P: n/a
Hi Ryan,

I have written a test sample regarding your scenario, however, I found that
once the DataColumn type in the datasource is of type "Integer", the
DataGridView will format the data into "#,###" without any problem. The
full sample code is listed below:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim dt As DataTable = New DataTable("test")
dt.Columns.Add(New DataColumn("column1", GetType(Integer)))
dt.Columns.Add(New DataColumn("column2", GetType(String)))

Dim i As Integer
For i = 0 To 5
Dim dr As DataRow
dr = dt.NewRow()
dr("column1") = i * 1000
dr("column2") = "item" + i.ToString()
dt.Rows.Add(dr)
Next

Me.Column1.DataPropertyName = "column1"
Me.Column1.ValueType = GetType(Integer)
Me.Column1.DefaultCellStyle.Format = "#,###"

Me.Column2.DataPropertyName = "column2"
Me.Column1.ValueType = GetType(String)

Me.DataGridView1.DataSource = dt
End Sub

Note: Column1 and Column2 are 2 DataGridViewTextBoxColumn(s) added from
designer.

Based on my test, once the "column1" is of type "Integer", the manually
input integer data in "column1" will be formatted into "#,###" style
without any problem. So, do you use "Integer" type in the datacolumn?

To restrict the user input as numeric data, you have 2 approaches:

#1, Restrict single key input to decimal character. You may get this done
by register the KeyDown event of TextBox in the DataGridViewTextBoxColumn
and filter non-decimal keys with SuppressKeyPress property, the sample code
is listed below:

Private Sub DataGridView1_EditingControlShowing(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.DataGridViewEditingControlSho wingEventArgs) Handles
DataGridView1.EditingControlShowing

If Me.DataGridView1.CurrentCell.ColumnIndex = 0 And Not e.Control
Is Nothing Then
Dim tb As TextBox = CType(e.Control, TextBox)
AddHandler tb.KeyDown, AddressOf TextBox_KeyDown
End If

End Sub

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.KeyEventArgs)
If Keys.D0 < e.KeyData And e.KeyData < Keys.D9 Then
e.Handled = False
Else
e.Handled = True
e.SuppressKeyPress = True
End If
End Sub

The sample code handles DataGridView.EditingControlShowing event and get
the TextBox reference through e.Control property. Then it registers the
event for TextBox.

#2, Do not validate the single key press, but delay the validation to the
commit time. You may handle DataGridView.CellValidating event and validate
the UI input. If the input is incorrect(based on the Integer.TryPause
result), you may set Cancel property to true to cancel the commit:

Private Sub DataGridView1_CellValidating(ByVal sender As System.Object,
ByVal e As System.Windows.Forms.DataGridViewCellValidatingEve ntArgs)
Handles DataGridView1.CellValidating
If e.ColumnIndex = 0 And Not e.FormattedValue = String.Empty Then

Dim i As Integer
Dim str As String = CType(e.FormattedValue, String)
str = str.Replace(",", String.Empty)
If Not Integer.TryParse(str, i) Then
MsgBox("Please input integer data")
e.Cancel = True
End If

End If
End Sub

These 2 code snippets both work well on my side. You may choose one of
these 2 solutions based on your application's requirement.

Hope this helps.

Best regards,
Jeffrey Tan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Jan 5 '07 #3

P: n/a
Thanks for the help. I don't want to set the type of the column to Integer
because I have other rows at the bottom of the datagrid that are not Integer
type (Account Codes which contain alphanumeric values). Still thanks for
mentioning this I wasn't aware of how to set the datatype and it could come
in handy for other projects. I considered making the non-integer rows part
of a 2nd datagridview but this doubles the amount of code I have to right
and layout isn't as nice without a lot of code in Resize events. No matter
my current method works fine as long as I can limit the user to only input
digit keys.. so on to part 2 of your code..

I tried your #1 method because I do want to validate on each single key
press (I want non-numeric keys to not even show up). The
EditingControlShowing event only fires when I first enter the cell. This
appears to be what you've intended it to do. However, the other event
TextBox_KeyDown never fires once I'm in edit mode in the cell. Why would
this event never trigger? Any ideas?

Thanks again for your detailed response..

Ryan

""Jeffrey Tan[MSFT]"" <je***@online.microsoft.comwrote in message
news:XL****************@TK2MSFTNGHUB02.phx.gbl...
Hi Ryan,

I have written a test sample regarding your scenario, however, I found
that
once the DataColumn type in the datasource is of type "Integer", the
DataGridView will format the data into "#,###" without any problem. The
full sample code is listed below:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim dt As DataTable = New DataTable("test")
dt.Columns.Add(New DataColumn("column1", GetType(Integer)))
dt.Columns.Add(New DataColumn("column2", GetType(String)))

Dim i As Integer
For i = 0 To 5
Dim dr As DataRow
dr = dt.NewRow()
dr("column1") = i * 1000
dr("column2") = "item" + i.ToString()
dt.Rows.Add(dr)
Next

Me.Column1.DataPropertyName = "column1"
Me.Column1.ValueType = GetType(Integer)
Me.Column1.DefaultCellStyle.Format = "#,###"

Me.Column2.DataPropertyName = "column2"
Me.Column1.ValueType = GetType(String)

Me.DataGridView1.DataSource = dt
End Sub

Note: Column1 and Column2 are 2 DataGridViewTextBoxColumn(s) added from
designer.

Based on my test, once the "column1" is of type "Integer", the manually
input integer data in "column1" will be formatted into "#,###" style
without any problem. So, do you use "Integer" type in the datacolumn?

To restrict the user input as numeric data, you have 2 approaches:

#1, Restrict single key input to decimal character. You may get this done
by register the KeyDown event of TextBox in the DataGridViewTextBoxColumn
and filter non-decimal keys with SuppressKeyPress property, the sample
code
is listed below:

Private Sub DataGridView1_EditingControlShowing(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.DataGridViewEditingControlSho wingEventArgs) Handles
DataGridView1.EditingControlShowing

If Me.DataGridView1.CurrentCell.ColumnIndex = 0 And Not e.Control
Is Nothing Then
Dim tb As TextBox = CType(e.Control, TextBox)
AddHandler tb.KeyDown, AddressOf TextBox_KeyDown
End If

End Sub

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.KeyEventArgs)
If Keys.D0 < e.KeyData And e.KeyData < Keys.D9 Then
e.Handled = False
Else
e.Handled = True
e.SuppressKeyPress = True
End If
End Sub

The sample code handles DataGridView.EditingControlShowing event and get
the TextBox reference through e.Control property. Then it registers the
event for TextBox.

#2, Do not validate the single key press, but delay the validation to the
commit time. You may handle DataGridView.CellValidating event and validate
the UI input. If the input is incorrect(based on the Integer.TryPause
result), you may set Cancel property to true to cancel the commit:

Private Sub DataGridView1_CellValidating(ByVal sender As System.Object,
ByVal e As System.Windows.Forms.DataGridViewCellValidatingEve ntArgs)
Handles DataGridView1.CellValidating
If e.ColumnIndex = 0 And Not e.FormattedValue = String.Empty Then

Dim i As Integer
Dim str As String = CType(e.FormattedValue, String)
str = str.Replace(",", String.Empty)
If Not Integer.TryParse(str, i) Then
MsgBox("Please input integer data")
e.Cancel = True
End If

End If
End Sub

These 2 code snippets both work well on my side. You may choose one of
these 2 solutions based on your application's requirement.

Hope this helps.

Best regards,
Jeffrey Tan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Jan 5 '07 #4

P: n/a
Ok, I got it working, just had to modify a few things in the code. Instead
of using a TextBox control I used a DataGridViewTextBoxEditingControl. Also
changed it so it excepts the number pad as well.

Ryan

If Not e.Control Is Nothing Then

Dim tb As DataGridViewTextBoxEditingControl = CType(e.Control,
DataGridViewTextBoxEditingControl)

AddHandler tb.KeyDown, AddressOf TextBox_KeyDown

End If

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.KeyEventArgs)

If (Keys.D0 <= e.KeyData And e.KeyData <= Keys.D9) Or (Keys.NumPad0 <=
e.KeyData And e.KeyData <= Keys.NumPad9) Then

e.Handled = False

Else

e.Handled = True

e.SuppressKeyPress = True

End If

End Sub

"Ryan" <Ty****@newsgroups.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
Thanks for the help. I don't want to set the type of the column to
Integer because I have other rows at the bottom of the datagrid that are
not Integer type (Account Codes which contain alphanumeric values). Still
thanks for mentioning this I wasn't aware of how to set the datatype and
it could come in handy for other projects. I considered making the
non-integer rows part of a 2nd datagridview but this doubles the amount of
code I have to right and layout isn't as nice without a lot of code in
Resize events. No matter my current method works fine as long as I can
limit the user to only input digit keys.. so on to part 2 of your code..

I tried your #1 method because I do want to validate on each single key
press (I want non-numeric keys to not even show up). The
EditingControlShowing event only fires when I first enter the cell. This
appears to be what you've intended it to do. However, the other event
TextBox_KeyDown never fires once I'm in edit mode in the cell. Why would
this event never trigger? Any ideas?

Thanks again for your detailed response..

Ryan

""Jeffrey Tan[MSFT]"" <je***@online.microsoft.comwrote in message
news:XL****************@TK2MSFTNGHUB02.phx.gbl...
>Hi Ryan,

I have written a test sample regarding your scenario, however, I found
that
once the DataColumn type in the datasource is of type "Integer", the
DataGridView will format the data into "#,###" without any problem. The
full sample code is listed below:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim dt As DataTable = New DataTable("test")
dt.Columns.Add(New DataColumn("column1", GetType(Integer)))
dt.Columns.Add(New DataColumn("column2", GetType(String)))

Dim i As Integer
For i = 0 To 5
Dim dr As DataRow
dr = dt.NewRow()
dr("column1") = i * 1000
dr("column2") = "item" + i.ToString()
dt.Rows.Add(dr)
Next

Me.Column1.DataPropertyName = "column1"
Me.Column1.ValueType = GetType(Integer)
Me.Column1.DefaultCellStyle.Format = "#,###"

Me.Column2.DataPropertyName = "column2"
Me.Column1.ValueType = GetType(String)

Me.DataGridView1.DataSource = dt
End Sub

Note: Column1 and Column2 are 2 DataGridViewTextBoxColumn(s) added from
designer.

Based on my test, once the "column1" is of type "Integer", the manually
input integer data in "column1" will be formatted into "#,###" style
without any problem. So, do you use "Integer" type in the datacolumn?

To restrict the user input as numeric data, you have 2 approaches:

#1, Restrict single key input to decimal character. You may get this done
by register the KeyDown event of TextBox in the DataGridViewTextBoxColumn
and filter non-decimal keys with SuppressKeyPress property, the sample
code
is listed below:

Private Sub DataGridView1_EditingControlShowing(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.DataGridViewEditingControlSh owingEventArgs) Handles
DataGridView1.EditingControlShowing

If Me.DataGridView1.CurrentCell.ColumnIndex = 0 And Not e.Control
Is Nothing Then
Dim tb As TextBox = CType(e.Control, TextBox)
AddHandler tb.KeyDown, AddressOf TextBox_KeyDown
End If

End Sub

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.KeyEventArgs)
If Keys.D0 < e.KeyData And e.KeyData < Keys.D9 Then
e.Handled = False
Else
e.Handled = True
e.SuppressKeyPress = True
End If
End Sub

The sample code handles DataGridView.EditingControlShowing event and get
the TextBox reference through e.Control property. Then it registers the
event for TextBox.

#2, Do not validate the single key press, but delay the validation to the
commit time. You may handle DataGridView.CellValidating event and
validate
the UI input. If the input is incorrect(based on the Integer.TryPause
result), you may set Cancel property to true to cancel the commit:

Private Sub DataGridView1_CellValidating(ByVal sender As
System.Object,
ByVal e As System.Windows.Forms.DataGridViewCellValidatingEve ntArgs)
Handles DataGridView1.CellValidating
If e.ColumnIndex = 0 And Not e.FormattedValue = String.Empty Then

Dim i As Integer
Dim str As String = CType(e.FormattedValue, String)
str = str.Replace(",", String.Empty)
If Not Integer.TryParse(str, i) Then
MsgBox("Please input integer data")
e.Cancel = True
End If

End If
End Sub

These 2 code snippets both work well on my side. You may choose one of
these 2 solutions based on your application's requirement.

Hope this helps.

Best regards,
Jeffrey Tan
Microsoft Online Community Support
================================================= =
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================= =
This posting is provided "AS IS" with no warranties, and confers no
rights.


Jan 5 '07 #5

P: n/a
Turns out I spoke too soon. This event handles all cells in the column so
now it limits my input into other cells in the column that I want to allow
users to input alphanumeric data.

"Ryan" <Ty****@newsgroups.nospamwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...
Ok, I got it working, just had to modify a few things in the code.
Instead of using a TextBox control I used a
DataGridViewTextBoxEditingControl. Also changed it so it excepts the
number pad as well.

Ryan

If Not e.Control Is Nothing Then

Dim tb As DataGridViewTextBoxEditingControl = CType(e.Control,
DataGridViewTextBoxEditingControl)

AddHandler tb.KeyDown, AddressOf TextBox_KeyDown

End If

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.KeyEventArgs)

If (Keys.D0 <= e.KeyData And e.KeyData <= Keys.D9) Or (Keys.NumPad0 <=
e.KeyData And e.KeyData <= Keys.NumPad9) Then

e.Handled = False

Else

e.Handled = True

e.SuppressKeyPress = True

End If

End Sub

"Ryan" <Ty****@newsgroups.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>Thanks for the help. I don't want to set the type of the column to
Integer because I have other rows at the bottom of the datagrid that are
not Integer type (Account Codes which contain alphanumeric values).
Still thanks for mentioning this I wasn't aware of how to set the
datatype and it could come in handy for other projects. I considered
making the non-integer rows part of a 2nd datagridview but this doubles
the amount of code I have to right and layout isn't as nice without a lot
of code in Resize events. No matter my current method works fine as long
as I can limit the user to only input digit keys.. so on to part 2 of
your code..

I tried your #1 method because I do want to validate on each single key
press (I want non-numeric keys to not even show up). The
EditingControlShowing event only fires when I first enter the cell. This
appears to be what you've intended it to do. However, the other event
TextBox_KeyDown never fires once I'm in edit mode in the cell. Why would
this event never trigger? Any ideas?

Thanks again for your detailed response..

Ryan

""Jeffrey Tan[MSFT]"" <je***@online.microsoft.comwrote in message
news:XL****************@TK2MSFTNGHUB02.phx.gbl. ..
>>Hi Ryan,

I have written a test sample regarding your scenario, however, I found
that
once the DataColumn type in the datasource is of type "Integer", the
DataGridView will format the data into "#,###" without any problem. The
full sample code is listed below:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim dt As DataTable = New DataTable("test")
dt.Columns.Add(New DataColumn("column1", GetType(Integer)))
dt.Columns.Add(New DataColumn("column2", GetType(String)))

Dim i As Integer
For i = 0 To 5
Dim dr As DataRow
dr = dt.NewRow()
dr("column1") = i * 1000
dr("column2") = "item" + i.ToString()
dt.Rows.Add(dr)
Next

Me.Column1.DataPropertyName = "column1"
Me.Column1.ValueType = GetType(Integer)
Me.Column1.DefaultCellStyle.Format = "#,###"

Me.Column2.DataPropertyName = "column2"
Me.Column1.ValueType = GetType(String)

Me.DataGridView1.DataSource = dt
End Sub

Note: Column1 and Column2 are 2 DataGridViewTextBoxColumn(s) added from
designer.

Based on my test, once the "column1" is of type "Integer", the manually
input integer data in "column1" will be formatted into "#,###" style
without any problem. So, do you use "Integer" type in the datacolumn?

To restrict the user input as numeric data, you have 2 approaches:

#1, Restrict single key input to decimal character. You may get this
done
by register the KeyDown event of TextBox in the
DataGridViewTextBoxColumn
and filter non-decimal keys with SuppressKeyPress property, the sample
code
is listed below:

Private Sub DataGridView1_EditingControlShowing(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.DataGridViewEditingControlS howingEventArgs) Handles
DataGridView1.EditingControlShowing

If Me.DataGridView1.CurrentCell.ColumnIndex = 0 And Not e.Control
Is Nothing Then
Dim tb As TextBox = CType(e.Control, TextBox)
AddHandler tb.KeyDown, AddressOf TextBox_KeyDown
End If

End Sub

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.KeyEventArgs)
If Keys.D0 < e.KeyData And e.KeyData < Keys.D9 Then
e.Handled = False
Else
e.Handled = True
e.SuppressKeyPress = True
End If
End Sub

The sample code handles DataGridView.EditingControlShowing event and get
the TextBox reference through e.Control property. Then it registers the
event for TextBox.

#2, Do not validate the single key press, but delay the validation to
the
commit time. You may handle DataGridView.CellValidating event and
validate
the UI input. If the input is incorrect(based on the Integer.TryPause
result), you may set Cancel property to true to cancel the commit:

Private Sub DataGridView1_CellValidating(ByVal sender As
System.Object,
ByVal e As System.Windows.Forms.DataGridViewCellValidatingEve ntArgs)
Handles DataGridView1.CellValidating
If e.ColumnIndex = 0 And Not e.FormattedValue = String.Empty Then

Dim i As Integer
Dim str As String = CType(e.FormattedValue, String)
str = str.Replace(",", String.Empty)
If Not Integer.TryParse(str, i) Then
MsgBox("Please input integer data")
e.Cancel = True
End If

End If
End Sub

These 2 code snippets both work well on my side. You may choose one of
these 2 solutions based on your application's requirement.

Hope this helps.

Best regards,
Jeffrey Tan
Microsoft Online Community Support
================================================ ==
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach
the
most efficient resolution. The offering is not appropriate for
situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================ ==
This posting is provided "AS IS" with no warranties, and confers no
rights.



Jan 5 '07 #6

P: n/a
Bah sorry about all the posting. I just moved the condition to check the
current cell to my KeyDown handler so it only limits keys if they're in the
right cell. Easy fix.

"Ryan" <Ty****@newsgroups.nospamwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
Turns out I spoke too soon. This event handles all cells in the column so
now it limits my input into other cells in the column that I want to allow
users to input alphanumeric data.

"Ryan" <Ty****@newsgroups.nospamwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...
>Ok, I got it working, just had to modify a few things in the code.
Instead of using a TextBox control I used a
DataGridViewTextBoxEditingControl. Also changed it so it excepts the
number pad as well.

Ryan

If Not e.Control Is Nothing Then

Dim tb As DataGridViewTextBoxEditingControl = CType(e.Control,
DataGridViewTextBoxEditingControl)

AddHandler tb.KeyDown, AddressOf TextBox_KeyDown

End If

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.KeyEventArgs)

If (Keys.D0 <= e.KeyData And e.KeyData <= Keys.D9) Or (Keys.NumPad0 <=
e.KeyData And e.KeyData <= Keys.NumPad9) Then

e.Handled = False

Else

e.Handled = True

e.SuppressKeyPress = True

End If

End Sub

"Ryan" <Ty****@newsgroups.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>>Thanks for the help. I don't want to set the type of the column to
Integer because I have other rows at the bottom of the datagrid that are
not Integer type (Account Codes which contain alphanumeric values).
Still thanks for mentioning this I wasn't aware of how to set the
datatype and it could come in handy for other projects. I considered
making the non-integer rows part of a 2nd datagridview but this doubles
the amount of code I have to right and layout isn't as nice without a
lot of code in Resize events. No matter my current method works fine as
long as I can limit the user to only input digit keys.. so on to part 2
of your code..

I tried your #1 method because I do want to validate on each single key
press (I want non-numeric keys to not even show up). The
EditingControlShowing event only fires when I first enter the cell.
This appears to be what you've intended it to do. However, the other
event TextBox_KeyDown never fires once I'm in edit mode in the cell.
Why would this event never trigger? Any ideas?

Thanks again for your detailed response..

Ryan

""Jeffrey Tan[MSFT]"" <je***@online.microsoft.comwrote in message
news:XL****************@TK2MSFTNGHUB02.phx.gbl.. .
Hi Ryan,

I have written a test sample regarding your scenario, however, I found
that
once the DataColumn type in the datasource is of type "Integer", the
DataGridView will format the data into "#,###" without any problem. The
full sample code is listed below:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim dt As DataTable = New DataTable("test")
dt.Columns.Add(New DataColumn("column1", GetType(Integer)))
dt.Columns.Add(New DataColumn("column2", GetType(String)))

Dim i As Integer
For i = 0 To 5
Dim dr As DataRow
dr = dt.NewRow()
dr("column1") = i * 1000
dr("column2") = "item" + i.ToString()
dt.Rows.Add(dr)
Next

Me.Column1.DataPropertyName = "column1"
Me.Column1.ValueType = GetType(Integer)
Me.Column1.DefaultCellStyle.Format = "#,###"

Me.Column2.DataPropertyName = "column2"
Me.Column1.ValueType = GetType(String)

Me.DataGridView1.DataSource = dt
End Sub

Note: Column1 and Column2 are 2 DataGridViewTextBoxColumn(s) added from
designer.

Based on my test, once the "column1" is of type "Integer", the manually
input integer data in "column1" will be formatted into "#,###" style
without any problem. So, do you use "Integer" type in the datacolumn?

To restrict the user input as numeric data, you have 2 approaches:

#1, Restrict single key input to decimal character. You may get this
done
by register the KeyDown event of TextBox in the
DataGridViewTextBoxColumn
and filter non-decimal keys with SuppressKeyPress property, the sample
code
is listed below:

Private Sub DataGridView1_EditingControlShowing(ByVal sender As
System.Object, ByVal e As
System.Windows.Forms.DataGridViewEditingControl ShowingEventArgs)
Handles
DataGridView1.EditingControlShowing

If Me.DataGridView1.CurrentCell.ColumnIndex = 0 And Not
e.Control
Is Nothing Then
Dim tb As TextBox = CType(e.Control, TextBox)
AddHandler tb.KeyDown, AddressOf TextBox_KeyDown
End If

End Sub

Private Sub TextBox_KeyDown(ByVal sender As System.Object, ByVal e
As
System.Windows.Forms.KeyEventArgs)
If Keys.D0 < e.KeyData And e.KeyData < Keys.D9 Then
e.Handled = False
Else
e.Handled = True
e.SuppressKeyPress = True
End If
End Sub

The sample code handles DataGridView.EditingControlShowing event and
get
the TextBox reference through e.Control property. Then it registers the
event for TextBox.

#2, Do not validate the single key press, but delay the validation to
the
commit time. You may handle DataGridView.CellValidating event and
validate
the UI input. If the input is incorrect(based on the Integer.TryPause
result), you may set Cancel property to true to cancel the commit:

Private Sub DataGridView1_CellValidating(ByVal sender As
System.Object,
ByVal e As System.Windows.Forms.DataGridViewCellValidatingEve ntArgs)
Handles DataGridView1.CellValidating
If e.ColumnIndex = 0 And Not e.FormattedValue = String.Empty
Then

Dim i As Integer
Dim str As String = CType(e.FormattedValue, String)
str = str.Replace(",", String.Empty)
If Not Integer.TryParse(str, i) Then
MsgBox("Please input integer data")
e.Cancel = True
End If

End If
End Sub

These 2 code snippets both work well on my side. You may choose one of
these 2 solutions based on your application's requirement.

Hope this helps.

Best regards,
Jeffrey Tan
Microsoft Online Community Support
=============================================== ===
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach
the
most efficient resolution. The offering is not appropriate for
situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
=============================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.



Jan 5 '07 #7

P: n/a
Hi Ryan,

Glad to see you have figured out all the obstacles!

Yes, I was also strange to find that
DataGridViewEditingControlShowingEventArgs class did not provide a property
to tell the column index. Further research found that we should get the
current editing cell index from the DataGridView.CurrentCell.ColumnIndex
property, so I provided it in the first reply.

Ok, if you need further help, please feel free to post, thanks

Best regards,
Jeffrey Tan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Jan 8 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.