wrote:

>The problem is when you are attempting to store the 'fieldList' objects.
>
>An ArrayList is a reference type. When you assign an instance of a reference
>type to something you are assigning a reference to the source object, not a
>copy of it.
>
>The declaration Dim fieldList As New ArrayList is the only place where
>fieldList is established as a 'new' object and all the later references to
>it are references to the original object.
>
>While loading, if you read the content of TableFields.Item(0) during the
>second iteration of the outer loop, directly after after the
>fieldList.Clear() line, you may be surprised to find that the 'fields' that
>you saw get loaded are longer there. This is because what is stored in the
>hash table is a reference to the object named fieldList. Follow my drift?
>
>Now it gets a bit difficult and you have to do some mental juggling and it
>took me me a little while to 'get it', but when you add the fieldlist object
>to the hash table on the second iteration of you are again adding a
>reference to the fieldlist object so you your hash table now has 2
>references to the same object which now holds the 'fields' from the second
>table. Still with me?
>
>TableList and TablePrimaryKeys are OK because you are adding strings to
>these hash tables and because a string is a value type the actual object
>gets copied as opposed to a reference to it.
>
>In the display loop you are creating yet another reference to the original
>object - tstFieldList = CType(TableFields.Item(key), ArrayList) - but on the
>second iteration you're clearing the contents of the referenced object and
>this is why you see no 'fields' on the second iteration.
>
>There you have it - absolute clarity.
>
>
>"J L" <john@marymonte.com> wrote in message
>news:gpjq415iv6c7jcpbfj3m0m8k30kcg9uvai@4ax.com...
>> Thanks Cor and Larry for any help you can give me. Here is the code I
>> am using:
>>
>> _____________________________________
>> Here are the declarations:
>>
>> Private TableList As New ArrayList
>> Private TablePrimaryKeys As New Hashtable
>> Private TableFields As New Hashtable
>>
>> Private Structure FieldInfo
>> Dim FieldName As String
>> Dim OrdinalPosition As Integer
>> Dim DataType As Type
>> Dim Size As Integer
>> End Structure
>>
>> ____________________________________
>> Here is how I fill them:
>>
>> Dim dt As DataTable
>> Dim dRow As DataRow
>> Dim dColumn As DataColumn
>> Dim aNull As DBNull
>> Dim aField As New FieldInfo
>> Dim fieldList As New ArrayList
>> Dim strPKList As String
>> Dim strTableName As String
>>
>> Dim i As Integer
>>
>> ConfigOpt.Initialize(Application.StartupPath & "\" &
>> Application.ProductName & ".cfg")
>> MarymonteDALConnectString = ConfigOpt.GetOption("Connect String")
>> MarymonteDALDataProvider = ConfigOpt.GetOption("DB Type")
>>
>> TableList.Clear()
>> TablePrimaryKeys.Clear()
>> TableFields.Clear()
>>
>> Try
>> If MarymonteDALDataProvider = "OleDb" Then
>> Dim cn As New OleDbConnection(MarymonteDALConnectString)
>> Dim cmd As New OleDbCommand
>> Dim myReader As OleDbDataReader
>>
>> ' test the connection string and read table info
>> cn.Open()
>> ' get list of tables
>> Dim myNull() As Object = {aNull, aNull, aNull, "TABLE"}
>> dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, myNull)
>> Dim dr As DataRow
>> For Each dr In dt.Rows
>> TableList.Add(dr("TABLE_NAME"))
>> Next
>> ' read table data
>> cmd.Connection = cn
>> For i = 0 To TableList.Count - 1
>> strTableName = TableList(i)
>> cmd.CommandText = "SELECT * FROM " & strTableName
>> myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
>> dt = myReader.GetSchemaTable()
>> strPKList = ""
>> fieldList.Clear()
>> For Each dRow In dt.Rows
>> aField.FieldName = dRow("ColumnName")
>> aField.OrdinalPosition = dRow("ColumnOrdinal")
>> aField.DataType = dRow("DataType")
>> aField.Size = dRow("ColumnSize")
>> fieldList.Add(aField)
>> If dRow("IsKey") Then
>> strPKList += dRow("ColumnName") & ";"
>> End If
>> Next
>> TableFields.Add(strTableName, fieldList)
>> TablePrimaryKeys.Add(strTableName, strPKList)
>> myReader.Close()
>> Next
>>
>> ________________________________________________
>> And here is my test code to access them:
>>
>> Private Sub DisplayTableInfoTest()
>> Dim i As Integer
>> Dim tstField As New FieldInfo
>> Dim tstFieldList As New ArrayList
>> Dim strTableName As String
>>
>> Dim key As New Object
>> For Each key In TableFields
>> tstFieldList.Clear()
>> tstFieldList = CType(TableFields.Item(key), ArrayList)
>> Dim n As Integer
>> For n = 0 To tstFieldList.Count - 1
>> tstField = CType(tstFieldList(n), FieldInfo)
>> MessageBox.Show(tstField.FieldName)
>> Next
>> Next
>> For i = 0 To TableList.Count - 1
>> strTableName = TableList(i)
>> tstFieldList.Clear()
>> tstFieldList = TableFields.Item(strTableName)
>> MessageBox.Show("Primary keys for " & strTableName & ": " &
>> TablePrimaryKeys(strTableName), strTableName)
>> Dim strTest As String
>> Dim m As Integer
>> For m = 0 To tstFieldList.Count - 1
>> tstField = CType(tstFieldList(m), FieldInfo)
>> strTest = ""
>> strTest += tstField.FieldName & vbCrLf & _
>> tstField.OrdinalPosition.ToString & vbCrLf & _
>> tstField.DataType.ToString & vbCrLf & _
>> tstField.Size.ToString
>> MessageBox.Show(strTest, TableList(i))
>> Next
>> Next
>> End Sub
>>
>> ____________________________________________________
>>
>> There are only two tables in the database (OpDetails and Operators).
>> OpDetails has 3 fields and Operators has 6. I see it fill the
>> ArrayList (fieldList) with the correct number of fields for each
>> table. And it saves that field list to the HashTable (TableFields).