You are here:

Visual Basic/Entering data into a DataGrid

Advertisement


Question
QUESTION: I have a DataGrid bound to a database table. It works fine if I key-in the data manually.  But when values are calculated automatically, it does not update the database with those values, even if I issue the UPDATE method. It calculates and presents the values correctly in the DataGrid, but ahen I click Save, changes are not saved to the database, and calculated values are erased from the DataGrid.  But if I key-in the values directly into the DataGrid, it works fine. Please, Help! .  See code below.

 
Private Sub mnuCalculate_Click(sender As System.Object, e As System.EventArgs) Handles mnuCalculate.Click
       CalculateMonthlyPayment()
       End Sub

Sub CalculateMonthlyPayment()
         'To calculate monthly payment automatically
Dim decMonthlyPayment, decLoanAmount, decMonthlyRate, decLoanPeriods As Decimal
       For Each row As DataGridViewRow In dgvLoans.Rows
         If Not row.IsNewRow Then
         decLoanAmount = row.Cells(3).Value
         decMonthlyRate = row.Cells(4).Value / 12 / 100
         decLoanPeriods = row.Cells(5).Value * 12
         decMonthlyPayment = decLoanAmount * decMonthlyRate * (1 + decMonthlyRate) ^ decLoanPeriods
         decMonthlyPayment = decMonthlyPayment / (((1 + decMonthlyRate) ^ decLoanPeriods) - 1)
         row.Cells(6).Value = decMonthlyPayment
         dgvLoans.EndEdit()
         TblLoansBindingSource.EndEdit()
         End If

       Next row
   End Sub
'To save DataGrid to the database table
Private Sub mnuSave_Click(sender As System.Object, e As System.EventArgs) Handles mnuSave.Click
       SaveChanges()
   End Sub

Sub SaveChanges()
       'Para compartir el código de almacenar
       Try
         'Para tomar los cambios del record actual, aunque no haya perdido el foco
         dgvLoans.EndEdit()
         'Para tomar los cambios del campo actual, aunque no haya perdido el foco (causa problemas al añadir)
         'TblLoansBindingSource.EndEdit()

         'Para que la actualización se refleje en la base de datos
         Me.TblLoansTableAdapter.Update(Pr14266January2013BankLoansDataSetRevised.tblLoans)

         Me.TblLoansTableAdapter.FillByCustomerId(Me.Pr14266January2013BankLoansDataSetRevised.tblLoans, cboCustomerId.Text)
         CalculateTotals()
         cboCustomerId.Focus()
       Catch ex As Exception
         MessageBox.Show(ex.Message, "Error")
       End Try
   End Sub

ANSWER: Dear Arnaldo,

When you are keying-in data manually, the Grid control knows, that you are trying to change data so it changing the status of the row you are editing to 'Edit', and, because grid is bound, grid automatically saving your changes to database.  When you are trying to do it in the code, grid is not in the 'Edit' mode, so data not saved.

To go to Edit mode row should be Selected - so after:

If Not row.IsNewRow Then

add this code:

row.Selected = True

Should fix your problem.

Regards,

Vitaly

---------- FOLLOW-UP ----------

QUESTION: Thanks for your prompt reaction.  Your explanation is very reasonable and I agree that it should be the cause of this problem.  But I tried it, and it didn't work.  I even tried the following instruction and it did not work either.  row.Cells(6).Selected = True
An additional recommendation will be appreciated.

ANSWER: OK, let's try to do it other way: let's change underlying dataset. You do bound your grid to some dataset , right?

Let's change this line:

For Each row As DataGridViewRow In dgvLoans.Rows

to use dataset row, not grid row:

For Each row As DataRow In dgvLoans.<dataset? DataSource? >

(I put <> because I'm not sure what property is actually your particular grid has)

Also,

Remove if statement and those 2 lines:

        dgvLoans.EndEdit()
        TblLoansBindingSource.EndEdit()

and rebind grid at the end.

Full code will be something like this:


For Each row As DataRow In dgvLoans.<dataset? DataSource? >
        
        decLoanAmount = row.Cells(3).Value
        decMonthlyRate = row.Cells(4).Value / 12 / 100
        decLoanPeriods = row.Cells(5).Value * 12
        decMonthlyPayment = decLoanAmount * decMonthlyRate * (1 + decMonthlyRate) ^ decLoanPeriods
        decMonthlyPayment = decMonthlyPayment / (((1 + decMonthlyRate) ^ decLoanPeriods) - 1)
        row.Cells(6).Value = decMonthlyPayment
        

      Next row
   dgvLoans.Rebind()


If this not working let me know how are you binding grid - I will need to see all your code to debug.


Regards,

Vitaly

---------- FOLLOW-UP ----------

QUESTION: Thanks again.  Sorry to bother you again.  But it didn't work.  Here is the code that I added. Of course I just used one loop or the other.

Sub CalculateMonthlyPayment()
      
       Dim decMonthlyPayment, decLoanAmount, decMonthlyRate, decLoanPeriods As Decimal
EITHER THIS LOOP:
       For Each row In Pr14266January2013BankLoansDataSetRevised.tblLoans.Rows
         row.BeginEdit()
         decLoanAmount = row.fldLoanAmount
         decMonthlyRate = row.fldLoanRate / 12 / 100
         decLoanPeriods = row.fldLoanYears * 12
         decMonthlyPayment = decLoanAmount * decMonthlyRate * (1 + decMonthlyRate) ^ decLoanPeriods
         decMonthlyPayment = decMonthlyPayment / (((1 + decMonthlyRate) ^ decLoanPeriods) - 1)
         row.fldMonthlyPayment = decMonthlyPayment
         row.EndEdit()
       Next

OR THIS ONE:
       For Each row As DataGridViewRow In dgvLoans.Rows
         If Not row.IsNewRow Then
         decLoanAmount = row.Cells(3).Value
         decMonthlyRate = row.Cells(4).Value / 12 / 100
         decLoanPeriods = row.Cells(5).Value * 12
         decMonthlyPayment = decLoanAmount * decMonthlyRate * (1 + decMonthlyRate) ^ decLoanPeriods
         decMonthlyPayment = decMonthlyPayment / (((1 + decMonthlyRate) ^ decLoanPeriods) - 1)
         row.Selected = True
         
         row.Cells(6).Value = decMonthlyPayment
         row.Selected = False

         End If
       Next

   End Sub

Answer
First of all this is my code:


For Each row As DataRow In dgvLoans.<dataset? DataSource? >
       
       decLoanAmount = row.Cells(3).Value
       decMonthlyRate = row.Cells(4).Value / 12 / 100
       decLoanPeriods = row.Cells(5).Value * 12
       decMonthlyPayment = decLoanAmount * decMonthlyRate * (1 + decMonthlyRate) ^ decLoanPeriods
       decMonthlyPayment = decMonthlyPayment / (((1 + decMonthlyRate) ^ decLoanPeriods) - 1)
       row.Cells(6).Value = decMonthlyPayment
       

     Next row
  dgvLoans.Rebind()

And this is yours:

For Each row As DataGridViewRow In dgvLoans.Rows
        If Not row.IsNewRow Then
        decLoanAmount = row.Cells(3).Value
        decMonthlyRate = row.Cells(4).Value / 12 / 100
        decLoanPeriods = row.Cells(5).Value * 12
        decMonthlyPayment = decLoanAmount * decMonthlyRate * (1 + decMonthlyRate) ^ decLoanPeriods
        decMonthlyPayment = decMonthlyPayment / (((1 + decMonthlyRate) ^ decLoanPeriods) - 1)
        row.Selected = True
        
        row.Cells(6).Value = decMonthlyPayment
        row.Selected = False

        End If
      Next

Could you, please, try to use my code without changing it?

And again, to debug I need full project code especially HOW DID YOU BIND YOUR GRID TO DATABASE?

Regards,

Vitaly  

Visual Basic

All Answers


Answers by Expert:


Ask Experts

Volunteer


VITALY

Expertise

Area of expertise is Visual Basic .NET (VS 2008 and Framework 3.5) design and development for Windows applications. You can ask me any Visual Basic questions and some questions about .NET Framework. You can also ask me how to use Component One controls in Visual Basic. Do not ask me questions about Web design and development in Visual Basic.

Experience

I'm working as VB programmer from 1997, so I have a lot of experience with Visual Basic .NET as well as Visual Basic 6. Area of expertise is Visual Basic .NET (VS 2008 and Framework 3.5) design and development for Windows applications; Component One controls for Visual Basic; User controls; Moving VB6 applications to .NET.

Education/Credentials
B.S. in Computer Science

©2016 About.com. All rights reserved.