• Where did my DataTable go? (VB.NET 2.0)

    Home » Forums » Developers, developers, developers » DevOps Lounge » Where did my DataTable go? (VB.NET 2.0)

    Author
    Topic
    #427827

    Hello All!
    I’m having trouble figuring out a work around for a little problem: First I dimmed the DataAdatpter and DataSet:

    Dim DA as ODBCDataAdapter = New ODBCDataAdapter
    Dim ds As DataSet = New DataSet

    I have the DataAdapter filling the DataTable in my Page_Load sub:

    Sub Page_Load(sender As Object, e As EventArgs)
    If Not(Page.IsPostBack) Then
    DA.SelectCommand = cmdPerv
    DA.Fill(ds, “INVOICES”)
    conPerv.Close
    cmdPerv.Dispose
    End If
    End Sub

    (I use the If Not(Page.IsPostBack) to prevent the table from reloading when other buttons on the form are clicked – ‘Clear Fields’, for example)

    Now, I use this DataTable as the DataSource for a Repeater:

    rptInv.DataSource = ds.Tables(“INVOICES”).SELECT(strSearch)
    rptInv.DataBind()

    The above 2 lines of code are in a sub that runs on a button click (not the Page_Load sub).

    Problem: while debugging, I find that there is no more table called “INVOICES”, and hence keep getting a System.NullReference error.

    I’ve tried referencing the table like ds.Tables(0), and get ‘No Table 0’ error. Also, if I remove the If..Then around the Page_Load sub, it runs fine, but is terribly slow, since it re-fills the DataTable every time a button is clicked.

    Any ideas? Thanks in advance.

    Viewing 0 reply threads
    Author
    Replies
    • #992304

      If conPerv is a connection object, try not closing or disposing it until you have finished using the dataset. Another thought is to create a DataTable object and set it equal to the ds.Tables(“INVOICES”) before you dispose the connection.

      • #992615

        Thanks Charlotte, hope you had a good New Year’s!!!

        OK…I added the DataTable & DataColumn:

        Dim INVOICES As DataTable = New DataTable(“INVOICES”)
        Dim dc As DataColumn

        I used code from msdn to create columns for the DataTable:

        dc = New DataColumn
        dc.DataType = Type.GetType(“System.String”)
        dc.ColumnName = “VENDOR”
        INVOICES.Columns.Add(dc)

        (one for each field pulled in the DA.Fill)

        I then reference the DataTable as the DataSource for the Repeater:

        rptInv.DataSource = INVOICES.SELECT(strSearch)

        This is now giving me a ‘Cannot find column [JOB]’ error (the column it can’t find is the first one specified in strSearch)

        I also removed conPerv.Close, to no different effect.

        Thanks for all the help, hopefully we’ll get her jammin’ soon!

        • #992739

          Sorry, but I don’t understand what you’re doing. Is there an actual table somewhere named INVOICES? Is this a web form being populated from that table? In any case, .Net is case sensitive, so try using a different name from the datatable that for the actual table … assuming there is an actual table. I don’t quite understand why you’re trying to create a datatable by hand when you can declare one and then set it to ds.Tables(0). In both your post, you use a strSearch variable in the SELECT method of the DataSet, but you don’t give any indication of what is *in* that variable, so there’s no way to know why it doesn’t work. It’s very hard to help when you don’t include all the necessary information, such as the specific exceptions you get and exactly what line throws that exception.

          • #992821

            Thanks Charlotte, sorry for the not-so-clear explanation.

            The actual table name is MASTER_APM_R2. Originally I didn’t try to create a DataTable by hand, I just referred to it by the name I gave it when I filled it with the DataAdapter. This latter method was only an attempt to fix the problem from before (DataTable is lost when the page posts back). I have attached a copy of the entire code to this post. The lines in bold and italics (only a few) were not in my original code, and the line that actually causes the error is underlined and in red.

            This did work in its entirety before I added the If Not(Page.IsPostBack) Then… around the lines in the Page_Load subroutine. I just can’t figure out why the DataTable seemingly ceases to exist.

            Thanks again!

            • #992866

              Sorry for jumping in here…

              On first glance, it appears that the data is not persisting between the initial page load and the post back. This is standard .NET behavior. You’re successfully populating the DataTable on initial page load, but the scope of the data is not persisted through to the post back.

              You’ll need to either wait to load the data until you need it (i.e. move the data-loading code into a separate function and only call it when you need it) or you can store the data into a session or page-level variable that lasts through postbacks.

              Edit: If you have a specific requirement for reading the data on the initial page load, you can also add the data table to the Viewstate. It’s been a while since I’ve worked with this and I can’t remember if the data table object is serializable. If not, you’ll need to use a dataset instead of data table.

              Hope this helps

            • #992893

              Mark!

              Thank you sir…It sounds like you know exactly what my problem is. However, I’m afraid I’m not savy enough with .NET yet, and am not sure how to make a variable page-level (at least not how I thought) or how to edit the ViewState. Any advice?

              Thanks again so much!

            • #992896

              Try this article.

              Also, you can google for asp.net viewstate for more article/tutorial links.

            • #992901

              Thanks for the leads! I learned something about ViewState today.

              Unfortunately, I tried it and since the table it pulls from has 150,000+ records, it runs incredibly slowly, and gives me a Maximum Length Exceeded error.

              Anybody that’s curious: ViewState(“INVOICES”) = ds.Tables(“INVOICES”) — easy, and I’m sure it works great for smaller items

              I took your first piece of advice and moved the DA.Fill… to the end of the Subroutine that does the searching. At least this way it doesn’t reload the table every time you want to ‘Clear Fields’, for example.

              Thanks so much Mark!

            • #992902

              Ahhh – 150,000 records is quite a bit of data to store in Viewstate. I didn’t realize it was going to be that big… You may also consider implementing a server-side paging solution to improve performance (so you end up reading only the number of records displayed on the page rather than the entire recordset).

              Glad you got it working!

    Viewing 0 reply threads
    Reply To: Where did my DataTable go? (VB.NET 2.0)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: