Choosing suitable format to display data

[NFLDLeaf]

Hi Guys,

I am a software engineer by trabe but haven't had the opportunity to dabble with VB until now. I have a little project that has been going pretty well until now. Besically the problem is that I have data from multiple SQL queries that has been formatted in a particular way. Modifying the SQL isn't an option as the formatting goes beyond what SQL is meant for. Once I have all the data and have it formatted and stored in my own data type (say a double array of Strings/Variants) I want to display it in a table with certian headers, column sizes,...etc.

What choices are available for the display control in VB? I don't think I can use a basic data grid as I havan't been able to figure out how to designate my double array of strings as the data source. There might be a way of creating an ADODB.Recordset and setting it's row values by hand, but I haven't figured that out yet. Perhaps using an excel spreadsheet in the application would be a solution? I haven't really looked into that yet.

If anyone can offer thier thoughts on this I would appreciate it.

[KevinJL]

Hi Joe,

You quote:

"There might be a way of creating an ADODB.Recordset and setting it's row values by hand"

Yes, you could create a disconnected recordset and have it live only in memory. Then loop through your array and add a record with each pass. When all the records have been added set the datasource of the datagrid to the recordset.

Here is an example of how I do this:
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient <--Have to have
rst.LockType = adLockBatchOptimistic <--Have to have
With rst
.Fields.Append Name:="ClassMember", Type:=adBigInt
.Fields.Append Name:="ErrorLogID", Type:=adBigInt
.Fields.Append Name:="EventLogID", Type:=adBigInt
.Fields.Append Name:="EventLogTime", Type:=adDate
.Fields.Append Name:="EventLogValue", Type:=adVariant
.Fields.Append Name:="EventDescription", Type:=adVarChar, DefinedSize:=255
.Open
End With

-OR-

You could skip the recordset business and populate your gird directly form the array. Run the function Ubound(), which will return the number of elements in your array.

Add one to that number ( to take into account a fixed row at the top of your grid) to the rows property of the grid. Then loop through your array and populate each row individually.

Here I am using a flexgrid but I would imagine the same principal would apply. As you can see I am populating the grid using a recordset, but we would just replace the rst stuff with your array stuff.

Hmm... come to think of it I guess this could be a third way <g>

' setup the grid
With fgrErrorLog
.Rows = morstErrorLog.RecordCount + 1
.Col = 0
.Row = 0
.Text = "Event Number"
.CellAlignment = flexAlignCenterCenter
.Col = 1
.Row = 0
.Text = "Event"
.CellAlignment = flexAlignCenterCenter
cont...

' populate the grid
With fgrErrorLog
.Col = 0
.Row = I
.Text = morstErrorLog!EventLogID.Value
.Col = 1
.Row = I
.Text = morstErrorLog!EventDescription.Value
cont...

HTH
Kevin

[vb_dba]

You could use a ListView to display the results in a grid that has column headers and the columns are sizable. You will need to add a reference in your project to Microsoft Windows Common Controls 6.0. Then just put the Listview on your form. Then to populate it:

'Define the column Headers, Size, and alignment
ListView1.ColumnHeaders.Clear
ListView1.ColumnHeaders.Add , , "Col1", 1500, 0
ListView1.ColumnHeaders.Add , , "Col2", 750, 2
ListView1.ColumnHeaders.Add , , "Col3", 750, 2
 
'Clear any existing list items
ListView1.ListItems.Clear
For i = LBound(myArray, 2) To UBound(myArray, 2)
    'Add Column 1 data
    With ListView1.ListItems.Add(, , myArray(0, i))
            'Add Column 2 data
            .ListSubItems.Add , , myArray(1, i)
            'Add Column 3 data
            .ListSubItems.Add , , myArray(2, i)
    End With
Next

[NFLDLeaf]

Hey Chris and Kevin,

Thanks for the very speedy replies! I appreciate it. I am sure I will be able to make use of the information, both in this particular case and in future cases with the different approaches may be more suitable!

[NFLDLeaf]

Hey Chris and Kevin,

Thanks for the very speedy replies! I appreciate it. I am sure I will be able to make use of the information, both in this particular case and in future cases with the different approaches may be more suitable!