locked
Set valid data for datagridview cell RRS feed

  • Question

  • Hello,

    i have datagridview which have some numeric columns and others for prices.

    i need to allow only some data to be inserted in these  columns' cells

    i have a code which used for normal text box

            '97 - 122 = Ascii codes for simple letters
            '65 - 90  = Ascii codes for capital letters
            '48 - 57  = Ascii codes for numbers
    
            If Asc(e.KeyChar) <> 8 Then
                If Asc(e.KeyChar) < 48 Or Asc(e.KeyChar) > 57 Then
                    e.Handled = True
                End If
            End If

    and for prices

     Dim allowed As String = "1234567890." & Chr(8)
            If Not allowed.Contains(e.KeyChar) Then
                e.Handled = True
                Exit Sub
            End If
            If e.KeyChar = "."c Then
                If TotalTB.Text.Contains("."c) Then
                    e.Handled = True
                    Exit Sub
                End If
            Else
                If Not e.KeyChar = Chr(8) AndAlso TotalTB.Text.Contains("."c) Then
                    If TotalTB.Text.Length - 2 > TotalTB.Text.IndexOf("."c) Then
                        e.Handled = True
                        Exit Sub
                    End If
                End If
            End If
    can i do somthing similar or close to that to these datagridview's cells?

    Saturday, August 31, 2019 8:53 PM

Answers

  • Hi

    Not sure if this will help, but here it is anyway.

    This uses a DataTable as the DataSource for the DataGridView. (always a good idea)

    The DataTable columns have been set to particulat Types, Integer and Double. (any others can be used as well).

    Once the code is running,the DataGridView is shown with the sample values as set in the code.

    If the User then adds a new row (by filling in the new row cells), or, edits an existing cell value, if the type of the input doesn't match the required a MessageBox pops up and complains (the faulty entry is rejected).

    What this all means, for example, is that is the User can only enter an Integer in the Integer column or a Double in the Double column (an Integer is acceptable in the Double column but not the other way around) - otherwise the MessageBox/Edit is necessary.

    This code is a very simplified example.

    ' Form1 with a blank DataGridView 
    ' named DGV
    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim dt As New DataTable("Freddy")
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		With dt
    			.Columns.Add("Integer", GetType(Integer))
    			.Columns.Add("Double", GetType(Double))
    
    			For i As Integer = 1 To 10
    				.Rows.Add(i, i / 3)
    			Next
    		End With
    		DGV.DataSource = dt
    	End Sub
    	Private Sub DGV_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DGV.DataError
    		If e.Exception.Message = "Input string was not in a correct format." Then
    			MessageBox.Show("Input was not in the correct format, please edit")
    			e.ThrowException = False
    		End If
    	End Sub
    End Class


    Regards Les, Livingston, Scotland

    • Marked as answer by AbanoubZak Sunday, September 1, 2019 6:37 PM
    Saturday, August 31, 2019 10:22 PM

All replies

  • Hi

    Not sure if this will help, but here it is anyway.

    This uses a DataTable as the DataSource for the DataGridView. (always a good idea)

    The DataTable columns have been set to particulat Types, Integer and Double. (any others can be used as well).

    Once the code is running,the DataGridView is shown with the sample values as set in the code.

    If the User then adds a new row (by filling in the new row cells), or, edits an existing cell value, if the type of the input doesn't match the required a MessageBox pops up and complains (the faulty entry is rejected).

    What this all means, for example, is that is the User can only enter an Integer in the Integer column or a Double in the Double column (an Integer is acceptable in the Double column but not the other way around) - otherwise the MessageBox/Edit is necessary.

    This code is a very simplified example.

    ' Form1 with a blank DataGridView 
    ' named DGV
    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim dt As New DataTable("Freddy")
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		With dt
    			.Columns.Add("Integer", GetType(Integer))
    			.Columns.Add("Double", GetType(Double))
    
    			For i As Integer = 1 To 10
    				.Rows.Add(i, i / 3)
    			Next
    		End With
    		DGV.DataSource = dt
    	End Sub
    	Private Sub DGV_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DGV.DataError
    		If e.Exception.Message = "Input string was not in a correct format." Then
    			MessageBox.Show("Input was not in the correct format, please edit")
    			e.ThrowException = False
    		End If
    	End Sub
    End Class


    Regards Les, Livingston, Scotland

    • Marked as answer by AbanoubZak Sunday, September 1, 2019 6:37 PM
    Saturday, August 31, 2019 10:22 PM
  • Thanks for the reply.

    i will just use the second part for now till i find a way.

    Private Sub DGV_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DGV.DataError
    		If e.Exception.Message = "Input string was not in a correct format." Then
    			MessageBox.Show("Input was not in the correct format, please edit")
    			e.ThrowException = False
    		End If
    	End Sub

    Sunday, September 1, 2019 6:37 PM
  • Thanks for the reply.

    i will just use the second part for now till i find a way.


    Hi

    Yes, the second part was the code towards your question - the first part was only to set things up for the example.


    Regards Les, Livingston, Scotland

    Sunday, September 1, 2019 6:55 PM
  • Thanks for the reply.

    i will just use the second part for now till i find a way.


    Hi

    Yes, the second part was the code towards your question - the first part was only to set things up for the example.


    Regards Les, Livingston, Scotland

    Data source property already done that part for me as i'm using sql datatable, i was searching for something similar or close enough  to what i did to the text box for example when i use the following code on a text box keypress event it only accept numbers press.(no letters, symbols....etc )

    If Asc(e.KeyChar) <> 8 Then
                If Asc(e.KeyChar) < 48 Or Asc(e.KeyChar) > 57 Then
                    e.Handled = True
                End If
            End If
    thanks again.



    • Edited by AbanoubZak Sunday, September 1, 2019 7:27 PM
    Sunday, September 1, 2019 7:26 PM
  • Hi

    Here is some code to try. This code uses the DataGridView Cell EditingControl which is itself a TextBox.

    Give this a try and see if it is closer to your expectations.

    It still needs the DataError handler to cater for the difference between Integer/Double (Decimal), but can be made to silently deal with such errors by commenting out the MessageBox line.

    EDIT: updated below code to a slightly better version

    ' Form1 with a BLANK
    ' DataGridView named DGV
    Option Strict On
    Option Explicit On
    Public Class Form1
        ' this is a datatable for this
        ' example only
        Dim dt As New DataTable("Freddy")

        Dim c As TextBox = Nothing
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ' this is just some test data for this
            ' example only
            With dt
                ' these are the only column types
                ' catered for in this example
                .Columns.Add("Integer", GetType(Integer))
                .Columns.Add("Double", GetType(Double))
                .Columns.Add("Decimal", GetType(Decimal))
                .Columns.Add("String", GetType(String))

                For i As Integer = 1 To 10
                    .Rows.Add(i, i / 3, i / 5, "Some String")
                Next
            End With
            DGV.DataSource = dt
        End Sub
        Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles DGV.EditingControlShowing
            c = CType(e.Control, TextBox)
            AddHandler CType(c, TextBox).KeyPress, AddressOf DGV_Keys
        End Sub
        Private Sub DGV_Keys(ByVal sender As Object, ByVal e As KeyPressEventArgs)
            Dim typ As Type = dt.Columns(DGV.CurrentCell.ColumnIndex).DataType
            c.SelectedText = Nothing
            If typ = GetType(Integer) Or typ = GetType(Double) Or typ = GetType(Decimal) Then
                Dim allow As String = "0123456789" & Chr(8)
                If Not typ = GetType(Integer) AndAlso Not c.Text.Contains(".") Then allow &= "."
                If Not c.Text.Contains("-") AndAlso c.SelectionStart = 0 Then allow &= "-"
                If Not Allow.Contains(e.KeyChar) Then
                    e.Handled = True
                End If
            End If
        End Sub
        Private Sub DGV_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DGV.DataError
            If e.Exception.Message = "Value was either too large or too small for an Int32." Or e.Exception.Message = "Input string was not in a correct format." Then
                ' remove MessageBox line for silent
                ' handling of this exception
                MessageBox.Show("Wrong Input type for thatb column, please edit")
                c.Text = Nothing
                e.ThrowException = False
            End If
        End Sub
    End Class


    Regards Les, Livingston, Scotland



    • Edited by leshay Monday, September 2, 2019 5:31 PM
    Sunday, September 1, 2019 10:11 PM