Hashtable question

1 Apr 2005 5:31 AM

J L

I have defined a structure

private structure FieldInfo
   dim FieldName as string
   dim OrdinalPostioin as Integer
   dim DataType as Type
   dim Size as Integer
end structure

I read this information from a DataReader which retrieves schema info
from an Access table. (That piece of information is for background
only, does not affect my question).

I fill an arraylist with FieldInfo objects.

I then add this arraylist to a HashTable whose key is the name of the
Access table and value is the arraylist of FieldInfo objects. The
hashtable is named TableFields

I am having trouble retrieving the information. Here is the code I am
using :

dim testField as FieldInfo
dim testFieldList as ArrayList
dim strTableName as String
for i = 0 to TableList.Count - 1
   strTableName = TableList(i)
   testFieldList.Clear()
   testFieldList = TableFields.Item(strTableName)
   dim m as integer
   for m = 0 to testFieldList.Count - 1
      testField = Ctype(testFieldList(m), FieldInfo)
      messagebox.show( testField.FieldName & vbcrlf & _
         testField.DataType.ToString & vbcrlf & _
         testField.Size.ToString)
   next

The problem is that this shows the field information for the second
table as belonging to the first and has no field information for the
second one.

Am I using the correct syntax to access an ArrayList of FieldInfo data
stored in a HashTable?

Is there a better way to go about it? The bottom line is that I want
to have a list of field name, ordinal position, data type and size for
each field in each data table so I can do some validation in my DAL in
a generic way once I know a table name.

TIA,
John

1 Apr 2005 7:01 AM

Cor Ligthert

JL,

Do I see it right that you are creating a table with data that you want to
process using a key?

Cor

1 Apr 2005 1:39 PM

J L

Hi Cor,
No, I am not creating a table. I am reading the schema of the tables
in my Access database and trying to store the field information in a
HashTable whose key is the table name. I need to have one entry per
table in the HashTable with its Value object being an ArrayList that
contains a structure variable for each field in the table. The
strucuture variable holds the field name, ordinal position, data type
and size.

I will post my complete code in my second response.

Thanks,
John

On Fri, 1 Apr 2005 09:01:55 +0200, "Cor Ligthert"
<notmyfirstn***@planet.nl> wrote:

Show quoteHide quote

>JL,
>
>Do I see it right that you are creating a table with data that you want to
>process using a key?
>
>Cor
>

1 Apr 2005 9:48 AM

Larry Lard

J L wrote:
[...]
Show quoteHide quote

> I then add this arraylist to a HashTable whose key is the name of the
> Access table and value is the arraylist of FieldInfo objects. The
> hashtable is named TableFields
>
> I am having trouble retrieving the information. Here is the code I am
> using :
>
> dim testField as FieldInfo
> dim testFieldList as ArrayList
> dim strTableName as String
> for i = 0 to TableList.Count - 1
>    strTableName = TableList(i)
>    testFieldList.Clear()
>    testFieldList = TableFields.Item(strTableName)
[...]
> The problem is that this shows the field information for the second
> table as belonging to the first and has no field information for the
> second one.
>
> Am I using the correct syntax to access an ArrayList of FieldInfo
data
> stored in a HashTable?

This looks fine; what we need to see also is the code where you load up
TableFields in the first place.

>
> Is there a better way to go about it? The bottom line is that I want
> to have a list of field name, ordinal position, data type and size
for
> each field in each data table so I can do some validation in my DAL
in
> a generic way once I know a table name.

A HashTable is a perfectly good way to store (key, value) information.

--
Larry Lard
Replies to group please

1 Apr 2005 1:41 PM

J L

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).
Also to note, the othe HashTable (TablePrimaryKeys) is filled
correctly and displays correctly.

In my test code this is what I see:
1. Displays the correct table names.
2. Displays the correct table primary keys (from the other HashTable I
filled).
3. Shows the 6 fields from Operators when accessing OpDetails (this is
first time through the display loop)
4. Shows no fields for Operators (second time through the loop)
5. I never see the 3 fields from OpDetails.

Seems like the fields for the second table processed (Operators) are
saved for the first table (OpDetails) and no fields saved for the
first table processed (OpDetails).

Hope this is not too confusing and somone can see my mistake.

TIA,
John

Show quoteHide quote

On 1 Apr 2005 01:48:31 -0800, "Larry Lard" <larryl***@hotmail.com>
wrote:

>
>J L wrote:
>[...]
>> I then add this arraylist to a HashTable whose key is the name of the
>> Access table and value is the arraylist of FieldInfo objects. The
>> hashtable is named TableFields
>>
>> I am having trouble retrieving the information. Here is the code I am
>> using :
>>
>> dim testField as FieldInfo
>> dim testFieldList as ArrayList
>> dim strTableName as String
>> for i = 0 to TableList.Count - 1
>>    strTableName = TableList(i)
>>    testFieldList.Clear()
>>    testFieldList = TableFields.Item(strTableName)
>[...]
>> The problem is that this shows the field information for the second
>> table as belonging to the first and has no field information for the
>> second one.
>>
>> Am I using the correct syntax to access an ArrayList of FieldInfo
>data
>> stored in a HashTable?
>
>This looks fine; what we need to see also is the code where you load up
>TableFields in the first place.
>
>>
>> Is there a better way to go about it? The bottom line is that I want
>> to have a list of field name, ordinal position, data type and size
>for
>> each field in each data table so I can do some validation in my DAL
>in
>> a generic way once I know a table name.
>
>A HashTable is a perfectly good way to store (key, value) information.

1 Apr 2005 2:46 PM

Stephany Young

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.

Show quoteHide quote

"J L" <j***@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).
> Also to note, the othe HashTable (TablePrimaryKeys) is filled
> correctly and displays correctly.
>
> In my test code this is what I see:
> 1. Displays the correct table names.
> 2. Displays the correct table primary keys (from the other HashTable I
> filled).
> 3. Shows the 6 fields from Operators when accessing OpDetails (this is
> first time through the display loop)
> 4. Shows no fields for Operators (second time through the loop)
> 5. I never see the 3 fields from OpDetails.
>
> Seems like the fields for the second table processed (Operators) are
> saved for the first table (OpDetails) and no fields saved for the
> first table processed (OpDetails).
>
> Hope this is not too confusing and somone can see my mistake.
>
> TIA,
> John
>
>
> On 1 Apr 2005 01:48:31 -0800, "Larry Lard" <larryl***@hotmail.com>
> wrote:
>
>>
>>J L wrote:
>>[...]
>>> I then add this arraylist to a HashTable whose key is the name of the
>>> Access table and value is the arraylist of FieldInfo objects. The
>>> hashtable is named TableFields
>>>
>>> I am having trouble retrieving the information. Here is the code I am
>>> using :
>>>
>>> dim testField as FieldInfo
>>> dim testFieldList as ArrayList
>>> dim strTableName as String
>>> for i = 0 to TableList.Count - 1
>>>    strTableName = TableList(i)
>>>    testFieldList.Clear()
>>>    testFieldList = TableFields.Item(strTableName)
>>[...]
>>> The problem is that this shows the field information for the second
>>> table as belonging to the first and has no field information for the
>>> second one.
>>>
>>> Am I using the correct syntax to access an ArrayList of FieldInfo
>>data
>>> stored in a HashTable?
>>
>>This looks fine; what we need to see also is the code where you load up
>>TableFields in the first place.
>>
>>>
>>> Is there a better way to go about it? The bottom line is that I want
>>> to have a list of field name, ordinal position, data type and size
>>for
>>> each field in each data table so I can do some validation in my DAL
>>in
>>> a generic way once I know a table name.
>>
>>A HashTable is a perfectly good way to store (key, value) information.
>

1 Apr 2005 3:20 PM

J L

Hi Stephany,
My PC ate my original response. I dont know if it will show up later
so here goes again...

You are fantastic! I did understand your explanation perfectly and
when I moved the declaration of the fieldList object into the loop
that was creating and filling it, it worked perfectly. Than you so
very much!

I am continually amazed and impressed by the talent and effort of
individuals on this NG. I am trying to contribute when possible but
want to say THANKS to all of you GURUS for both your expertise and
time! I am sure the Universe will reward you for your kindness.

John

Show quoteHide quote

On Sat, 2 Apr 2005 02:46:23 +1200, "Stephany Young" <noone@localhost>
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" <j***@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).
>> Also to note, the othe HashTable (TablePrimaryKeys) is filled
>> correctly and displays correctly.
>>
>> In my test code this is what I see:
>> 1. Displays the correct table names.
>> 2. Displays the correct table primary keys (from the other HashTable I
>> filled).
>> 3. Shows the 6 fields from Operators when accessing OpDetails (this is
>> first time through the display loop)
>> 4. Shows no fields for Operators (second time through the loop)
>> 5. I never see the 3 fields from OpDetails.
>>
>> Seems like the fields for the second table processed (Operators) are
>> saved for the first table (OpDetails) and no fields saved for the
>> first table processed (OpDetails).
>>
>> Hope this is not too confusing and somone can see my mistake.
>>
>> TIA,
>> John
>>
>>
>> On 1 Apr 2005 01:48:31 -0800, "Larry Lard" <larryl***@hotmail.com>
>> wrote:
>>
>>>
>>>J L wrote:
>>>[...]
>>>> I then add this arraylist to a HashTable whose key is the name of the
>>>> Access table and value is the arraylist of FieldInfo objects. The
>>>> hashtable is named TableFields
>>>>
>>>> I am having trouble retrieving the information. Here is the code I am
>>>> using :
>>>>
>>>> dim testField as FieldInfo
>>>> dim testFieldList as ArrayList
>>>> dim strTableName as String
>>>> for i = 0 to TableList.Count - 1
>>>>    strTableName = TableList(i)
>>>>    testFieldList.Clear()
>>>>    testFieldList = TableFields.Item(strTableName)
>>>[...]
>>>> The problem is that this shows the field information for the second
>>>> table as belonging to the first and has no field information for the
>>>> second one.
>>>>
>>>> Am I using the correct syntax to access an ArrayList of FieldInfo
>>>data
>>>> stored in a HashTable?
>>>
>>>This looks fine; what we need to see also is the code where you load up
>>>TableFields in the first place.
>>>
>>>>
>>>> Is there a better way to go about it? The bottom line is that I want
>>>> to have a list of field name, ordinal position, data type and size
>>>for
>>>> each field in each data table so I can do some validation in my DAL
>>>in
>>>> a generic way once I know a table name.
>>>
>>>A HashTable is a perfectly good way to store (key, value) information.
>>
>

1 Apr 2005 5:32 PM

J L

OMG!!! You are fantastic! I do understand it with extreme clarity.
Once I moved the declaration of fieldList into my loop, it worked
fine. As for the display routine, the part where I was clearing the
object I had actually discarded already. And now the Universe is in
order.

I have to say that I am continually amazed at the quality of
individuals on this NG. I try to contribute when I can but thanks to
you and all the other GURUS for your support!!

John

Show quoteHide quote

On Sat, 2 Apr 2005 02:46:23 +1200, "Stephany Young" <noone@localhost>
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" <j***@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).
>> Also to note, the othe HashTable (TablePrimaryKeys) is filled
>> correctly and displays correctly.
>>
>> In my test code this is what I see:
>> 1. Displays the correct table names.
>> 2. Displays the correct table primary keys (from the other HashTable I
>> filled).
>> 3. Shows the 6 fields from Operators when accessing OpDetails (this is
>> first time through the display loop)
>> 4. Shows no fields for Operators (second time through the loop)
>> 5. I never see the 3 fields from OpDetails.
>>
>> Seems like the fields for the second table processed (Operators) are
>> saved for the first table (OpDetails) and no fields saved for the
>> first table processed (OpDetails).
>>
>> Hope this is not too confusing and somone can see my mistake.
>>
>> TIA,
>> John
>>
>>
>> On 1 Apr 2005 01:48:31 -0800, "Larry Lard" <larryl***@hotmail.com>
>> wrote:
>>
>>>
>>>J L wrote:
>>>[...]
>>>> I then add this arraylist to a HashTable whose key is the name of the
>>>> Access table and value is the arraylist of FieldInfo objects. The
>>>> hashtable is named TableFields
>>>>
>>>> I am having trouble retrieving the information. Here is the code I am
>>>> using :
>>>>
>>>> dim testField as FieldInfo
>>>> dim testFieldList as ArrayList
>>>> dim strTableName as String
>>>> for i = 0 to TableList.Count - 1
>>>>    strTableName = TableList(i)
>>>>    testFieldList.Clear()
>>>>    testFieldList = TableFields.Item(strTableName)
>>>[...]
>>>> The problem is that this shows the field information for the second
>>>> table as belonging to the first and has no field information for the
>>>> second one.
>>>>
>>>> Am I using the correct syntax to access an ArrayList of FieldInfo
>>>data
>>>> stored in a HashTable?
>>>
>>>This looks fine; what we need to see also is the code where you load up
>>>TableFields in the first place.
>>>
>>>>
>>>> Is there a better way to go about it? The bottom line is that I want
>>>> to have a list of field name, ordinal position, data type and size
>>>for
>>>> each field in each data table so I can do some validation in my DAL
>>>in
>>>> a generic way once I know a table name.
>>>
>>>A HashTable is a perfectly good way to store (key, value) information.
>>
>