时间戳

首页 > 解决方案 > SQL 数据读取器只读取 VB.net 中的第一行

sql-server - SQL 数据读取器只读取 VB.net 中的第一行

问题描述

我有一个数据库，其中包含一个名为restaurant. 该表有名为“time”和“tableno”的列，有 20 行。我正在使用此代码来读取数据：

    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

它几乎可以工作，但问题是它只读取表中的第一行。我的意思是，当我单击按钮处理此代码时，按钮仅根据表中的第一行更改背景颜色。

'tableno' 2 的行是第一行，它改变了背景颜色。但是 'tableno' 1 是第二行，我无法读取这一行来更改背景颜色。

我怎样才能使它与其他行一起工作？

标签： sql-serverdatabasevb.netsqldatareader

解决方案

这里有几点...

  1. SqlConnection,SqlCommand并且SqlDataReader是IDisposable, 所以应该在using块中。
  2. 您只处理第一行，因为您没有使用循环遍历所有行。
  3. 只有一个“Button1”和一个“Button3”，因此虽然下面的代码将遍历所有行，但它每次都会更新相同的控件，因此您不会看到任何差异。在不知道您的屏幕是什么样的情况下，我无法为此提供解决方案。
  4. 你对类型有点模棱两可。例如，“差异”是一个short；但是您正在将其与字符串进行比较。在此基础上，我将假设 TableNo 列是int. 尝试确保将字符串与字符串进行比较，将整数与整数进行比较，等等。
  5. 考虑为您的变量提供正确的名称，而不仅仅是d1, d2。
  6. DataReader 具有用于从列中获取值的强类型方法。
  7. 不要一遍又一遍地从读取器中获取相同的值，将其存储在局部变量中。
  8. DateDiff 返回长而不是短
  9. 我怀疑您想要大于或等于，而不是等于，因为您似乎想要显示预订的距离。
  10. 您需要将 ToolStripStatusLabel1.Text 从字符串转换为 DateTime

例如...

    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

看到我当时所达到的目标后，我又想到了一些事情：

  1. 似乎 tableno 和按钮之间存在关系。
  2. 它总是将相同的颜色放在同一个按钮中。您可能希望颜色有所不同。

所以这里是循环部分的替换

                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

推荐阅读