Datagridview behaviour problem
Some time ago I wrote a program in vb that imports data from an Access database into a datagridview. The imported file leaves some cells unpopulated. The program performs a series of calculations based on the contents of various cells in the grid and writes the results to the empty cells. Ultimately, the program writes the grid back to the original Access database. All of this works great with no problems; however, I decided to get the datagridview information directly from an Excel spreadsheet using:
Code:
Imports System.IO
Imports ExcelDataReader
Public Class Form1
Dim tables As DataTableCollection
Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "Excel 97-2003 Workbook | *.xlsx"}
If ofd.ShowDialog() = DialogResult.OK Then
txtFileName.Text = ofd.FileName
Using stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read)
Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(stream)
Dim result As DataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
.ConfigureDataTable = Function(__) New ExcelDataTableConfiguration() With {
.UseHeaderRow = True}})
tables = result.Tables
cboSheet.Items.Clear()
For Each table As DataTable In tables
cboSheet.Items.Add(table.TableName)
Next
End Using
End Using
End If
End Using
End Sub
Code:
Private Sub cboSheet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSheet.SelectedIndexChanged
Dim dt As DataTable = tables(cboSheet.SelectedItem.ToString())
dgvPrice.DataSource = dt
End Sub
This imports the data fine, but, if I use the same code for populating empty cells as I did with the Access import, the empty cells don't populate even though, in debug mode, I can see that the variables are calculating correctly. It simply won't populate the cells in the grid and I can't work out why it works in the first instance and not in the second.
The following code is being used in both cases to populate the empty cells in both cases - works in one and not in the other.
Code:
Private Sub btnCalc_Click(sender As Object, e As EventArgs) Handles btnCalc.Click
Dim cpc As Integer
Dim tot(300)
Dim c As Integer
Dim smallmin As Double
For i = 0 To dgvPrice.RowCount - 1
If IsDBNull(dgvPrice.Rows(i).Cells(21).Value) Then 'References CountPriceComp - if it's null...............................
If Not IsDBNull(dgvPrice.Rows(i).Cells(23).Value) Then 'References BestDTB - if it's not null..................................
dgvPrice.Rows(i).Cells(26).Value = dgvPrice.Rows(i).Cells(25).Value 'MinFromSelection = Selection
End If
End If
If Not IsDBNull(dgvPrice.Rows(i).Cells(21).Value) Then 'References CountPriceComp - if it's not null...............................
cpc = dgvPrice.Rows(i).Cells(21).Value 'set variable cpc to be value of count price compare code
c = i 'sets variable c to row index
For x = 0 To cpc - 1 'sets variable x to count of price compare code and cycles through rows to get selection prices for the number of same price compare codes
tot(x) = dgvPrice.Rows(c).Cells(25).Value
If c < dgvPrice.RowCount - 1 Then
c = c + 1
ElseIf c > dgvPrice.RowCount - 1 Then
Exit Sub
End If
Next x
For x = 0 To cpc - 1
If Not IsDBNull(tot(x)) Then
If tot(x) > 0 Then smallmin = tot(x)
End If
Next x
'ASSIGN SMALL MIN TO MIMIMUM FROM SELECTION COLUMN
For y = 0 To cpc - 1
If Not IsDBNull(tot(y)) Then
If tot(y) < smallmin And tot(y) <> 0 Then smallmin = tot(y)
End If
Next y
For j = 0 To cpc - 1
dgvPrice.Rows(i).Cells(26).Value = smallmin
i = i + 1
Next j
i = i - 1
cpc = 0
smallmin = 0
End If
Next i
End Sub
Any help would be appreciated.
Additionally, the code used for importing the Access database is:
Code:
Private Sub Pricing_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Using OleDBConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Access\Test.mdb;")
Using Phase7DA As New OleDb.OleDbDataAdapter("select * from UploadPricing order by PriceComp", OleDBConn)
With Phase7DA
.MissingSchemaAction = MissingSchemaAction.AddWithKey
.Fill(Dset, "Pricing")
End With
End Using
End Using
DgvPrice.DataSource = Dset.Tables(0)
End sub
Re: Datagridview behaviour problem
That is terrible code. You are incrementing and decrementing a For loop counter inside the loop. NEVER do that. Who knows whether it gets done the right number of times. It's hard to say what that code does right or wrong because it's definitely bad, looks rather convoluted and contains a single comment. I would suggest going back to the drawing board, developing an algorithm and then implementing that in code and NOT changing the value of a loop counter inside the loop. All I can suggest with the current code is to debug it by stepping through it line by line and examining every variable at every step to see if and where reality differs from your expectation. If it never does then clearly there's something amiss with your expectation.
Re: Datagridview behaviour problem
Quote:
Originally Posted by
jmcilhinney
That is terrible code. You are incrementing and decrementing a For loop counter inside the loop. NEVER do that. Who knows whether it gets done the right number of times. It's hard to say what that code does right or wrong because it's definitely bad, looks rather convoluted and contains a single comment. I would suggest going back to the drawing board, developing an algorithm and then implementing that in code and NOT changing the value of a loop counter inside the loop. All I can suggest with the current code is to debug it by stepping through it line by line and examining every variable at every step to see if and where reality differs from your expectation. If it never does then clearly there's something amiss with your expectation.
I wasn't looking for such vehement criticism as to how the code is written. I'm aware that the code is far from perfect; that I'm self-taught and that I'm 76 years of age. The fact that the code works in the first instance was of great satisfaction. I have debugged the code and all variables are as expected so, despite your scathing remarks, the code works!! All I need to know is why it writes results back into the datagridview in one instance and not in another.
Re: Datagridview behaviour problem
So, your code doesn't do what it is supposed to but it works? Even a broken clock is right twice a day. That code will produce the expected result in one particular case does not mean that it works. If it worked then it would work and you wouldn't be here. As I said before, if you can step through that code and everything conform to your expectations except the result then it is your expectations that are wrong and you should recalibrate them, which would mean going back to developing an algorithm. I say "going back" but I doubt that you actually did develop an algorithm in the first place. Very few beginners do but they should. I can't say why your code doesn't do what it's supposed to do because I don't know what it's supposed to do. I'm talking about the steps to get to a result, not just the result. I can't imagine why you would have a For loop where you explicitly increment and decrement the loop counter so I can't tell you how to fix that other than to get rid of it and do whatever it is you're trying to do properly. I'm not trying to be nasty. I've written terrible code myself. I'm trying to impress upon you how important it is that you develop a clear, working algorithm and then write good code to implement that, rather than trying to band-aid something that is a bad starting point.
