VB.NET DataSet table data empty

Question

I'm trying to use the dataset for a report, but the data is gone when I try to use it. Here is my code for the most part:

Variables:

Dim ResultsDataView As DataView
Dim ResultsDataSet As New DataSet
Dim ResultsTable As New DataTable
Dim SQLQuery As String

Search: This is where a datagrid is populated in the main view. The data shows up perfectly.

Private Sub Search(Optional ByVal Bind As Boolean = True, Optional ByVal SearchType As String = "", Optional ByVal SearchButton As String = "")
        Dim SQLQuery As String
        Dim ResultsDataSet
        Dim LabelText As String
        Dim MultiBudgetCenter As Integer = 0

        SQLQuery = "A long and detailed SQL query that grabs N rows with 7 columns"
        ResultsDataSet = RunQuery(SQLQuery)
        ResultsTable = ResultsDataSet.Tables(0)

        For Each row As DataRow In ResultsTable.Rows
            For Each item In row.ItemArray
                sb.Append(item.ToString + ","c)
                Response.Write(item.ToString + "\n")
                Response.Write(vbNewLine)
            Next
            sb.Append(vbCr & vbLf)
        Next
        'Response.End()

        If Bind Then
            BindData(ResultsDataSet)
        End If

    End Sub

Binding Data: I think this is a cause in the issue.

Private Sub BindData(ByVal InputDataSet As DataSet)
        ResultsDataView = InputDataSet.Tables("Results").DefaultView

        ResultsDataView.Sort = ViewState("SortExpression").ToString()

        ResultsGridView.DataSource = ResultsDataView

        ResultsGridView.DataBind()
End Sub

Reporting action: This is where I am trying to use the table data. But it is showing as nothing.

Protected Sub ReportButton_Click(sender As Object, e As EventArgs) Handles ReportButton.Click
        For Each row As DataRow In ResultsTable.Rows
            For Each item In row.ItemArray
                Response.Write(item.ToString)
            Next
        Next
    End Sub

The reason I'm trying to loop through this data is to both display the data in a gridview on the main view as well as export the data to CSV. If there is a different way to export a SQL query to CSV, I'm open to any suggestions.

There has to be something I can do to get the data from the SQL query to persist through the ReportButton_Click method. I've tried copying the datatable, I've tried global variables, I've tried different methods of looping through the dataset. What am I missing?!

Thank you all in advance.

EDIT Here is the Page_Load:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack Then
            'set focus to postback control
            Dim x As String = GetPostBackControlName(Page)

            If Len(x) > 0 Then
                x = x.Replace("$", "_")
                SetFocus(x)
            End If
        End If

        If Not IsPostBack Then
            ResultsGridView.AllowPaging = False

            'Enable Gridview sorting
            ResultsGridView.AllowSorting = True

            'Initialize the sorting expression
            ViewState("SortExpression") = "ID DESC"

            'Populate the Gridview
            Search()
        End If
    End Sub

Could you show the code in the Page_Load? Did you reload your data at each call to Page_Load? — Steve, Aug 24 '18 at 14:00
So your Search function is called only the first time the page loads, thus when you get a Not IsPostBack your previous globals are totally forgotten. You need to save the result of your loads in a a Session variable a restore those value from this Session variable — Steve, Aug 24 '18 at 14:03
That `RunQuery()` method scares me. It's likely forcing you to write code that is crazy-vulnerable to sql injection attacks. — Joel Coehoorn, Aug 24 '18 at 14:15
Don't worry. It scared me too, but this is an intranet application. Any SQL injection attack would be done by an employee, and we aren't worried about that. — brettwbyron, Aug 24 '18 at 14:50

Steve · Accepted Answer · 2018-08-24T14:12:35.917

In your search function add this line after the ResultsTable setting

    ResultsTable = ResultsDataSet.Tables(0)
    Session("LastSearch") = ResultsTable

Then in your report click event handler recover your data from the Session variable

Protected Sub ReportButton_Click(sender As Object, e As EventArgs) Handles ReportButton.Click
    ResultsTable = DirectCast(Session("LastSearch"), DataTable)
    For Each row As DataRow In ResultsTable.Rows
        For Each item In row.ItemArray
            Response.Write(item.ToString)
        Next
    Next
End Sub

You need to read about ASP.NET Life Cycle and understand that every time ASP.NET calls your methods it creates a new instance of your Page class. Of course this means that global page variables in ASP.NET are not very useful.
Also consider to read about that Session object and not misuse it.

What is the difference between SessionState and ViewState?

Thank you! This was driving me nuts. I've never used Session variables before, so this was very helpful. Thanks again! — brettwbyron, Aug 24 '18 at 14:12

VB.NET DataSet table data empty

1 Answers1