Need advice on datagrid row backcolour/SQL

[RocAFella2007]

Hi, I require help with an SQL statement please. I have information showing in a datagrid, the sql for that is pretty simple as it is:

"Select * from Orders", conn

Now, there is a column in the datagrid which is called preorders, now what I need help with is where the preorder column = ‘Yes’ I want that row to be another colour, what is the best way to do this? Im sure the following is something close to it, however wouldn’t this only show the information where the column is ‘Yes’? I want to show all the information in the datagrid, like the SQL statement above. But ONLY where it says ‘Yes’ in the column I would prefer if that row was another colour.

"Select * from Orders where preorder = ‘Yes’", conn

Thanks.

 

[SiriusBlackOp]

You can't do this with just SQL. You will need to loop throught the rows of the datagrid testing for Yes and then set the backcolor manualy.

Senior Software Developer

http://www.scoutsft.com

 

[RocAFella2007]

could you give me any helpers or suggestions on how I would do this, I know youve mentioned a loop but how would I get it to work with the datagrid?

 

[SiriusBlackOp]

Are you using VS-2003 or VS-2005?

What you are trying to do may not be possible in VS-2003, But if you use a DataGridView in VS-2005 you should be able to color each cell in a given row.

If you are using 2005 with a DataGridView then I will work up an example for you.

Let me know.

Senior Software Developer

http://www.scoutsft.com

 

[RocAFella2007]

hmmm thats where im going to face a problem. You see im using Vb2003, im not using 2005.

 

[SiriusBlackOp]

Yep.... If it were an ASP.NET app in VB2003 then I could tell you how to do it using the ItemDataBound event... but the Window Application's Datagrid doesn't have that event.

If you need it... maybe there is a third party datagrid out there for VB2003 that could help you out.

Sorry I couldn't help more.

Senior Software Developer

http://www.scoutsft.com

 

[RocAFella2007]

No Problem, i just wont add that function in my program.

 

[Auguy]

I use many different colors for rows indicating a certain condition. I also use a different back color for the current row. Try adding a handler to each column of your grid and let the handler set the backgrouns colors. I'm sure the experts on this forum might have a better way. Good Luck!

Set up grid columns similar to:
[code/]
Dim Column As New FormattableTextBoxColumn

Column = New FormattableTextBoxColumn
Column.MappingName = "ProStatus"
Column.HeaderText = "Status"
Column.Width = 65
Column.TextBox.ContextMenu = New ContextMenu
Column.Alignment = HorizontalAlignment.Center
' Add handler
AddHandler Column.SetCellFormat, AddressOf FormatGridRow
ProFileStyle.GridColumnStyles.Add(Column)
[/code]

Set up handler:
[code/]
#Region "columnstyle event handlers"
Private Sub FormatGridRow(ByVal sender As Object, ByVal e As DataGridFormatCellEventArgs)
' Conditionally set properties in e depending upon e.Row and e.Col.
Dim cProStatus As String = Microsoft.VisualBasic.Left(CStr(IIf(e.Column <> 0, Me.grdProFile(e.Row, 0), e.CurrentCellValue)), 4) 'For performance reasons this probably should be changed to nested IF statements (I think)

'MessageBox.Show(cProStatus.ToString)
Select Case cProStatus
Case "COVE"
e.ForeBrush = Me.disabledTextBrush3
If e.Row = Me.grdProFile.CurrentRowIndex Then 'currentrow?
e.TextFont = Me.currentRowFont
e.BackBrush = Me.currentRowBackBrush
Else
e.BackBrush = Me.disabledBackBrush3
End If
Case "PRO "
e.ForeBrush = Me.disabledTextBrush4
If e.Row = Me.grdProFile.CurrentRowIndex Then 'currentrow?
e.TextFont = Me.currentRowFont
e.BackBrush = Me.currentRowBackBrush
Else
e.BackBrush = Me.disabledBackBrush4
End If
Case "TEMP"
e.ForeBrush = Me.disabledTextBrush1
If e.Row = Me.grdProFile.CurrentRowIndex Then 'currentrow?
e.TextFont = Me.currentRowFont
e.BackBrush = Me.currentRowBackBrush
Else
e.BackBrush = Me.disabledBackBrush5
End If
Case Else
If e.Row = Me.grdProFile.CurrentRowIndex Then 'currentrow?
e.BackBrush = Me.currentRowBackBrush
e.TextFont = Me.currentRowFont
End If
End Select
End Sub
#End Region
[/code]


Auguy
Sylvania, Ohio

 

[Auguy]

Forgot you will need this class. This isn't my code and i don't remember where I got it. Hope this helps

[/code]
Public Class FormattableTextBoxColumn
Inherits DataGridTextBoxColumn

Public Event SetCellFormat As FormatCellEventHandler

'used to fire an event to retrieve formatting info
'and then draw the cell with this formatting info
Protected Overloads Overrides Sub Paint(ByVal g As System.Drawing.Graphics, ByVal bounds As System.Drawing.Rectangle, ByVal [source] As System.Windows.Forms.CurrencyManager, ByVal rowNum As Integer, ByVal backBrush As System.Drawing.Brush, ByVal foreBrush As System.Drawing.Brush, ByVal alignToRight As Boolean)
Dim e As DataGridFormatCellEventArgs = Nothing

'fire the formatting event
Dim col As Integer = Me.DataGridTableStyle.GridColumnStyles.IndexOf(Me)
e = New DataGridFormatCellEventArgs(rowNum, col, Me.GetColumnValueAtRow([source], rowNum))
RaiseEvent SetCellFormat(Me, e)

Dim callBaseClass As Boolean = True ' assume we will call the baseclass

If Not (e.BackBrush Is Nothing) Then
backBrush = e.BackBrush
End If
If Not (e.ForeBrush Is Nothing) Then
foreBrush = e.ForeBrush
End If

'if TextFont set, then must call drawstring
If Not (e.TextFont Is Nothing) Then
g.FillRectangle(backBrush, bounds)
Try
Dim charWidth As Integer = CInt(Math.Ceiling(g.MeasureString("c", e.TextFont, 20, StringFormat.GenericTypographic).Width))
'Dim charWidth As Integer = Fix(Math.Ceiling(g.MeasureString("c", e.TextFont, 20, StringFormat.GenericTypographic).Width))

Dim s As String = Me.GetColumnValueAtRow([source], rowNum).ToString()
Dim maxChars As Integer = CInt(Math.Min(s.Length, bounds.Width / charWidth))

Try
g.DrawString(s.Substring(0, maxChars), e.TextFont, foreBrush, bounds.X, bounds.Y + 2)
Catch ex As Exception
Console.WriteLine(ex.Message.ToString())
End Try
Catch 'empty catch
End Try
callBaseClass = False
End If

If Not e.UseBaseClassDrawing Then
callBaseClass = False
End If
If callBaseClass Then
MyBase.Paint(g, bounds, [source], rowNum, backBrush, foreBrush, alignToRight)
End If
'clean up
If Not (e Is Nothing) Then
If e.BackBrushDispose Then
e.BackBrush.Dispose()
End If
If e.ForeBrushDispose Then
e.ForeBrush.Dispose()
End If
If e.TextFontDispose Then
e.TextFont.Dispose()
End If
End If
End Sub 'Paint
End Class
[/code]

Auguy
Sylvania, Ohio