Getting a custom table based on DB data

  • Thread starter Thread starter Malhavoc
  • Start date Start date
M

Malhavoc

I have a table in the Access DB which is structured this way:

ITEMID
FIELDNAME
FIELDVALUE

The Key is ITEMID,FIELDNAME

I need such a configuration because the items I need to store can have
whatever fields the user could like. A simple example of the data
contained could be:

ITEMID;FIELDNAME;FIELDVALUE
1;FIELDA;1111
1;FIELDB;2222
2;FIELDA;3333
2;FIELDB;4444


I need to load a datagrid in VB.NET displaying a table like this
(according to the above example):

ITEM;FIELDA;FIELDB
1;1111;2222
2;3333;4444

I used to just load the data from Access and then build the grid
directly from VB, but the records are becoming too many and this
approach is just too slow. I was thinking about creating the table I
need directly from a query, and then loading the result in a grid;
however, this is something I'm having problems doing. Could anyone help
me? Thanks.

Just a couple of notes:

- not every field is necessary present for each ITEM, so I need to
handle the possibility of DBNull entries for some fields.

- If it can help, I know which fieldnames I can get before querying the
DB



If, instead, you think I should focus on improving the VB code, here it
is:



'dtH is the table I need to fill, and it already has the correct table
schema.
Private Sub LoadCards(ByVal dtToLoad As DataTable)
Dim i As Integer
Dim rowcount As Integer
Dim row As DataRow
dtH.Clear()
rowcount = dtToLoad.Rows.Count - 1
i = 0
Do While i <= rowcount
row = dtH.NewRow
row.Item("ITEMID") = dtToLoad.Rows(i).Item("ITEMID")
Do Until dtToLoad.Rows(i).Item("ITEMID") <>
row.Item("ITEMID") OrElse i > rowcount
If dtToLoad.Rows(i).Item("FIELDNAME") IsNot
DBNull.Value Then
row.Item(dtToLoad.Rows(i).Item("FIELDNAME")) =
dtToLoad.Rows(i).Item("FIELDVALUE")
End If
i += 1
Loop
dtH.Rows.Add(row)
Loop
End Sub
 
The problem isn't with your code. You have a defective non-relational
database(?) structure. I recommend a good book on database design.
 
Earl ha scritto:
The problem isn't with your code. You have a defective non-relational
database(?) structure. I recommend a good book on database design.
Click to expand...

The problem is that I can't really do any better with this table. The
rest of the database is structured well, but here I can't foresee which
fields the table could have, so I need this NAME-VALUE workaround.

However, I finally managed to solve my problem using a Crosstab query:
it does exactly what I need, and in a eyeblink :-)

TRANSFORM First(TABLE.FIELDVALUE) AS FirstOfVALUE
SELECT TABLE.ITEMID
FROM TABLE
GROUP BY TABLE.ITEMID
PIVOT TABLE.FIELDNAME;
 
I've managed to obtain what I needed with a crosstab query in Access.
Something similar to this:

TRANSFORM First(TABLE.FIELDVALUE) AS FirstOfVALUE
SELECT TABLE.ITEMID
FROM TABLE
GROUP BY TABLE.ITEMID
PIVOT TABLE.FIELDNAME;


This runs in an eyeblink in access. However, when I try to load it as a
DataTable in VB, it still takes an handful of seconds.


Dim q,connectionstring As String
q = 'Here I've copied all the SQL code above
connectionstring = 'just the connection string to my access file
Dim dt as DataTable
Dim data_adapter As New OleDb.OleDbDataAdapter(q, connectionstring)
data_adapter.Fill(dt)

Any way to improved the performance?
 
Back
Top