Private Sub TabControl1_Changed(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged
Dim DGV As DataGridView = Nothing
Select Case TabControl1.SelectedIndex
Case 0 ' User clicks on First Tab
Call ProjTab()
DGV = DataGridView3
Case 1 ' User clicks on Second Tab
Call RefOpen()
DGV = DataGridView1
Case 2 ' User clicks on Third Tab
Call RefClosed()
DGV = DataGridView2
End Select
Call ColorDGV(DGV)
End Sub
Private Sub ColorDGV(DGV As DataGridView)
If DGV.IsHandleCreated Then
If DGV Is DataGridView2 And Me.AllDatesCheckBox_Closed.Checked = True Then
Exit Sub
End If
Dim col As DataGridViewColumn = DGV.Columns("QRev")
col.DefaultCellStyle.BackColor = Color.LightGray
col = DGV.Columns("QuoteNo")
col.DefaultCellStyle.BackColor = Color.LightGray
col = DGV.Columns("PType")
col.DefaultCellStyle.Font = New Font(DGV.Font, FontStyle.Italic)
Me.ProgressBar1.Visible = True
Dim i As Integer = DGV.Rows.Count
If i > 0 Then
ProgressBar1.Maximum = i
Dim c As Integer = 0
For Each row As DataGridViewRow In DGV.Rows
'Read a record from the database table
Dim sqCon As New SqlClient.SqlConnection(MAINDB)
Dim sqCmd As New SqlClient.SqlCommand
Dim QuoteNo As String = row.Cells.Item("QuoteNo").Value
Dim dbDORate As Boolean = False
Dim dbDXRate As Boolean = False
Dim SageSoReq As Boolean = False
Dim ReleaseModBol As Boolean = False
sqCmd.Connection = sqCon 'create the DB connection
sqCon.Open() 'open the connection
sqCmd.CommandText = "SELECT DORate, DXRate, SageSoReq, ReleaseMod FROM QuoteView WHERE QuoteNo = '" & QuoteNo & "'"
Dim sqReader As SqlDataReader = sqCmd.ExecuteReader() 'execute the SQL command
If sqReader.HasRows Then
While sqReader.Read()
If Not IsDBNull(sqReader.Item("DORate")) Then
dbDORate = sqReader.Item("DORate")
End If
If Not IsDBNull(sqReader.Item("DXRate")) Then
dbDXRate = sqReader.Item("DXRate")
End If
If Not IsDBNull(sqReader.Item("SageSoReq")) Then
SageSoReq = sqReader.Item("SageSoReq")
End If
If Not IsDBNull(sqReader.Item("ReleaseMod")) Then
ReleaseModBol = sqReader.Item("ReleaseMod")
End If
End While
End If
sqReader.Close() 'always close the reader when you're done with it.
sqCon.Close()
If dbDORate = True Then
row.Cells("DueDate").Style.BackColor = Color.DarkOliveGreen
row.Cells("DueDate").Style.ForeColor = Color.White
If DGV Is DataGridView1 Then
Me.DOLbl1.Visible = True
End If
End If
If dbDXRate = True Then
row.Cells("DueDate").Style.BackColor = Color.Navy
row.Cells("DueDate").Style.ForeColor = Color.White
If DGV Is DataGridView1 Then
Me.DXLbl1.Visible = True
End If
End If
If SageSoReq = True Then
row.Cells("CustName").Style.BackColor = Color.LightGray
row.Cells("PartNum").Style.BackColor = Color.LightGray
End If
If DGV Is DataGridView3 Then
Dim JobID As String = QuoteNo & row.Cells("PType").Value
If SageSOBol(JobID) = True Then
row.Cells("QuoteNo").Style.BackColor = Color.LightGreen
row.Cells("QRev").Style.BackColor = Color.LightGreen
row.Cells("CustName").Style.BackColor = Color.LightGreen
row.Cells("PartNum").Style.BackColor = Color.LightGreen
End If
ElseIf DGV Is DataGridView1 Then
If row.Cells.Item("DueDate").Value < Date.Today Then
row.Cells.Item("DueDate").Style.BackColor = Color.Red
End If
If row.Cells.Item("DueDate").Value > Date.Today Then
row.Cells.Item("DueDate").Style.BackColor = Color.Honeydew
End If
End If
If ReleaseModBol = True Then
row.Cells("QuoteNo").Style.BackColor = Color.Orange
row.Cells("QRev").Style.BackColor = Color.Orange
row.Cells("CustName").Style.BackColor = Color.Orange
row.Cells("PartNum").Style.BackColor = Color.Orange
End If
'Check for open tickets on this Work Order
Try
Dim TagNo As String = Nothing
Dim dbClosed As Boolean = True
TagNo = row.Cells.Item("QuoteNO").Value
sqCmd = sqCon.CreateCommand()
sqCmd.CommandText = "SELECT Closed FROM dbo.Tagged WHERE TagNo = '" & TagNo & "'"
sqCon.Open()
Dim RetVal As Object = sqCmd.ExecuteScalar()
If Not IsNothing(RetVal) And Not IsDBNull(RetVal) Then
dbClosed = RetVal 'Returns a single value
End If
sqCon.Close()
If dbClosed = False Then
row.Cells.Item("QuoteNO").ErrorText = "Open Ticket"
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
If ProgressBar1.Value <> ProgressBar1.Maximum Then
c = c + 1
End If
ProgressBar1.Value = c
Next
End If
End If
Me.ProgressBar1.Visible = False
End Sub