Gridview EmptyDataText When Data is in Database View?

[clausont]

This is a web application in VB.net connecting to an Access database on a 2003 server. I am using VWD 2010 Express.
The problem:

I have a asp.net web page using vb.net that connects to an access database. When I run a query from the web page, it seems to run properly. Ie: The entered information in inserted to the proper table and the "Results" view in the database is populated with the correct data. When it is viewed from the web page using a simple "Select * From Results", it returns an EmptyDataText message even though the data is clearly there in the Access database view. I can use the exact same command on any different view or table and return the relevant information in the view or table. I can connect to a different identical Access database and it all works just fine.
A really odd part of this is that when I add a gridview and configure the datasource, I go to "test query" and it returns the header of the database view but not the data that is clearly there in the columns under the column names
I have been working on this problem for several days now to no avail.
I have double checked, triple checked everything I could possibly do.

Is there possibly something in Access that will restrict the data from showing in a web page? I know that sounds dumb but I am really scratching my head here.
I really appreciate any help or thoughts on this.
If you need more information, please let me know.
The code for the Gridview that should display the data is below:

<asp:AccessDataSource ID="AccessDataSourceResult" runat="server" 
   SelectCommand="SELECT * FROM [Results]" 
        DataFile="E:\inetpub\[URL unfurl="true"]wwwroot\Ehealthlink.com\database\IndividualRatesWashington.mdb">[/URL]
    </asp:AccessDataSource>

<asp:GridView ID="gvResult" runat="server" AutoGenerateColumns="False"
        DataSourceID="AccessDataSourceResult" CellPadding="1" ForeColor="#333333" BackColor="White"
        BorderColor="Blue" BorderStyle="Solid" BorderWidth="1px" EmptyDataText="No Records Returned"
        Height="15px" HorizontalAlign="Center" ShowFooter="True" Width="95%" 
        Font-Names="Arial">
        
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" BorderColor="Blue" BorderStyle="Solid"
            BorderWidth="1px" Font-Names="Arial" Font-Size="XX-Small" HorizontalAlign="Center"
            VerticalAlign="Middle" Width="6%" />
        <Columns>
            
            
            <asp:BoundField DataField="Company Name" HeaderText="Company Name" 
                SortExpression="Company Name" />
            <asp:BoundField DataField="Logo" HeaderText="Logo" 
                SortExpression="Logo" />
            
            <asp:BoundField DataField="CompanyID" HeaderText="CompanyID" 
                SortExpression="CompanyID" />
            <asp:BoundField DataField="PlanID" HeaderText="PlanID" 
                SortExpression="PlanID" />
            <asp:BoundField DataField="Deductible" HeaderText="Deductible" 
                SortExpression="Deductible" />
            <asp:BoundField DataField="Deductible3" HeaderText="Deductible3" 
                ReadOnly="True" SortExpression="Deductible3" />
            <asp:BoundField DataField="Deductible2" HeaderText="Deductible2" 
                ReadOnly="True" SortExpression="Deductible2" />
            <asp:BoundField DataField="Month" HeaderText="Month" ReadOnly="True" 
                SortExpression="Month" />
            <asp:BoundField DataField="Premium" HeaderText="Premium" ReadOnly="True" 
                SortExpression="Premium" />
            <asp:BoundField DataField="NumberChildren2" HeaderText="NumberChildren2" 
                SortExpression="NumberChildren2" />
            <asp:BoundField DataField="MemberAge" HeaderText="MemberAge" ReadOnly="True" 
                SortExpression="MemberAge" />
            <asp:BoundField DataField="Spouse" HeaderText="Spouse" ReadOnly="True" 
                SortExpression="Spouse" />
            <asp:BoundField DataField="Children" HeaderText="Children" ReadOnly="True" 
                SortExpression="Children" />
            <asp:BoundField DataField="SpouseAge" HeaderText="SpouseAge" ReadOnly="True" 
                SortExpression="SpouseAge" />
            <asp:BoundField DataField="NumberChildren" HeaderText="NumberChildren" 
                SortExpression="NumberChildren" />
            <asp:BoundField DataField="County" HeaderText="County" 
                SortExpression="County" />
            <asp:BoundField DataField="ZipCode" HeaderText="ZipCode" 
                SortExpression="ZipCode" />
            <asp:BoundField DataField="Plan Name" HeaderText="Plan Name" 
                SortExpression="Plan Name" />
            <asp:BoundField DataField="Special Features" HeaderText="Special Features" 
                SortExpression="Special Features" />
            <asp:BoundField DataField="Enhanced Benefits" HeaderText="Enhanced Benefits" 
                SortExpression="Enhanced Benefits" />
            <asp:BoundField DataField="DrugCard" HeaderText="DrugCard" 
                SortExpression="DrugCard" />
            <asp:BoundField DataField="Plan Type" HeaderText="Plan Type" 
                SortExpression="Plan Type" />
            <asp:BoundField DataField="DeductibleID" HeaderText="DeductibleID" 
                SortExpression="DeductibleID" />
            <asp:BoundField DataField="Copayment" HeaderText="Copayment" 
                SortExpression="Copayment" />
            <asp:BoundField DataField="Your Share In-Network" 
                HeaderText="Your Share In-Network" SortExpression="Your Share In-Network" />
            <asp:BoundField DataField="Out-Of-Pocket In-Network" 
                HeaderText="Out-Of-Pocket In-Network" 
                SortExpression="Out-Of-Pocket In-Network" />
            <asp:BoundField DataField="Your Share Out-Of-Network" 
                HeaderText="Your Share Out-Of-Network" 
                SortExpression="Your Share Out-Of-Network" />
            <asp:BoundField DataField="Out-Of-Pocket Out-Of-Network" 
                HeaderText="Out-Of-Pocket Out-Of-Network" 
                SortExpression="Out-Of-Pocket Out-Of-Network" />
            <asp:BoundField DataField="PerFamily" HeaderText="PerFamily" 
                SortExpression="PerFamily" />
            <asp:BoundField DataField="Display" HeaderText="Display" 
                SortExpression="Display" />
            <asp:BoundField DataField="Copay" HeaderText="Copay" SortExpression="Copay" />
            <asp:BoundField DataField="MemberAge2" HeaderText="MemberAge2" ReadOnly="True" 
                SortExpression="MemberAge2" />
            <asp:BoundField DataField="SpouseAge2" HeaderText="SpouseAge2" ReadOnly="True" 
                SortExpression="SpouseAge2" />
            <asp:BoundField DataField="Client" HeaderText="Client" 
                SortExpression="Client" />
            
        </Columns>
        <EmptyDataRowStyle HorizontalAlign="Center" VerticalAlign="Middle" Width="6%" />
    </asp:GridView>

Code Behind:

Sub CrossPage_FirstPage()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\inetpub\[URL unfurl="true"]wwwroot\Ehealthlink.com\database\IndividualRatesWashington.mdb;"[/URL]

        Dim MyCon As New OleDbConnection(strConn)

        Dim MySqlDelete As String = "delete from problemdefinition"
        Dim MySqlInsert As String = "INSERT INTO problemdefinition (client, memberage, classtype, spouseage, classtypespouse, numberchildren, county, zipcode, deductible, startcoverage) VALUES (@client, @memberage, @classtype, @spouseage, @classtypespouse, @numberchildren, @county, @zipcode, @deductible, @startcoverage)"
        'Dim MySqlInsert As String = "INSERT INTO problemdefinition (client,memberage, ) VALUES (@client,@memberage)"
        Dim textboxName As TextBox = Me.PreviousPage.Master.FindControl("MainContent").FindControl("txtName")

        Dim cmd As New OleDbCommand(MySqlDelete, MyCon)
        With cmd.Parameters
            .Add(New OleDbParameter("@client", textboxName.Text))
            .Add(New OleDbParameter("@memberage", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("txtAge"), TextBox).Text))
            .Add(New OleDbParameter("@classtype", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("rbsmoke1"), RadioButtonList).Text))
            .Add(New OleDbParameter("@spouseage", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("txtSpouseAge"), TextBox).Text))
            .Add(New OleDbParameter("@classtypespouse", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("rbsmoke2"), RadioButtonList).Text))
            .Add(New OleDbParameter("@numberchildren", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("txtNumberChildren"), TextBox).Text))
            .Add(New OleDbParameter("@county", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("lblhidden1"), Label).Text))
            .Add(New OleDbParameter("@zipcode", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("drpZip1"), TextBox).Text))
            .Add(New OleDbParameter("@deductible", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("DrpDed1"), DropDownList).SelectedItem.Value))
            .Add(New OleDbParameter("@startcoverage", DirectCast(Me.PreviousPage.Master.FindControl("MainContent").FindControl("DrpStartCov1"), DropDownList).SelectedItem.Value))
        End With
        Dim result As Integer = -1


        If MyCon.State = ConnectionState.Closed Then
            Try
                MyCon.Open()
                cmd.CommandText = MySqlDelete
                result = cmd.ExecuteNonQuery()
            Catch ex As Exception
                Response.Write(ex.Message)
            Finally
                MyCon.Close()
            End Try
        End If

        If result <> -1 Then
            If MyCon.State = ConnectionState.Closed Then
                Try
                    MyCon.Open()
                    cmd.CommandText = MySqlInsert
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    Response.Write(ex.Message)
                Finally
                    MyCon.Close()
                End Try
            End If

            '--------------------------------------------------

            '--------------------------------------------------
        End If
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            AccessDataSourceResult.DataFile = "E:\inetpub\[URL unfurl="true"]wwwroot\Ehealthlink.com\database\IndividualRatesWashington.mdb"[/URL]
            If Me.PreviousPage Is Nothing = False Then
                CrossPage_FirstPage()
            End If
        End If

    End Sub

 

[jbenson001]

First, get rid of the DataSource control. They will only cause you headaches as you have seen.
Second, write your Select command as you have done with the others and use ExecuteDataTable or ExecuteDataSet, whichever is relevant to your needs.

This way you can easily debug your issue.

 

[clausont]

Thanks JBenson. As you can tell, I'm no expert in this area

I certainly have found that the datasource controls are a headache.
Debugging is something that has eluded me in ASP.NET. But I guess I should start a different thread to learn how to easily debug.

I don't know why but now it is working just fine. No idea why.
Is there a way to mark this as solved?

 

[jbenson001]

There is no way to mark as solved in the forum.
Debugging in ASP.NET is awesome and easy compared to classic ASP.

The easiest way to click in the left margin in your code behind file to set a break point. you will see a red circle there. Then you can just click the play (green ">" button) in the tool bar. Run your app, and the code will stop on any breakpoints you set once you hit that code.

 

[clausont]

Lol! That sounds too easy. I have done that inadvertently but didn't know what it was for then forgot to check. I will try that next time.
By the way - Found the reason for the original error. It was the way the db query was written in the access db in the first place. The same query basically worked in classic asp so the db operator felt no need to do anything differently for asp.net. Turns out that a required value was missing in the internal query even though it all appeared to be working correctly on the internal db. Odd problem but solved now.

 

[jbenson001]

Glad you found the issue.