SQL data reader only reads the first row in VB.net

Question

I have a database containing a table named restaurant. This table has columns named "time" and "tableno", and has 20 rows. I am using this code to read the data:

    Dim connString As String = "server=DESKTOP-69QA9LH\SQLEXPRESS; database=servicedb; integrated security=true"
    Dim conn As New SqlConnection(connString)
    Dim command As SqlCommand
    Dim reader As SqlDataReader

    conn.Open()
    Dim query As String
    query = "select time,tableno from restaurant "
    command = New SqlCommand(query, conn)
    reader = command.ExecuteReader
    reader.Read()
    Dim d1 As DateTime = ToolStripStatusLabel1.Text
    Dim d2 As DateTime = reader("time")
    Dim diff As Short = DateDiff(DateInterval.Minute, d2, d1)

    If reader("tableno") = "2" AndAlso diff = "5" Then
        Button3.BackColor = Color.LawnGreen
    End If

    If reader("tableno") = "2" AndAlso diff = "10" Then
        Button3.BackColor = Color.LawnGreen
    End If       

    If reader("tableno") = "2" AndAlso diff = "15" Then
        Button3.BackColor = Color.LawnGreen
    End If

    If reader("tableno") = "1" AndAlso diff = "5" Then
        Button1.BackColor = Color.Brown
    End If

    If reader("tableno") = "1" AndAlso diff = "10" Then
        Button1.BackColor = Color.Brown
    End If
    If reader("tableno") = "1" AndAlso diff = "15" Then
        Button1.BackColor = Color.Brown
    End If

It almost works, but problem is that it only reads the 1st row in the table. I mean, when I click the button to process this code, buttons change background color based only on the first row from the table.

The row with 'tableno' 2 is the 1st row, and it changes that background color. But 'tableno' 1 is the 2nd row and I can't read this row to change background color.

How can I make it work with the other rows?

You need to spend some time learning how ADO.NET works. Do you know what that `Read` method you're calling actually does? It doesn't really seem that you do. Spend the time to read the documentation for that method and check out some examples. [Here](http://www.vbforums.com/showthread.php?469872-Retrieving-and-Saving-Data-in-Databases) are some of my own. — jmcilhinney, Jun 16 '18 at 02:39
Can you help me to correct my code.,cos im not sure how to implement yours code to my code. — Andrew Punio, Jun 16 '18 at 03:01
Do you understand the principle? That's the problem. In your code you are calling `Read` once so you are only reading one record. If you expect to read every record then you have to keep calling `Read` until there are no more records to read. How do you usually do the same thing multiple times? By using a loop. The link I provided demonstrates how to do that. I'm not providing any more because this site is not intended as a tutorial on the basics or as a code-writing service. — jmcilhinney, Jun 16 '18 at 03:13
Please can you check this url to enhance quality of question https://stackoverflow.com/help/how-to-ask — Prags, Jun 16 '18 at 03:41

Richardissimo · Answer 1 · 2018-06-16T08:33:32.937

There are several points here...

SqlConnection, SqlCommand and SqlDataReader are IDisposable, so should be in using blocks.
You are only processing the first row because you aren't using a loop to iterate through all the rows.
There is only one "Button1" and one "Button3", so although the code below will iterate through all the rows, it updates the same controls each time, so you won't see any difference. Without knowing what your screen is like, I can't offer a solution for that.
You're being a bit ambiguous with types. For example, "diff" is a short; but you are comparing it against strings. On that basis, I'm going to assume the TableNo column is an int. Try to ensure you compare strings with strings and ints with ints, and so on.
Consider giving your variables proper names, not just d1, d2.
DataReader has strongly typed methods for fetching values from columns.
Don't keep fetching the same value from the reader over and over again, store it in a local variable.
DateDiff returns a long, not a short
I suspect you want a greater than or equalto, rather than an equals, since you appear to want to show how far off the booking is.
You need to convert the ToolStripStatusLabel1.Text from a string to a DateTime

For example...

    Dim d1 As DateTime = DateTime.Parse(ToolStripStatusLabel1.Text)

    Dim connString As String = "server=DESKTOP-69QA9LH\SQLEXPRESS; database=servicedb; integrated security=true"
    Using conn As New SqlConnection(connString)
        conn.Open()

        Dim query As String
        query = "select time,tableno from restaurant "
        Using command As New SqlCommand(query, conn)
            Using reader = command.ExecuteReader
                While reader.Read()

                    REM 0 because its the first column in the query. If you prefer to look it up, you can do reader.GetOrdinal("time")
                    Dim time As DateTime = reader.GetDateTime(0)
                    REM Its hard to tell from the posted code, so Im going to assume tableno is an int32
                    Dim tableNo As Int32 = reader.GetInt32(1)

                    Dim diff As Long = DateDiff(DateInterval.Minute, time, d1)

                    If tableNo = 2 AndAlso diff >= 5 Then
                        Button3.BackColor = Color.LawnGreen
                    End If
                    If tableNo = 2 AndAlso diff >= 10 Then
                        Button3.BackColor = Color.LawnGreen
                    End If
                    If tableNo = 2 AndAlso diff >= 15 Then
                        Button3.BackColor = Color.LawnGreen
                    End If
                    If tableNo = 1 AndAlso diff >= 5 Then
                        Button1.BackColor = Color.Brown
                    End If
                    If tableNo = 1 AndAlso diff >= 10 Then
                        Button1.BackColor = Color.Brown
                    End If
                    If tableNo = 1 AndAlso diff >= 15 Then
                        Button1.BackColor = Color.Brown
                    End If
                End While
            End Using
        End Using
    End Using

Having seen what I had reached at that point, a few more things occurred to me:

It seems like there is a relationship between the tableno and the button.
It was always putting the same color in the same button. You probably want the color to vary.

So here is a replacement of the loop part

                While reader.Read()
                    REM 0 because its the first column in the query. If you prefer to look it up, you can do reader.GetOrdinal("time")
                    Dim time As DateTime = reader.GetDateTime(0)
                    REM Its hard to tell from the posted code, so Im going to assume tableno is an int32
                    Dim tableNo As Int32 = reader.GetInt32(1)

                    Dim diff As Long = DateDiff(DateInterval.Minute, time, d1)

                    REM Consider turning this group of lines into a method which converts the diff to a color
                    Dim diffColor As Color = Color.Gray REM Default color
                    If diff >= 5 Then
                        diffColor = Color.LawnGreen
                    End If
                    If diff >= 10 Then
                        diffColor = Color.Brown
                    End If
                    If diff >= 15 Then
                        diffColor = Color.Red
                    End If

                    If tableNo = 1 Then
                        Button1.BackColor = diffColor
                    End If
                    If tableNo = 2 Then
                        Button3.BackColor = diffColor
                    End If
                End While

thank you for code but just one more thing..where can i put d1 ? cos i receive error about d1 missing. and when i execute code its show "specifed cast is not valid" error — Andrew Punio, Jun 16 '18 at 04:20
It's just outside the loop, since it doesn't need to be re-calculated each time (see the first chunk of code, it's above the connection string). I hadn't spotted the lack of a Parse, so I've just edited it. — Richardissimo, Jun 16 '18 at 04:21
i try to achieve following scenario. when user click button 5 its take time diffeence from database and depend on time difference make different color of button2 — Andrew Punio, Jun 16 '18 at 04:33
With due respect, I've answered the posted question of why it was only processing the first row; and a whole heap of other issues with the posted code. I would have hoped you could work that out by looking at the above answer; but feel free to post that as a *different* question. And read the "[How To Ask](https://stackoverflow.com/help/how-to-ask)" link, as you need to make it clear what you're asking. — Richardissimo, Jun 16 '18 at 04:41

score 0 · Answer 2 · edited Jun 17 '18 at 02:20

With help from Richarddissimo's answer, I ended up with following code. I just changed 2 things there in getDatetime to GetValue and it works well.

Dim d1 As DateTime = DateTime.Parse(ToolStripStatusLabel1.Text)

Dim connString As String = "server=DESKTOP-69QA9LH\SQLEXPRESS; database=servicedb; integrated security=true"
Using conn As New SqlConnection(connString)
    conn.Open()

    Dim query As String
    query = "select time,tableno from restaurant "
    Using command As New SqlCommand(query, conn)
        Using reader = command.ExecuteReader
            While reader.Read()

                REM 0 because its the first column in the query. If you prefer to look it up, you can do reader.GetOrdinal("time")
                Dim time As DateTime = reader.getvalue(0)
                REM Its hard to tell from the posted code, so Im going to assume tableno is an int32
                Dim tableNo As Int32 = reader.getvalue(1)

                Dim diff As Long = DateDiff(DateInterval.Minute, time, d1)

                If tableNo = 2 AndAlso diff >= 5 Then
                    Button3.BackColor = Color.LawnGreen
                End If
                If tableNo = 2 AndAlso diff >= 10 Then
                    Button3.BackColor = Color.LawnGreen
                End If
                If tableNo = 2 AndAlso diff >= 15 Then
                    Button3.BackColor = Color.LawnGreen
                End If
                If tableNo = 1 AndAlso diff >= 5 Then
                    Button1.BackColor = Color.Brown
                End If
                If tableNo = 1 AndAlso diff >= 10 Then
                    Button1.BackColor = Color.Brown
                End If
                If tableNo = 1 AndAlso diff >= 15 Then
                    Button1.BackColor = Color.Brown
                End If
            End While
        End Using
    End Using
End Using

It's a shame you didn't mention that my clearly stated assumption that the type of the `tableno` column was `Int32` was incorrect. I could have edited my answer and then you could have accepted it. So what type was it? If it was some kind of string you can use `GetString`, change the type of the variable, and go back to your original comparisons of `tableno` against strings. And if you had to use `GetValue` for time then what type is that column in the database? — Richardissimo, Jun 17 '18 at 04:42

SQL data reader only reads the first row in VB.net

2 Answers2