Click here to Skip to main content
15,882,113 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good morning. Currently I'm rebuilding one application and I don't understand one thing. Program read data from SQL and write this data to datagridview. Everything is OK, only last penultimate 8 records is not written to datagridview. What can be reason of that?

What I have tried:

VB
<pre>Private Sub FillFirstMeter(Index As Integer, FirstID As Integer, LastID As Integer)
        Me.Refresh()
        Dim rowNR As Integer = 0
        Dim Connection As New 
         SqlClient.SqlConnection(STR_SQLConnectionString)
        Dim DATE_TIME_VALUE As Date
        Dim METER_VALUE As Integer
        DG_Search.Rows.Add(LastID - FirstID)
        DG_Search.Refresh()

        For ID = FirstID To LastID
            Connection.Close()

            'GET DATE TIME FROM FIRST METER
            Query = String.Empty
            Query = "SELECT DATE_TIME FROM Meter_ID_" & Index & " WHERE ID=" 
             & ID
            Dim Command As New SqlClient.SqlCommand(Query, Connection)
            Connection.Open()
            DATE_TIME_VALUE = Command.ExecuteScalar
            Command.Dispose()
            Connection.Close()
            DG_Search.Rows(rowNR).Cells(Index - 1).Value = DATE_TIME_VALUE


            'GET VALUE FROM FIRST METER
            Query = String.Empty
            Query = "SELECT VALUE FROM Meter_ID_" & Index & " WHERE ID="
             & ID
            Command = New SqlClient.SqlCommand(Query, Connection)
            Connection.Open()
            METER_VALUE = Command.ExecuteScalar
            Command.Dispose()
            Connection.Close()
            DG_Search.Rows(rowNR).Cells(Index + 1).Value = METER_VALUE
            DG_Search.Rows(rowNR).Cells(Index).Value = ID
            If rowNR < RECORDS_COUNT Then
                rowNR += 1
            End If
            Debug.WriteLine(rowNR & Space(1) & ID & Space(1) & 
              DATE_TIME_VALUE & Space(1) & METER_VALUE)
        Next
        DG_Search.Refresh()
    End Sub
<pre>For testing purposes  into code of writing values to datagridview I put also debug.writeline and here is everything OK, also last penultimate 8 values is here.


CONTENT OF DATAGRIDVIEW
22. 2. 2023 17:00:00	93636	6564382
22. 2. 2023 18:00:00	93637	6564676
22. 2. 2023 19:00:00	93638	6564948
22. 2. 2023 20:00:00	93639	6565226
22. 2. 2023 21:00:00	93640	6565510
22. 2. 2023 22:00:00	93641	6565778
23. 2. 2023 7:00:00	    93650	6567930

CONTENT OF DEBUG WINDOW
8621 93636 22. 2. 2023 17:00:00 6564382
8622 93637 22. 2. 2023 18:00:00 6564676
8623 93638 22. 2. 2023 19:00:00 6564948
8624 93639 22. 2. 2023 20:00:00 6565226
8625 93640 22. 2. 2023 21:00:00 6565510
8626 93641 22. 2. 2023 22:00:00 6565778
8626 93642 22. 2. 2023 23:00:00 6566023
8626 93643 23. 2. 2023 00:00:00 6566246
8626 93644 23. 2. 2023 1:00:00 6566460
8626 93645 23. 2. 2023 2:00:00 6566664
8626 93646 23. 2. 2023 3:00:00 6566874
8626 93647 23. 2. 2023 4:00:00 6567076
8626 93648 23. 2. 2023 5:00:00 6567314
8626 93649 23. 2. 2023 6:00:00 6567594
8626 93650 23. 2. 2023 7:00:00 6567930
Posted
Updated 22-Feb-23 20:14pm
v2
Comments
Graeme_Grant 23-Feb-23 1:30am    
Can't do anything without seeing your code.

1 solution

At a guess, it's the RECORDS_COUNT that's too small - but we can't see where that is set. If it's too small, the final record will be overwritten multiple times and you will get a difference between the DGV rows and the printed output.
Use the debugger (rather than Debug.WriteLine) and follow exactly what the code does while it is running and you'll see what I mean, and be able to see what RECORDS_COUNT actually is compared to the number of rows processed.

But ... don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
 
Share this answer
 
Comments
robo.pavlik 23-Feb-23 2:43am    
Thanks for answer. The problem was in RECORDS_COUNT.
OriginalGriff 23-Feb-23 3:06am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900