CODEWITHSUNDEEP
vb.netsql-server-2008vb.net-2010
M Ahsan Asif
M Ahsan Asif

Reputation: 37

How to change row color in datagridview by comparing two columns from different tables using vb.net?

No success!

enter image description here

If user of tblcon with billmonth of tbltrns exists in tbltrns then highlight the rows of tblcon with red color

Code:

 Private Sub checkexist()

For Each row As DataGridViewRow In DataGridView1.Rows

        ConObj = New SqlConnection(ConStr)
        ConObj.Open()
        Dim qry As String = "SELECT * FROM tblTrns WHERE userName=@userName and bill_month=@bill_month"
        CmdObj = New SqlCommand(qry, ConObj)
        CmdObj.Parameters.AddWithValue("@bill_month", DateTimePicker1.Text)
        CmdObj.Parameters.AddWithValue("@userName", (row.Cells("User").Value.ToString))

        drObj = CmdObj.ExecuteReader()

    If drObj.HasRows Then
        row.DefaultCellStyle.BackColor = Color.Red

    End If
    Next

   ConObj.Close()
End Sub

Query for the Grid

 Public Function GetData() As DataView

    ConObj = New SqlConnection(ConStr)
    ConObj.Open()
    CmdObj = New SqlCommand
    dsObj = New DataSet
    daObj = New SqlDataAdapter()

    Dim SelectQry = "SELECT UserName[User],doj[Connection Date],packagename[Package],profilename[Profile],billing[Payment],fees[Fees],connectionstatus[Status] from TblCon"

    CmdObj.CommandText = SelectQry
    CmdObj.Connection = ConObj
    daObj.SelectCommand = CmdObj
    daObj.Fill(dsObj)
    TvObj = dsObj.Tables(0).DefaultView

    Return TvObj
    ConObj.Close()

End Function

Query Changed in getdata but no success. Please guide ...........................................................................................................................................................................................................................................

Public Function GetData() As DataView

    ConObj = New SqlConnection(ConStr)
    ConObj.Open()
    CmdObj = New SqlCommand
    dsObj = New DataSet
    daObj = New SqlDataAdapter()

    Dim SelectQry = Dim SelectQry = "SELECT UserName[User],doj[Connection Date],packagename[Package],profilename[Profile],billing[Payment],fees[Fees],conn‌​‌​‌​ectionstatus[Status], (SELECT ISNULL(COUNT(*), 0) FROM tblTrns WHERE tblTrns.userName = tblCon.UserName AND bill_month = '" & DateTimePicker1.Text & "') [Comparison] from TblCon"

    CmdObj.CommandText = SelectQry
    CmdObj.Connection = ConObj
    daObj.SelectCommand = CmdObj
    daObj.Fill(dsObj)
    TvObj = dsObj.Tables(0).DefaultView

    Return TvObj
    ConObj.Close()

End Function

Upvotes: 0

Views: 880

Answers (1)

Abner
Abner

Reputation: 426

I recommend you to fill a new column in your grid with a value for comparison and set it's visibility to false. You can do that by a subselect or a left join on the second table (if you wish an example just post the query that fills your grid).

Based on your comparison column you can use the CellPainting event of the GridView. Like this:

Private Sub grdGridView_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles grdGridView.CellPainting
    If e.RowIndex >= 0 Then

        If grdGridView.Rows(e.RowIndex).Cells("colComparison").Value <> "0" And Not IsNothing(grdGridView.Rows(e.RowIndex).Cells("colComparison").Value) Then
            e.CellStyle.BackColor = Color.OrangeRed
            e.CellStyle.SelectionBackColor = Color.IndianRed
        End If

    End If
End Sub

Upvotes: 1

Related Questions