Datagridview Error

In my datagridview I have 3 columns, lets say Column1, 2, 3.

Column1 is Column2 / Column3.

Problem I have is I'm trying to prevent an error if the user types "0" into Column 2 or 3. I can prevent against it dividing by null or a letter by the following codes:

Code:

---

If Not IsNull(Me.DataDataGridView1.CurrentRow.Cells("Column2").Value) Then

---

and

Code:

---

If IsNumeric(Me.DataDataGridView1.CurrentRow.Cells("FieldName").Value) Then

---

But, I can't protect it from someone typing "0" because zero is numeric and

Code:

---

If Me.DataDataGridView1.CurrentRow.Cells("FieldName").Value = 0 Then

---

causes problems as well because it treats 0 as null.

Any ideas are appreciated.

i don't think you need to handle this issue this way

i would like to say
(1) you cross check numeric values
(2) you cross check null entries, i mean ""

then come to division part.

it is up to you which column value should be more & which should be less
but still if the value is 0 then handle it with in the function which returns the column 1 cell value accordingly .. check this function.

vb.net Code:

Private Function JpiPer() As Double
        Dim Col_2value As Double = 10
        Dim Col_3value As Double = 0
        Dim outPut As Double
 
 
        If Double.IsInfinity(Col_2value / Col_3value) Then
            outPut = 0
        Else
            outPut = Col_2value / Col_3value
        End If
 
        MessageBox.Show(outPut)
 
        Return outPut
    End Function

This works fine if I'm using existing data, but when I add a row it gives me a dbnull crash. Also I can get it to update the text in the message box to what it should say but I cannot get the value displayed in the message box to display in Column 1.


All I'm looking for is the the following:

Column1 = Column2 / Column3

BUT.....

My main goal is to allow the user to leave Columns 2 and 3 Null, type letters, do anything you want to do, and it only calculates Column 1 if Column 2 and 3 contain numbers (greater than 0).

Thanks

Code:

---

but when I add a row

---

(1) how u are adding the row & then performing what action fallow correct sequence

Code:

---

All I'm looking for is the the following:
Column1 = Column2 / Column3

---

(2) that is what the above function is doing

Code:

---

allow the user to leave Columns 2 and 3 Null, type letters,

---

if so manipulate it in your code as if , column values are irrelevant then replace it with 0

the best solution is show your code

You can handle dealing with not allowing zero in CellValidating event of the DataGridView.

Example, we have a column named DepartmentNumberColumn which only numerics are allows and on edit or add we have a default value of 100 we can do the following

Code:

---

    Sub DataGridView1_CellValidating( _
        ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) _
        Handles DataGridView1.CellValidating

        Dim GridView As DataGridView = DataGridView1

        Dim cell As DataGridViewCell = GridView.Item(e.ColumnIndex, e.RowIndex)

        If cell.IsInEditMode Then
            Dim c As Control = GridView.EditingControl

            Select Case GridView.Columns(e.ColumnIndex).Name
                Case "DepartmentNumberColumn"
                    c.Text = NumberCleaner(c.Text)
            End Select
        End If

    End Sub

    Function NumberCleaner(ByVal str As String) As String
        Dim Value As String = System.Text.RegularExpressions.Regex.Replace(str, "[a-zA-Z\b\s-.]", "")
        If Value.Length > 0 Then
            If Value = "0" Then
                Value = "100"
            End If
        Else
            Value = "100"
        End If
        Return Value
    End Function

---

Then for new rows suggest default values

Code:

---

    Private Sub DataGridView1_DefaultValuesNeeded( _
        ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) _
        Handles DataGridView1.DefaultValuesNeeded

        e.Row.Cells("DepartmentNameColumn").Value = "English"
        e.Row.Cells("DepartmentNumberColumn").Value = 100
    End Sub

---

If you want to indicate to the user the problem then look at setting the cell's ErrorText when there is an issue and then when the issue is resolved remove the ErrorText.

Thanks Kevin, I'm with you here, just would appreciate it if you could try to picture my project and maybe give me a some additional pointers for validating.

I'm building a grid for users to add rows for new tests, probably 4 to 8 tests(rows) combined on each sample(form with sample info). When you add a new sample(refresh form) the grid will start over blank from a new databound relationship row added. My original plan was to have each sample start with an empty grid with 9 columns. Col1 is an ID, it will have default value. Col2 is an "age" column, the user enters how many days from the "sample date" the test is to occur. Col3 is a calculated date of user entered textbox date + Col2 (you helped me with this one). Col4 user enters a diameter. Col5 is an area calculation based off diameter entered in Col4. Col6 is pounds of force that the user enters. Col7 is calculation of PSI which is Col6 divided by Col5. Col8 is default "Specified PSI". Col9 is Percent of PSI which is Col7 / Col8.

Basically you can see how nulls and zeros and letters can cause a major problem for me, especially when creating the next rows by arrowing down and when dividing by other calculated columns. I have it all coded so the math's good. What I need help with is making an error proof grid that will take whatever the user throws at it. Preferably I'd like the grid to only calculate if there is a number in the applicable text boxes, otherwise it ignores the calculation and you can type anything in a cell - letters, zeros, leave it blank, it don't matter. I tried message boxes and it is a pain because they pop up when you make a new row and I just think they are annoying. I can get it to work most of the time using ISDBNULL or ISNUMERIC but some zeros still slip through and saying =0 or <1 is like saying "null" which will make it fire when a new row is added.

I know, it sounds more like an excel spreadsheet than a database. Not really a wise idea for my first ever program but hey, swing for the fences right :) Thanks for helping guys.

Quote:

---

Originally Posted by jpiper

Thanks Kevin, I'm with you here, just would appreciate it if you could try to picture my project and maybe give me a some additional pointers for validating.

I'm building a grid for users to add rows for new tests, probably 4 to 8 tests(rows) combined on each sample(form with sample info). When you add a new sample(refresh form) the grid will start over blank from a new databound relationship row added. My original plan was to have each sample start with an empty grid with 9 columns. Col1 is an ID, it will have default value. Col2 is an "age" column, the user enters how many days from the "sample date" the test is to occur. Col3 is a calculated date of user entered textbox date + Col2 (you helped me with this one). Col4 user enters a diameter. Col5 is an area calculation based off diameter entered in Col4. Col6 is pounds of force that the user enters. Col7 is calculation of PSI which is Col6 divided by Col5. Col8 is default "Specified PSI". Col9 is Percent of PSI which is Col7 / Col8.

Basically you can see how nulls and zeros and letters can cause a major problem for me, especially when creating the next rows by arrowing down and when dividing by other calculated columns. I have it all coded so the math's good. What I need help with is making an error proof grid that will take whatever the user throws at it. Preferably I'd like the grid to only calculate if there is a number in the applicable text boxes, otherwise it ignores the calculation and you can type anything in a cell - letters, zeros, leave it blank, it don't matter. I tried message boxes and it is a pain because they pop up when you make a new row and I just think they are annoying. I can get it to work most of the time using ISDBNULL or ISNUMERIC but some zeros still slip through and saying =0 or <1 is like saying "null" which will make it fire when a new row is added.

I know, it sounds more like an excel spreadsheet than a database. Not really a wise idea for my first ever program but hey, swing for the fences right :) Thanks for helping guys.

---

Unfortunately adding and editing within a DataGridView is possible in short as you have learned already things get messy fast. This is why I only allow add/edit by showing a modal dialog for these purposes where it is simpler to manage these operations. My thought is just because you can add and edit in a DataGridView does not mean you should without understanding how to implement and how to maintain code later.

What I would suggest is if the user is entering many records at once to use a modal dialog, they press save, validation is done then save the data to a BindingSource which is the DataSource of the DataGridView. The modal dialog only closes when the user presses the dialog’s close button.