Question datagrid alignment of columns

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
I've scoured the net and it seems to me we can't custom align the columns of a datagrid in vb.net ce.

Is there another way around it?

I have a currency field that I'd like to align to the right. The grid is set to do left internally and we can't change it.

The desktop version allows this.

regards, Ricky
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
You'll need to create a DataGridTableStyle, append custom columns to it, and apply the style to the grid. Dont quote me on this (as I am not sitting near a working solution), but it's something like :-

Code:
        Dim tbEnquiry As DataGridTableStyle = New DataGridTableStyle
        dgEnquiry.TableStyles.Clear()

        Dim dataGridCustomColumn As DataGridCustomTextBoxColumn

        dataGridCustomColumn = New DataGridCustomTextBoxColumn
        With dataGridCustomColumn
            .Owner = dgEnquiry
            .MappingName = "CURRENCY_FIELD_NAME_GOES_HERE"
            .Alignment = HorizontalAlignment.Right
        End With
        tbEnquiry.GridColumnStyles.Add(dataGridCustomColumn)
        dataGridCustomColumn.Dispose()

        dgEnquiry.TableStyles.Add(tbEnquiry)

        tbEnquiry.Dispose()
If you cant get it to work, let me know. I also have a solution for colouring cells based on values it it would help.
 

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
doesn't work

the DataGridCustomTextBoxColumn

doesn't have a property called Alignment in .net cf
it also doesn't have a Owner property either

I looked at the DataGridColumnStyle and it doesn't have alignment either.

I believe the desktop version does.

any ideas?

regards, Ricky
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
Sorry Ricky - I should have checked the definition on DataGridCustomTextBoxColumn before I posted that :eek: It's been so long since I did any CF projects.

You'll need the attached file :)
 

Attachments

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
got the file now what?

I have loaded the file into a .vb file and added it to my project.

How do I implement it?

I haven't built a grid from the ground up which is what I think I need to do to make this work.

regards, Ricky
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
Yes, unfortunately the only way I found (although there may be others) was to build the grid completely by code.

In the New event for the form, I execute a Prepare method which prepared the command and the grid, and then execute a Process method as necessary.

I've put the cell color change in as well for you :)

Code:
Private cmdEnquiry As SqlCeCommand = New SqlCeCommand

Private Sub Prepare_Enquiry()
    cmdEnquiry.Connection = sConn
    cmdEnquiry.CommandText = "TABLE_NAME"
    cmdEnquiry.CommandType = CommandType.TableDirect
    cmdEnquiry.IndexName = "idxEnquiry"

    Dim tbEnquiry As DataGridTableStyle = New DataGridTableStyle
    dgEnquiry.TableStyles.Clear()

    Dim dataGridCustomColumn As DataGridCustomTextBoxColumn

    'START: repeat this section as necessary for all required columns
    dataGridCustomColumn = New DataGridCustomTextBoxColumn
        With dataGridCustomColumn
        .Owner = dgEnquiry
        .HeaderText = "QTY"
        .MappingName = "QUANTITY"
        .Width = 40
        .AlternatingBackColor = alternatingColor
        .Alignment = HorizontalAlignment.Right
        .ReadOnly = True
        .NullText = ""
        AddHandler .SetCellFormat, AddressOf Enquiry_SetCellFormat
    End With
    tbEnquiry.GridColumnStyles.Add(dataGridCustomColumn)
    dataGridCustomColumn.Dispose()
    'END: repeat this section as necessary for all required columns

    dgEnquiry.TableStyles.Add(tbEnquiry)

    tbEnquiry.Dispose()
End Sub


Private Sub Process_Enquiry()
    dgEnquiry.Visible = False
    dgEnquiry.SuspendLayout()
    cmdEnquiry.SetRange(DbRangeOptions.Match, New Object() {txtCode.Text.ToUpper}, Nothing)
    sqlDA.SelectCommand = cmdEnquiry
    Try
        sqlDS.Tables.Clear()
        sqlDA.Fill(sqlDS, 0, 200, "Enquiry")
        Dim Enquiry As DataTable = sqlDS.Tables(0)
        dgEnquiry.DataSource = Enquiry
        Enquiry.Dispose()
    Catch ex As SqlCeException
        MessageBox.Show("SQLCE error: " & ex.NativeError & vbCrLf & ex.Message)
    Finally
        dgEnquiry.ResumeLayout()
        dgEnquiry.Visible = True
    End Try
End Sub

Private Const enq_OutstandingQuantity As Int32 = 5 'column number
Private enq_Completed As New SolidBrush (SystemColors.GrayText)
Private Sub Enquiry_SetCellFormat(ByVal sender As Object, ByVal e As DataGridFormatCellEventArgs)
    If Convert.ToInt32(dgEnquiry.Item(e.Row, enq_OutstandingQuantity)) <= 0 then
        e.BackBrush = enq_Completed
    End If
End Sub 'SetCellFormat
 

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
Thanks, that works fine

Thanks for your help.

That works just right.

regards, Ricky
 

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
one more question

All the grid stuff is working good.

I haven't tried the color thing. I don't have a need at this stage.

I'd like to make the format of a boolean column to be yes/no instead of true/false

I found some code that was make it work but alas I think it's for the desktop not ce.

Do you know how I can do this?

regards, Ricky
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
Not at work so I cant give you a CF solution, but if you are pulling the data using a query, you could change the format from a boolean to text and then use a CustomTextColumn on it. For example :-

Code:
SELECT
  CASE WHEN TABLE_NAME.COLUMN_NAME = TRUE THEN 'YES' ELSE 'NO' END
AS COLUMN_NAME_TEXT
FROM
  TABLE_NAME
 

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
Ummm that doesn't work

I'm using VS2005 and Sqlce 3.0.

This is my SQL statement. I put the Case statement in just like it says on the Microsoft website for v3.5

Code:
Select FareId,FareDate,FareHash,TimeStart,TimeEnd,Pickup,Dropoff,FareType, CASE Booked WHEN True THEN 'Yes' WHEN False THEN 'No' END From Fares Order By FareDate Desc, FareHash Desc
and it throws the following error

Code:
The column name is not valid. [Node name (if any)= ,Column name = True
I'm now assuming I need to use sqlce 3.5 which is called Sql Server Compact.

I'll create a test app to see if it works.

regards, Ricky
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
Try this instead :-

Code:
CASE
  WHEN Booked = True THEN 'Yes'
  WHEN Booked = False THEN 'No'
  ELSE 'N/A' END
AS
  BookedText
 

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
didn't work but finally got it to work

This code is in my frmFares which has the custom column datagrid.

I changed the sql statement to

Code:
Select FareId,FareDate,FareHash,TimeStart,TimeEnd,Pickup,Dropoff,Fare,FareType, CASE(Booked) WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END As Booked From Fares Order By FareDate Desc, FareHash Desc
in sqlce a boolean is represented as a bit type. it's 1 for true, 0 for false.

All I needed to change was this in frmFareEdit

Code:
chkBooked.Checked = IIf(.Item("Booked") = "Yes", True, False)
where before it was a simple
Code:
chkBooked.Checked=.Item("Booked")
All works like a charm. Thanks for the help yet again.

regards, Ricky
 

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
True or False doesn't work

It has to be 1 or 0.

In sql boolean is represented by a bit type - 1 or 0.

If I put True or False in the statement it crashes. I have it working with the 1 or 0.

regards, Ricky
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
You didnt say that you also had to use the column value for a checkbox!

In that case, I would create a new DataGridCustomBooleanTextColumn (again, based on the textbox). Create additional properties such as "TextToDisplayWhenTrue" and "TextToDisplayWhenFalse" etc. and set them by default to YES and NO.
 

rickydalley

Member
Joined
Aug 28, 2009
Messages
24
Programming Experience
10+
sorry - i wasn't clear

The checkbox is one on the edit form - not on the grid.

The grid shows Yes/No then when I load the fields of the edit form I convert the Yes/No to True/False for the .Checked property of the checkbox with the simple iif statement.

It's working very well. Thanks for all the help.

regards, Ricky
 
Top Bottom