Hashtable question

[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

[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

[Larry Lard]

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.

--
Larry Lard
Replies to group please

[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"
<no************@planet.nl> wrote:

JL,

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

Cor

[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
On 1 Apr 2005 01:48:31 -0800, "Larry Lard" <la*******@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.

[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.
"J L" <jo**@marymonte.com> wrote in message
news:gp********************************@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" <la*******@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.

[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
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" <jo**@marymonte.com> wrote in message
news:gp********************************@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" <la*******@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.

[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

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" <jo**@marymonte.com> wrote in message
news:gp********************************@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" <la*******@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.