Getting a custom table based on DB data

[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

 

[MStef]

Try to do this with CROSSTAB query.

 

[gemma-the-husky]

i don't think you can do this with a query, because its anti normalized behaviour. you can do it quite easily with code - look in similar threads in reports, because thats where i've seen threads for this.

 

[malhavoc]

In fact I was doing that through code, but it becomes too slow when it comes to many rows. Here's the code I was using: maybe there is something I can improve in it?


'dtH is the table I need to fill, and it already has the correct table schema.
'''''''''''''Private Sub LoadItems(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

 

[gemma-the-husky]

thinking again, and igniring the values, how many different user types are there ie fieldA, fieldb, fieldc etc. - do you control these with a lookup etc? if there is a realtive few, perhaps its doable with queries

 

[malhavoc]

I've given a try to the crosstab query (which I just started learning now). Seems like it does exactly what I need!

Using the Access Wizard I've generated this code. I used First as the aggregate function (I wasn't very sure it was the right one really); I don't get what the first line does exactly (maybe I can clean the query a bit), but this very simple query seems to do EXACTLY what I needed. And in a eyeblink!

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

 

[MStef]

Hello Malhavoc!

I think it cna be done with CROSSTAB query.
Look at "DemoCrosstabMalhavocA2000.mdb" (attachment).

 

[malhavoc]

Ok, that does the trick. However, I still have one problem: I've added the crosstab query in the Access DB, but I don't know how to call it from within VB.NET. I used to set up TableAdapters in the DataSource designer and create simple select queries directly from visual studio, but I don't know how to call from .NET a query already in the DB.. any help please?

EDIT:

I've managed to query the db, but with this code the data_adapter.Fill(dt) takes some seconds to resolve, while calling the query directly from Access takes only an eyeblink. Is there a better way to fill a datatable with the resulting records?

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)