只能看到SqlDataReader的最后一行

Question

所以我有一个gridview显示员工计划工作的时间。网格限制为12周，并仅显示所选项目的小时数。网格是创建数据透视表的存储过程的产物，该数据透视表以工作周作为数据透视列。

我正在为网格中的每个单元格添加一个工具提示，该单元格显示每周计划的总小时数(显示任何项目的计划小时数，而不仅仅是所选项目)。如果员工只为一个项目安排了时间，则工具提示将按预期显示。但是，当有多个项目时，只有查询的最后一个项目(最后一行)会出现在工具提示中。

我可以看到正在发生的事情是，我正在为每一行重新创建strTooltip变量，这就是为什么我只显示最后一行。但我似乎无法设计出一种有效读取数据的聪明方法。

在网格的rowdatabound事件中调用:

        Using con As New SqlConnection(cnSQLLive)
            con.Open()
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spJobForecastingGetEmployeeProjectBreakdown"
            cmd.Connection = con
            cmd.Parameters.Add("@alias", SqlDbType.VarChar)
            cmd.Parameters.Add("@startdate", SqlDbType.DateTime)
            cmd.Parameters("@alias").Value = e.Row.Cells(4).Text
            cmd.Parameters("@startdate").Value = datStartDate
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            Do While reader.Read()
                For i As Integer = 5 To e.Row.Cells.Count - 1
                    Dim strTooltip As String = ""
                    Dim intTotalWeekHours As Integer = 0
                    Dim strWorkWeek As String = GridView1.HeaderRow.Cells(i).Text
                    Dim tempTextboxAs TextBox = DirectCast(e.Row.Cells(i).Controls(0), TextBox)
                    If Not IsDBNull(reader(strWorkWeek)) Then
                        strTooltip += reader(strWorkWeek) & " Hours -- " & reader("Project") & "<br />" & vbCrLf
                        intTotalWeekHours += reader(strWorkWeek)
                    End If
                    strTooltip += "-------------" & "<br />" & intTotalWeekHours & " Total Hours This Week"
                    tempTextbox.ToolTip = strTooltip
                Next
            Loop
        End Using

提前感谢!

Answer 1

我通过在循环外创建数组来解决这个问题，如下所示:

        Dim TooltipArray() As String = {"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""}
        Dim TotalHoursArray() As Integer = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
        Using con As New SqlConnection(cnSQLLive)
            con.Open()
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spJobForecastingGetEmployeeProjectBreakdown"
            cmd.Connection = con
            cmd.Parameters.Add("@alias", SqlDbType.VarChar)
            cmd.Parameters.Add("@startdate", SqlDbType.DateTime)
            cmd.Parameters("@alias").Value = e.Row.Cells(4).Text
            cmd.Parameters("@startdate").Value = HiddenFieldDate.Value
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            Do While reader.Read()
                For i As Integer = 5 To e.Row.Cells.Count - 1
                    Dim strTooltip As String = ""
                    Dim intTotalWeekHours As Integer = 0
                    Dim strWorkWeek As String = GridViewProjectEntry.HeaderRow.Cells(i).Text
                    Try
                        If Not IsDBNull(reader(strWorkWeek)) Then
                            If reader("Project") = " - " Then
                                TooltipArray(i) += reader(strWorkWeek) & " Hours -- Department Overhead" & "<br />" & vbCrLf
                            Else
                                TooltipArray(i) += reader(strWorkWeek) & " Hours -- " & reader("Project") & "<br />" & vbCrLf
                            End If
                            TotalHoursArray(i) += reader(strWorkWeek)
                        End If
                    Catch ex As Exception
                    End Try
                Next
            Loop
        End Using

希望这对其他人有所帮助!