Dataset Q's: Column Sizing and Value getting.

[stingrae]

Hey, 2 questions:

with a flexgrid it was really easy to do the following things:

1) change the size of a flexgrid's columns. e.g. MSFlexGrid.ColWidth(3) = 500
2) get the text that is in a cell. e.g. MSFlexGrid.TextMatric(RowNum,ColNum)



are either of these methods available with the Datagrid control in Vb.NET? I saw there is a MaxColumnWidth property (or something similar), but nothing as nice as above.

anyone figured out this before??

cheers,

stingrae

[stingrae]

anyone?

[briancps]

Ive always used Viseosofts grids (now Component one I think)-I gave up with VB.net and unbound grids because it was too hard. I am using FlexGrid 7.0 as a COM addin (which is the VB6 flexgrid with more features I think). works fine and has all the features of flexgrid. If I had more time I would probably persist but I get paid when i install the software and this works.
I am not using bound grids as the application I am installing dosn't need it.

[stingrae]

I found this at the following website:
www.msde.biz/vbnetfaq.htm

I can't seem to get it to work though.

    Public Sub DataGridApplyAutomaticWidths _
    (ByVal DataGrid As System.Windows.Forms.DataGrid, ByVal NumberOfRowsToScan As Integer, ByVal MaxPixelWidth As Integer)
        'Method: DataGridApplyAutomaticWidths
        'Parameters
        ' DataGrid=DataGrid that is to be formatted.
        ' NumberOfRowsToScan=Number of data records to be scanned in order to compute the columns widths (Recommend 20).
        ' MaxPixelWidth=Maximum allowable pixel width of a column.
        '
        'Takes a DataGrid that should be already assigned a populated dataset,
        'and formats the DataGrid so that the column widths reflect the widths of the data.
        'This is accomplished by dynamically creating a single DataGridTableStyle and multiple DataGridTextBoxColumn(s).
        'For each data column, the width of the column is set to the highest data width within the column. 
        'In order to reduce computation time where large datasets exist, 
        'a parameter exists to define how many rows are scanned.
        'I would suggest that only the first 20 rows are scanned.
        '
        'Create graphics object for measuring widths.
        Dim Graphics As Graphics = DataGrid.CreateGraphics()
        Try
            Dim DataTable As DataTable = DataGrid.DataSource.DataSet.Tables(0)
            'Clear any existing table styles.
            DataGrid.TableStyles.Clear()
            'Define new table style
            Dim TableStyle As DataGridTableStyle = New DataGridTableStyle()
            'Use mapping name that is defined in the data source.
            TableStyle.MappingName = DataTable.TableName
            'Now create the column styles within the table style.
            Dim Column As DataColumn
            Dim ColumnStyle As DataGridTextBoxColumn
            Dim Width As Integer
            For Each Column In DataTable.Columns
                ColumnStyle = New DataGridTextBoxColumn()
                With ColumnStyle
                    .TextBox.Enabled = True
                    .HeaderText = Column.ColumnName
                    .MappingName = Column.ColumnName
                    'Set width to header text width.
                    Width = Graphics.MeasureString(.HeaderText, DataGrid.Font, MaxPixelWidth).Width
                End With
                'Change width, if data width is wider than header text width.
                Dim iRow As Integer
                'Check the width of the data in the first X rows.
                Dim IUpToRow As Integer = NumberOfRowsToScan
                If DataTable.Rows.Count <= IUpToRow Then
                    IUpToRow = DataTable.Rows.Count - 1
                End If
                Dim DataRow As DataRow
                For iRow = 0 To IUpToRow
                    DataRow = DataTable.Rows(iRow)
                    If Graphics.MeasureString(DataRow(Column.ColumnName), DataGrid.Font, MaxPixelWidth).Width > Width Then
                        Width = Graphics.MeasureString(DataRow(Column.ColumnName), DataGrid.Font, MaxPixelWidth).Width
                    End If
                Next
                ColumnStyle.Width = Width + 4
                'Add the new column style to the table style.
                TableStyle.GridColumnStyles.Add(ColumnStyle)
            Next
            'Add the new table style to the data grid.
            DataGrid.TableStyles.Add(TableStyle)
        Finally
            Graphics.Dispose()
        End Try
    End Sub

[stingrae]

briancps,

thanks for your input. i have now reverted back to the trusted MSFlexGrid. one question, do you (or anyone) know what the constant values are for

set_ColAlignment

?

I cannot find anything on the web for this method.

cheers,

stingrae.

[briancps]

these are the constants for the VS7 Flexgrid - probably should be the same for the MSFlexgrid ???

hope this helps

regards

BH


this is the colalignment property

Returns or sets the alignment of the given column.
Syntax
[form!]VSFlexGrid.ColAlignment(Col As Long)[ = AlignmentSettings ]
Remarks
Valid settings for the ColAlignment property are:

Constant Value
flexAlignLeftTop 0
flexAlignLeftCenter 1
flexAlignLeftBottom 2
flexAlignCenterTop 3
flexAlignCenterCenter 4
flexAlignCenterBottom 5
flexAlignRightTop 6
flexAlignRightCenter 7
flexAlignRightBottom 8
flexAlignGeneral 9
The flexAlignGeneral setting aligns text to the left and numbers and dates to the right.

The ColAlignment property affects all cells in the specified column, including those in fixed rows. You may override this setting for fixed cells using the FixedAlignment property. You may override it for individual cells using the Cell(flexcpAlignment) property.


This example sets the alignment of the third column to the right and bottom

fg.ColAlignment(2) = flexAlignRightBottom


You may set the alignment of pictures in cells using the CellPictureAlignment or Cell(flexcpPictureAlignment) properties.

When setting this property, the Col parameter should be set to a value between zero and Cols - 1 to set the alignment of a given column, or to -1 to set the alignment of all columns.

Data Type
AlignmentSettings (Enumeration)

[briancps]

also here is my cde to fill the grid with the results of my query in case this helps. the grid contains employer codes and names plus an ID from the Db which the column 0 is set to hidden

regards

BH


Private Sub DataRefresh()
'refresh the list box based on the current search filter

Dim sSql As String
Dim cn As New OleDb.OleDbConnection(dbcConnection)
cn.Open()

'set sql string to reflect search criteria
If txtSearch.Text = "" Then
sSql = "SELECT ID, EMCode, EMName FROM Employers ORDER BY EMCode"
Else
sSql = "SELECT ID, EMCode, EMName FROM Employers WHERE EMCode LIKE '%" & txtSearch.Text & "%' OR EMName LIKE '%" & txtSearch.Text & "%' ORDER BY EMCode"
End If

'open the ADO.NET Datareader
Dim cmd As New OleDb.OleDbCommand(sSql, cn)
Dim drd As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'fill flexgrid here
lstSearch.Rows = 1
lstSearch.FormatString = "ID|Code|Name"
lstSearch.set_ColWidth(0, 960)
lstSearch.set_ColWidth(1, 1200)
lstSearch.set_ColWidth(2, 2700)

Do While drd.Read
lstSearch.AddItem(drd!ID & vbTab & drd!EMCode & vbTab & drd!EMName)
Loop
drd.Close()

End Sub

[stingrae]

Cool!!! Thanks heaps for those constants.

I prefer to use the TextMatric property to add data. I just find it easier on the eyes.

        Dim strSQLStm As String
        grdRates.Rows = 1
        strSQLStm = "SELECT * " & _
                    "FROM tbl_ItemRate " & _
                    "WHERE itr_Item = " & ItemID & " " & _
                      "AND itr_Active = True " & _
                    "ORDER BY itr_Name"
        Debug.WriteLine(strSQLStm)
        Dim cmdRates As New OleDb.OleDbCommand(strSQLStm, gconDatabase)
        Dim datRates As OleDb.OleDbDataReader

        cmdRates.Connection = gconDatabase
        datRates = cmdRates.ExecuteReader
        Do Until datRates.Read = False
            grdRates.AddItem("")
            grdRates.set_TextMatrix(grdRates.Rows - 1, cgrdName, datRates("itr_Name"))
            grdRates.set_TextMatrix(grdRates.Rows - 1, cgrdPrice, Format(datRates("itr_Price"), "#,##0.00"))
            grdRates.set_TextMatrix(grdRates.Rows - 1, cgrdTax, Format(datRates("itr_Tax"), "#,##0.00"))
            grdRates.set_TextMatrix(grdRates.Rows - 1, cgrdTotal, Format(datRates("itr_Price") + _
                                                                         datRates("itr_Tax"), "#,##0.00") & "    ")
            grdRates.set_TextMatrix(grdRates.Rows - 1, cgrdID, datRates("itr_ID"))
        Loop
        datRates.Close()

i downloaded the eval version of FlexGrid8 but cannot get it to work real well. The demos look great but at AUD$600, it's a bit too steep.

[briancps]

didn't relaize it was that expensive now. We've used VS controls since version 3 and just keep upgrading at the upgrade cost
BH