ASP.NET

Private Sub showGrid()

    Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
    ' Dim strQuery As String

    Dim cmd As New SqlCommand("-placeholder-", conn)
    Dim sb As New StringBuilder("SELECT L.LocationId," & _
           "c.courseId, " & _
            "c.coursename, " & _
           "CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
               " ELSE 'Available' END AS 'AvailableSeats'," & _
            "d.dateid, " & _
           " d.trainingDates, " & _
            "d.trainingtime, " & _
           " c.CourseDescription, " & _
           " i.instructorName, " & _
            "l.location " & _
          " FROM tblLocations L " & _
          " Inner Join tblCourses c on l.locationId = c.locationId " & _
          " Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
         "  Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
         "  Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
         "  OUTER APPLY (Select Count(*) TakenSeats " & _
         "  FROM tblTrainings T " & _
         "  where(L.LocationId = T.LocationId) " & _
         "  AND c.courseId = t.courseId) TS")

    ' Hard-code the acceptable values here:
    Dim andors As String = If(ANDOR.SelectedValue = "OR", " OR ", " AND ")
    Dim startedWhere As Boolean = False

    If DropDownList1.SelectedValue <> "" Then
        sb.Append(If(startedWhere, andors, " WHERE "))
        sb.Append("i.instructorName = @InstructorName")
        cmd.Parameters.AddWithValue("@InstructorName", DropDownList1.SelectedValue)
        startedWhere = True
    End If

    If ViewSelect = "Range" Then
        sb.Append(If(startedWhere, andors, " WHERE "))
        sb.Append("d.trainingDates Between @FromDate And @EndDate")
        cmd.Parameters.AddWithValue("@FromDate", Date.Parse(txtFromDate.Text))
        cmd.Parameters.AddWithValue("@EndDate", Date.Parse(txtToDate.Text))
        startedWhere = True

    ElseIf ViewSelect = "Specific" Then
        sb.Append(If(startedWhere, andors, " WHERE "))
        sb.Append("d.trainingDates = @SpecificDate")
        cmd.Parameters.AddWithValue("@SpecificDate", Date.Parse(txtSpecificDate.Text))
        startedWhere = True
    End If

    cmd.CommandText = sb.ToString()

    ' Dim cmd As New SqlCommand(strQuery) 'This says cmd has already been declared
    Dim dt As DataTable = GetData(cmd)
    If dt.Rows.Count = 0 Then

        cmd.Parameters.Clear()
        cmd.Parameters.AddWithValue("@FromDate", Date.Today.AddMonths(-1))
        cmd.Parameters.AddWithValue("@EndDate", Date.Today)

        cmd.CommandText = "SELECT L.LocationId," & _
           "c.courseId, " & _
            "c.coursename, " & _
           "CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
               " ELSE 'Available' END AS 'AvailableSeats'," & _
            "d.dateid, " & _
           " d.trainingDates, " & _
            "d.trainingtime, " & _
           " c.CourseDescription, " & _
           " i.instructorName, " & _
            "l.location " & _
          " FROM tblLocations L " & _
          " Inner Join tblCourses c on l.locationId = c.locationId " & _
          " Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
         "  Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
         "  Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
         "  OUTER APPLY (Select Count(*) TakenSeats " & _
         "  FROM tblTrainings T " & _
         "  where(L.LocationId = T.LocationId) " & _
         "  AND c.courseId = t.courseId) TS " & _
         " where d.trainingDates Between @FromDate And @EndDate"

        dt = GetData(cmd)
    End If

    GridView1.DataSource = dt
    GridView1.DataBind()

End Sub

samflex wrote:

Second, when the page loads, I start paging, it starts showing some weird lines.

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
        GridView1.PageIndex = e.NewPageIndex
        showGrid()
        GridView1.DataBind()
    End Sub
    Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As EventArgs)
        Dim gvrow As GridViewRow = GridView1.BottomPagerRow
        Dim lblcurrentpage As Label = DirectCast(gvrow.Cells(0).FindControl("CurrentPage"), Label)
        lblcurrentpage.Text = Convert.ToString(GridView1.PageIndex + 1)
        Dim page As Integer() = New Integer(6) {}
        page(0) = GridView1.PageIndex - 2
        page(1) = GridView1.PageIndex - 1
        page(2) = GridView1.PageIndex
        page(3) = GridView1.PageIndex + 1
        page(4) = GridView1.PageIndex + 2
        page(5) = GridView1.PageIndex + 3
        page(6) = GridView1.PageIndex + 4
        For i As Integer = 0 To 6
            If i <> 3 Then
                If page(i) < 1 OrElse page(i) > GridView1.PageCount Then
                    Dim lnkbtn As LinkButton = DirectCast(gvrow.Cells(0).FindControl("p" + Convert.ToString(i)), LinkButton)
                    lnkbtn.Visible = False
                Else
                    Dim lnkbtn As LinkButton = DirectCast(gvrow.Cells(0).FindControl("p" + Convert.ToString(i)), LinkButton)
                    lnkbtn.Text = Convert.ToString(page(i))
                    lnkbtn.CommandName = "PageNo"

                    lnkbtn.CommandArgument = lnkbtn.Text
                End If
            End If
        Next
        If GridView1.PageIndex = 0 Then
            Dim lnkbtn As LinkButton = DirectCast(gvrow.Cells(0).FindControl("LinkButton1"), LinkButton)
            lnkbtn.Visible = False
            lnkbtn = DirectCast(gvrow.Cells(0).FindControl("LinkButton2"), LinkButton)

            lnkbtn.Visible = False
        End If
        If GridView1.PageIndex = GridView1.PageCount - 1 Then
            Dim lnkbtn As LinkButton = DirectCast(gvrow.Cells(0).FindControl("LinkButton3"), LinkButton)
            lnkbtn.Visible = False
            lnkbtn = DirectCast(gvrow.Cells(0).FindControl("LinkButton4"), LinkButton)

            lnkbtn.Visible = False
        End If
        If GridView1.PageIndex > GridView1.PageCount - 5 Then
            Dim lbmore As Label = DirectCast(gvrow.Cells(0).FindControl("nmore"), Label)
            lbmore.Visible = False
        End If
        If GridView1.PageIndex < 4 Then
            Dim lbmore As Label = DirectCast(gvrow.Cells(0).FindControl("pmore"), Label)
            lbmore.Visible = False
        End If
    End Sub
    Private Sub lb_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
        GridView1.PageIndex = Convert.ToInt32(e.CommandArgument) - 1
    End Sub
    Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

        If e.Row.RowType = DataControlRowType.Pager Then
            Dim gvr As GridViewRow = e.Row
            Dim lb As LinkButton = DirectCast(gvr.Cells(0).FindControl("p0"), LinkButton)
            AddHandler lb.Command, AddressOf lb_Command

            lb = DirectCast(gvr.Cells(0).FindControl("p1"), LinkButton)
            AddHandler lb.Command, AddressOf lb_Command
            lb = DirectCast(gvr.Cells(0).FindControl("p2"), LinkButton)
            AddHandler lb.Command, AddressOf lb_Command
            lb = DirectCast(gvr.Cells(0).FindControl("p4"), LinkButton)
            AddHandler lb.Command, AddressOf lb_Command
            lb = DirectCast(gvr.Cells(0).FindControl("p5"), LinkButton)
            AddHandler lb.Command, AddressOf lb_Command
            lb = DirectCast(gvr.Cells(0).FindControl("p6"), LinkButton)
            AddHandler lb.Command, AddressOf lb_Command
        End If
    End Sub
End Class

SearchString = txtSpecificDate.Text
ViewSelect = Request("ViewBy")
If ViewSelect = "" Or ViewSelect = "Range" Then
    ViewSelect = "Range"
Else
    ViewSelect = "Specific"
End If

If ViewSelect = "Range" Then
    sb.Append(If(startedWhere, andors, " WHERE "))
    sb.Append("(d.trainingDates >= @FromDate And d.trainingDates < DateAdd(day, 1, @EndDate))")
    cmd.Parameters.AddWithValue("@FromDate", Date.Parse(txtFromDate.Text))
    cmd.Parameters.AddWithValue("@EndDate", Date.Parse(txtToDate.Text))
    startedWhere = True

ElseIf ViewSelect = "Specific" Then
    sb.Append(If(startedWhere, andors, " WHERE "))
    sb.Append("(d.trainingDates >= @SpecificDate And d.trainingDates < DateAdd(day, 1, @SpecificDate))")
    cmd.Parameters.AddWithValue("@SpecificDate", Date.Parse(txtSpecificDate.Text))
    startedWhere = True
End If

sb.Append("(d.trainingDates >= @FromDate And d.trainingDates < DateAdd(day, 1, @EndDate))")
    cmd.Parameters.AddWithValue("@EndDate", Date.Parse(txtToDate.Text))

sb.Append("(CONVERT(VARCHAR(10), d.trainingDates, 101) >= @FromDate And CONVERT(VARCHAR(10), d.trainingDates, 101) < @EndDate)")
           cmd.Parameters.AddWithValue("@FromDate", txtFromDate.Text)