Datagrid Column4 Value depends on Column5 Value

[gbhs]

Hi To All
I am a VB.Net newbie
In my datagrid called dgvExamScores, if column5 cell is not empty, then column4 is 3 (3 is stored in textbox txtCreditValue).
Code works well But
PROBLEM:
When I delete a column5 value, column4 value of 3 is NOT deleted as expected.
Where is the error in my code?
is this event the correct one to use?
Here is my code

[XCODE]Private Sub dgvExamScores_CellValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvExamScores.CellValidated
For i As Int32 = 0 To Me.dgvExamScores.Rows.Count - 1
If Not IsDBNull(Me.dgvExamScores.Rows(i).Cells(5).Value) Then
Me.dgvExamScores.Rows(i).Cells(4).Value = Me.txtCreditValue.Text
Else
IsDBNull(Me.dgvExamScores.Rows(i).Cells(4).Value)
End If

Next

End Sub[/XCODE]

Anyone Help Please!!!!

 

[IanRyder]

Hi,

The statement in your else clause does not actually do anything. By saying:-

Else
  IsDBNull(Me.dgvExamScores.Rows(i).Cells(4).Value)
End If

All you are actually doing is returning a True or False value based on the contents of Cell(4)?

I think that what you are trying to do is set the value of the cell to null. i.e:-

Else
  Me.dgvExamScores.Rows(i).Cells(4).Value = DBNull.Value
End If

All that being said, you should not be doing this in a For loop to check and update every row in your DataGridView every time any one cell is validated. That's just a waste of processing time. I would suggest that you move this to the CellValueChanged event, get rid of the For Loop, only do something when Column 5 is changed and then just manipulate the current row you are working on. Have a look here:-

Private Sub dgvExamScores_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvExamScores.CellValueChanged
  If e.ColumnIndex = 5 Then
    If Not IsDBNull(Me.dgvExamScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
      Me.dgvExamScores.Rows(e.RowIndex).Cells(4).Value = Me.txtCreditValue.Text
    Else
      Me.dgvExamScores.Rows(e.RowIndex).Cells(4).Value = DBNull.Value
    End If
  End If
End Sub

Hope that helps.

Cheers,

Ian

 

[gbhs]

Thanks Ian
It worked well.
Breakthrough for me

I Will now tackle how to save these datagrid changes to Sql database. I keep having an error about some sqlcommand needed and I dont know how to use SQLCommandBuilder on a datagrid. No thread seems to handle it.

thanks

 

[IanRyder]

Hi,

Here is something I posted elsewhere yesterday. Have a read through and see if it can give you some pointers in what you need to achieve:-

Imports System.Data.SqlClient
 
Public Class Form1
  'Here we define the connection to the database
  Private sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  'Here we define a DataAdapter to hold all the SQL command to update your database
  Private daEmployees As New SqlDataAdapter("Select * From Employees", sqlConn)
  'Here we use a CommandBuilder to specifically create the Update, Insert and Delete commands for the DataAdapter
  Private sqlCmndBuilder As New SqlCommandBuilder(daEmployees)
  'Here we create a DataSet to hold our information
  Private myDS As New DataSet
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    'Here we load our information into our DaatSet using the DataAdapter's Fill method
    daEmployees.Fill(myDS, "Employees")
    'Here we set the DataSource of the DataGridView to the correct Table in the DataSet
    DataGridView1.DataSource = myDS.Tables(0)
  End Sub
 
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    'Since the DataGridView is bound to the DataTable in the Dataset any changes that
    'are made in the DataGridView are mirrored in the underlying table in the Dataset
    'This includes all updates to existing records, inserted rows and deleted rows
    'We can therefore use the Update method of the DataAdapter to persist all your
    'Changes back to the database by passing the correct DataSet table as a parameter
    daEmployees.Update(myDS.Tables(0))
    MsgBox("Employee Details Updated!")
  End Sub
End Class

Hope that helps.

Cheers,

Ian

 

[gbhs]

Hi Ian
Please I get this error
Operator '>=' is not defined for type 'DBNull' and type Integer
The problem is in column11.
Fact is, code runs well but when I delete scores from column 7 and column 8 then error occurs.
Where am I erring in code below.


Private Sub dgvScores_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvScores.CellValueChanged

'RESTRICT SCORE VALUES TO 20 OR LESS 20

If e.ColumnIndex = 7 Then
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
If Me.dgvScores.Rows(e.RowIndex).Cells(7).Value < 0 Or Me.dgvScores.Rows(e.RowIndex).Cells(7).Value > 20 Then
MsgBox("A Score can not be more than 20 or less than 0")
Me.dgvScores.Rows(e.RowIndex).Cells(7).Value = DBNull.Value

End If
End If
End If


'FILL AVG COLUMN i.e. COLUMN 9

If e.ColumnIndex = 7 Or e.ColumnIndex = 8 Then
If IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(7).Value) And IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(8).Value) Then
Me.dgvScores.Rows(e.RowIndex).Cells(9).Value = DBNull.Value
Me.dgvScores.Rows(e.RowIndex).Cells(10).Value = DBNull.Value
'Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = Nothing
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(7).Value) And IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(8).Value) Then
Me.dgvScores.Rows(e.RowIndex).Cells(9).Value = Me.dgvScores.Rows(e.RowIndex).Cells(7).Value
End If
If IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(7).Value) And Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(8).Value) Then
Me.dgvScores.Rows(e.RowIndex).Cells(9).Value = Me.dgvScores.Rows(e.RowIndex).Cells(8).Value
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(7).Value) And Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(8).Value) Then
Me.dgvScores.Rows(e.RowIndex).Cells(9).Value = Math.Round((Me.dgvScores.Rows(e.RowIndex).Cells(7).Value + Me.dgvScores.Rows(e.RowIndex).Cells(8).Value) / 2, 2)
End If
End If



'FILL THE COMMENT COLUMN i.e. Column 11
If e.ColumnIndex = 9 Then
'If IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(7).Value) And IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(8).Value) Then
' Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = Nothing
'End If

'ERROR MESSAGE IS AT THIS LEVEL Operator '>=' is not defined for type 'DBNull' and type Integer

If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 0 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value <= 5) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Very Poor"
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value > 5 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value < 8) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Poor"
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 8 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value < 10) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Below Average"
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 10 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value < 12) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Average Pass"
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 12 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value < 14) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Fair Pass"
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 14 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value < 16) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Good"
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 16 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value < 18) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Very Good"
End If
If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) And (Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 18 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value <= 20) Then
Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Excellent"
End If


End If

End Sub

 

[IanRyder]

Hi,

By using the AND conditional operator in your IF statement you are forcing both sides of the conditional statement to be evaluated regardless of whether your field contains a Null value or not. There are two ways you can go about solving this:-

1) The .NET way to solve this is to use the AndAlso statement to perform this evaluation. Have a look here:-

AndAlso Operator (Visual Basic)

2) Alternatively, and the old way to overcome this, is to break down the IF statement to ensure you are not performing a calculation on a Null value. i.e:-

If Not IsDBNull(Me.dgvScores.Rows(e.RowIndex).Cells(e.Col umnIndex).Value) then 
  If Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value >= 0 And Me.dgvScores.Rows(e.RowIndex).Cells(e.ColumnIndex).Value <= 5 Then
    Me.dgvScores.Rows(e.RowIndex).Cells(11).Value = "Very Poor"
  End If
End If

Hope that helps.

Cheers,

Ian

 

[gbhs]

Thanks
The problem is resolved.

The Clear button is not giving required results.
I have a form bound to 2 unrelated tables table1 and table2.
The form has 3 buttons.

btnDisplay for loading data to form when correct passwork entered - its code works well.
btnSave for saving data entered to both tables - its code works well.
btnClear for clearing all controls and preparing form for new record entry - Problem here.

table1 is bound to all textboxes
table2 is bound to the datagrid dgvScores

The fields of table2 that populate the datagrid depend on different SQLStrings
(eg strSQL1 loads Name,Course,Exam1 fields
strSQL2 loads Name,Course,Exam2 fields
strSQL3 loads Name,Course,Exam3 fields etc)

The SQLStr called depends on the number in a certain textbox (eg if txtExam.text=1 then strSQL1 is called etc)

PROBLEM:
Assuming DA1 , DA2 are respective dataadapters and DS1 , DS2 are respective datasets,
how can I code the Clear button btnClear so that after data entry I can clear the form
and start new record data entry without closing the form.

(NB- I try not to use wizards. I use toolbox controls and code)

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
DS1.Clear()
DS2.Clear()

dgvScores.Refresh()
dgvScores.DataSource = Nothing
dgvScores.Columns.Clear()
dgvScores.Rows.Clear()

DA1.Dispose()
DA2.Dispose()

If Conn.State = ConnectionState.Open Then
Conn.Close()
End If

For Each item In Me.GroupBox1.Controls
If TypeOf item Is TextBox Then
item.text = String.Empty
End If
Next
For Each item In Me.GroupBox2.Controls
If TypeOf item Is TextBox Then
item.text = String.Empty
End If
Next
For Each item In Me.GroupBox3.Controls
If TypeOf item Is TextBox Then
item.text = String.Empty
End If
Next

For Each item In Me.GroupBox5.Controls
If TypeOf item Is TextBox Then
item.text = String.Empty
End If
Next
txtPassword.Focus()
End Sub