Dim coilpdi, allPLNcoils As String
Dim CoilPDIRowCount As Integer

Dim allcoils As New DataSet
Dim PDIDataset As New DataSet

Dim myConnection As New OracleConnection("Data Source=DUF_TCM; User ID=PCC;Password=DUFERCO")

myConnection.Open()

FileOpen(10, "C:\Documents and Settings\alogue\Desktop\Coil PDI\PDI.txt", OpenMode.Output)
PrintLine(10, txtgradecode.Text, txtwidth.Text, txthbgauge.Text, txttargetgauge.Text)
FileClose(10)

'Queries for the roll id and mate specified by the find textbox
allPLNcoils = "Select * from tcm_planned_coil where process_status ='PLN' order by schedule_number, sequence_number"
coilpdi = "Select * from tcm_planned_coil where process_status = 'PLN' and grade_code ='" + txtgradecode.Text + "' and width = '" + txtwidth.Text + "' and hot_band_gauge='" + txthbgauge.Text + "' and target_gauge='" + txttargetgauge.Text + "' order by schedule_number, sequence_number"

Dim PDICommand As New OracleCommand(coilpdi, myConnection)
Dim allPLNcoilsCommand As New OracleCommand(allPLNcoils, myConnection)

Dim PDIAdapter As New OracleDataAdapter(PDICommand)
Dim allPLNcoilsAdapter As New OracleDataAdapter(allPLNcoilsCommand)


'clears the dataset 
PDIDataset.Clear()
allcoils.Clear()

'using the command object, open a data adapter
PDIAdapter.SelectCommand = PDICommand
allPLNcoilsAdapter.SelectCommand = allPLNcoilsCommand

'Fills the data adapter with the dataset
allPLNcoilsAdapter.Fill(allcoils, "Table3")
PDIAdapter.Fill(PDIDataset, "Table2")

With Me.dgQueryResults
.DataSource = allcoils 'display the data in the datagrid
.NavigateTo(0, -1) 'expand the datagrid one level
.NavigateTo(0, "Table3") 'expand the datagrid down to the child level to show query results
.ParentRowsVisible = False
.AllowSorting = True
.AllowNavigation = True
.AllowDrop = False
.ColumnHeadersVisible = True
.PreferredColumnWidth = 200 'Set in pixels
.ParentRowsVisible = False
.TabStop = False
.Enabled = True
.Visible = True
.ReadOnly = True
.AlternatingBackColor = System.Drawing.Color.Bisque
End With

RowCount = allcoils.Tables("Table3").DefaultView.Count
CoilPDIRowCount = PDIDataset.Tables("Table2").Rows.Count
ColCount2 = allcoils.Tables("Table3").Columns.Count + 1

'****************************** Display Coil PDI in Hidden ListBox *************************
For f = 0 To CoilPDIRowCount - 1
For q = 0 To RowCount - 1
If dgQueryResults.Item(q, 0) = PDIDataset.Tables("Table2").Rows(f).Item("ID") Then
lstcoilpdi.Items.Add(q)
End If
Next
Next

' f = 0
'q = 0
'****************************** End Display CoilPDI in Hidden ListBox **********************

AddCellFormattingColumnStyles2(Me.dgQueryResults, New FormatCellEventHandler(AddressOf FormatGridCells)) ' This is a private Sub within this class.

PDIDataset.Dispose()
allcoils.Dispose()

myConnection.Close()

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information)
Finally

End Try
End Sub
Private Sub AddCellFormattingColumnStyles2(ByVal grid As DataGrid, ByVal handler As FormatCellEventHandler)
Dim w As Integer
Dim dgt2 As DataGridTableStyle
dgt2 = New DataGridTableStyle
dgt2.MappingName = "Table3"
Dim gridCol2 As DataGridFormattableTextBoxcolumn

w = 0
Do While (w < ColCount2 - 1)

gridCol2 = New DataGridFormattableTextBoxcolumn(w)
gridCol2.MappingName = allcoils.Tables("Table3").Columns (w).ColumnName
gridCol2.HeaderText = allcoils.Tables("Table3").Columns(w).ColumnName
AddHandler gridCol2.SetCellFormat, handler
dgt2.GridColumnStyles.Add(gridCol2)
w = (w + 1)

Loop

grid.TableStyles.Clear()
grid.TableStyles.Add(dgt2)
End Sub

Private Sub FormatGridCells(ByVal sender As Object, ByVal e As DataGridFormatCellEventArgs)
'This sub is responsible for changing the background color of rows in the DataGrid.

If e.Column = 5 Then '% shape tolerance column (called SHAPE_BAND1)
e.BackBrush = Brushes.LightYellow
End If

If e.Column = 10 Then '% gauge tolerance column (called PER_BODY_TOL)
e.BackBrush = Brushes.LightYellow
End If
'*************************** Format matching coil PDI ***************************
For a = 0 To lstcoilpdi.Items.Count - 1

If e.Row = lstcoilpdi.Items.Item(a) Then
e.BackBrush = Brushes.Black
e.ForeBrush = Brushes.White
End If

Next
'*************************** End Format matching coil PDI **************************
a = 0
End Sub