Search code examples
sqlvb.netdatasetoledbcommandoledbdataadapter

ASP.NET GridView Empty After Dynamic Fill From VB.NET


I have an ASP.NET GridView that is not being populated with new rows from a VB.NET Fill. The most recent incarnation of the code I am working with is as follows:

Protected Sub uGV(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnQuery.Click 
    Dim rFm As DateTime = DateTime.Parse(calFrom.SelectedDate.Date.ToString)
    Dim rTo As DateTime = DateTime.Parse(calTo.SelectedDate.Date.ToString)
    Dim oleUpConnString As String = ConfigurationManager.ConnectionStrings("OLEDBConnectionStringtoSQL").ConnectionString
    Dim oleUpConn As New OleDbConnection()
    oleUpConn.ConnectionString = oleUpConnString
    oleUpConn.Open()
    Dim vUp As New OleDbDataAdapter()
    Dim upCmd As String = "SELECT * FROM mail WHERE (date BETWEEN '07/02/2013' AND '07/04/2013')"
    Dim dsUp As New DataSet()
    Dim dt As New DataTable("Table1")
    Dim upCmdEx As New OleDbCommand(upCmd, oleUpConn)
    With upCmdEx.Parameters
        .AddWithValue("?", rFm)
        .AddWithValue("?", rTo)
    End With
    Try
        vUp.SelectCommand = upCmdEx
    Catch ex As Exception
        MsgBox("The select command failed")
    End Try
    Try
        Me.GridView2.DataSource = dsUp.Tables(0)
        vUp.Fill(dsUp)
    Catch ex As Exception
        MsgBox("Filling the dataset failed")
    End Try
    Me.GridView2.DataSource = dsUp
    'Me.GridView2.DataBind()
    oleUpConn.Close()
End Sub

I have spent several hours on this without a resolution, so if someone could identify what I have missed it would be greatly appreciated.

EDIT - GridView is coded as follows:

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="True" 
                    BackColor="White"  CaptionAlign="Top" 
                    CellPadding="4" ForeColor="#333333" 
                    GridLines="None" Caption="Surveys Completed">
                    <RowStyle BackColor="#EFF3FB" />
                    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                    <EditRowStyle BackColor="#2461BF" />
                    <AlternatingRowStyle BackColor="White" />
                </asp:GridView>

Solution

  • After extensive time spent on this, here is the final incarnation of the code behind for a Dynamic View feature for a GridView that will update in real-time with no page refresh required.

    Protected Sub uGV(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnQuery.Click 
        Dim rFm As String = String.Empty
        Dim rTo As String = String.Empty
        rFm = calFrom.SelectedDate.Date.ToString
        rTo = calTo.SelectedDate.Date.ToString
        Dim oleUpConn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("OLEDBConnectionStringtoSQL").ConnectionString)
        Dim vUp As OleDbDataAdapter = New OleDbDataAdapter
        Dim selectSQL As String = "SELECT * FROM mail WHERE (date BETWEEN ? AND ?)"
        Dim upCmdEx As OleDbCommand = New OleDbCommand(selectSQL, oleUpConn)
        vUp.SelectCommand = upCmdEx
        upCmdEx.Parameters.AddWithValue("@FROMDATE", rFm)
        upCmdEx.Parameters.AddWithValue("@TODATE", rTo)
        Dim dsUp As DataSet = New DataSet
        vUp.Fill(dsUp, "DynamicView")
        If dsUp.Tables("DynamicView").Rows.Count > 0 Then
            GridView2.DataSource = dsUp.Tables("DynamicView")
            GridView2.DataBind()
        Else
            MsgBox("Nothing to display for the selected date range.  Please select a new date range and try again.")
            Exit Sub
        End If
    End Sub