Results 1 to 14 of 14

Thread: [RESOLVED] How To Make DataTable Static

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    87

    Resolved [RESOLVED] How To Make DataTable Static

    Hi All,
    Please, have a look at the code snippet below:

    Code:
    Private Function GetRecords() As DataTable
            Static Dim staticTable As DataTable
            Dim temporaryTable As New DataTable
    
            If Me.Tag.Equals("Static table has not yet been loaded with daily records") Then
                staticTable = New DataTable
                'temporaryTable gets its contents from a database. The code for that is not included here.
                staticTable = temporaryTable.Copy()
                Me.Tag = "Static table has been loaded with daily records"
            End If
            Return staticTable
        End Function
    The temporaryTable gets its contents from a database. It takes several minutes to populate this table, so I decided to fetch the records from the database and keep them in a static table. Subsequent fetch should simply use the static table instead of going to the database. The problem is that once code execution goes from this form to another form and back to this form, the static table would no longer have rows (i.e. staticTable.Rows.Count = 0). The reason for the IF ... End If block is to prevent having to connect to the database when the staticTable has already been populated.

    Good people of VBForums, please, help!

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: How To Make DataTable Static

    You can store and retrieve the schema as well as the contents of the DataTable into a My.Settings value by using the following code:
    Code:
    Private Function ConvertDataToString(ByVal dt As DataTable) As String
        Dim conversion As String = String.Empty
    
        Using writer As IO.TextWriter = New IO.StringWriter()
            dt.WriteXml(writer, XmlWriteMode.WriteSchema)
            conversion = writer.ToString()
        End Using
    
        Return conversion
    End Function
    
    Private Function ConvertStringToData(ByVal value As String) As DataTable
        Dim dt As DataTable
    
        Using reader As IO.TextReader = New IO.StringReader(value)
            dt = New DataTable()
            dt.ReadXml(reader)
        End Using
    
        Return dt
    End Function
    Assuming that you create a My.Setting named table and set its type to String, then you could use the code like this to save/load it:
    Code:
    'Save the setting
    My.Settings.Table = ConvertDataToString(MyDataTable) : My.Settings.Save()
    
    'Get the table back from the setting
    MyDataTable = ConvertStringToData(My.Settings.Table)
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How To Make DataTable Static

    Why does it need to be static? Why not create a private DataTable, and initialize it to Nothing.
    Code:
    Private MyData as DataTable = Nothing
    Then in your function, see if it is nothing, and if so, create it and fill it.

    Code:
    Private Function GetRecords() As DataTable
            If MyData is Nothing Then
                MyData = New DataTable
                'Load data into MyData (you didn't show that)
            End If
            Return MyData
        End Function
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    87

    Re: How To Make DataTable Static

    Thank you very much Sir, could you kindly expatiate on this "create a My.Setting named table and set its type to String".

  5. #5
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: How To Make DataTable Static

    The problem is that once code execution goes from this form to another form and back to this form, the static table would no longer have rows
    If that means that the original form is closed and reopened, then it is understandable that it does not work as you expect. the reason is that the form is deleted and nothing within it will survive. no static vars, no form global vars, nothing contained in the form.
    you could put your table in a module though

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    87

    Re: How To Make DataTable Static

    Thanks, the original form is not closed. It remains open even as code execution goes to the second form. When the second form closes, control returns to the original form and the requirement is to load the records again and perform some more processing.

  7. #7
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: How To Make DataTable Static

    Quote Originally Posted by 1369Times View Post
    Thanks, the original form is not closed. It remains open even as code execution goes to the second form. When the second form closes, control returns to the original form and the requirement is to load the records again and perform some more processing.
    then my guess would be that because your GetRecords function does return a reference to staticTable some further processing of this return value executes a .clear on the table. that would explain why
    staticTable.Rows.Count = 0
    and not staticTable=nothing.
    could that be?

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: How To Make DataTable Static

    Quote Originally Posted by 1369Times View Post
    Thank you very much Sir, could you kindly expatiate on this "create a My.Setting named table and set its type to String".
    To create a setting, open the project's properties by clicking on Menu > [project name] Properties. Once you have the properties window pulled up, click on the Settings tab. Now that the settings tab is pulled up, you can create a new setting and name it whatever you want.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    87

    Re: How To Make DataTable Static

    Thanks dday9.
    This line of code dt.WriteXml(writer, XmlWriteMode.WriteSchema)
    Throws the following exception:
    Cannot serialize the DataTable. DataTable name is not set.

    My confession is that I am not conversant with this aspect of VB.

  10. #10
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: How To Make DataTable Static

    If your DataTable doesn't have a name, then give it one. This is generally done whenever you create the DataTable:
    Code:
    Dim dt As New DataTable("Name Goes Here")
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    87

    Re: How To Make DataTable Static

    Apology, here's the function that's throwing the exception. The dt is already defined as a parameter.

    Code:
    Private Function ConvertDataToString(ByVal dt As DataTable) As String
        Dim conversion As String = String.Empty
    
        Using writer As IO.TextWriter = New IO.StringWriter()
            dt.WriteXml(writer, XmlWriteMode.WriteSchema)
            conversion = writer.ToString()
        End Using
    
        Return conversion
    End Function
    Last edited by 1369Times; May 24th, 2017 at 06:36 PM.

  12. #12
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: How To Make DataTable Static

    Quote Originally Posted by 1369Times View Post
    Apology, here's the function that's throwing the exception. The dt is already defined as a parameter.

    Code:
    Private Function ConvertDataToString(ByVal dt As DataTable) As String
        Dim conversion As String = String.Empty
    
        Using writer As IO.TextWriter = New IO.StringWriter()
            dt.WriteXml(writer, XmlWriteMode.WriteSchema)
            conversion = writer.ToString()
        End Using
    
        Return conversion
    End Function
    You are passing a DataTable into this function, you have even named the parameter dt - simply make sure whatever DataTable you pass into this function has a name and it should work just fine.

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    87

    Re: How To Make DataTable Static

    Thanks. I just added dt.TableName = "table" and the error message gone. I need to modify the rest of my codes and run the complete program. I will give feedback shortly.

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Mar 2012
    Posts
    87

    Re: How To Make DataTable Static

    Much respect to all of you! The fix work fine. Thank you very much dday9.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width