Doing something in VB.net Application and was wondering if their is a way to do it in Ignition. First I query a SQL table and save that to a Data Table. I then use a For Loop to use that tables row info to run another query and write the info row by row into a new table. Since I am not super familiar with scripting I am not sure it’s limitations or power. Below is a sample of my VB.net code. Point in the right direction or any help you can suggest. Thanks in advance, Keith
MyConnection.Open() 'Opens the SQL Connection
'Search Parameters
Dim SQLString As String
DTstation.Rows.Clear() 'Clears Previous Rows
DTstation.Columns.Clear() 'Clears Previous Columns
'SQL Query to collect station Info
Try 'Try catch error checking For SQL Server Connection
'SQL String Defining the Database Query
SQLString = "SELECT StationID FROM StationID WHERE ProductLine = '" & Vline & "' " & _
"AND Active = 'Yes' Order BY [StationOrder] ASC"
Dim Adapter As New OleDbDataAdapter(SQLString, MyConnection) 'Tells the Adapter How to get the Data
Adapter.Fill(DTstation) 'Fill the Data Table with the Query Data
Catch ex As Exception
'Exception Error Message that will Display
MsgBox("Station List Error " & _
ex.Message, MsgBoxStyle.Critical)
End Try
MyConnection.Close() 'Opens the SQL Connection
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
DTFinal.Rows.Clear() 'Clears Previous Rows
DTFinal.Columns.Clear() 'Clears Previous Columns
'Creates The Data Table for display
DTFinal.Columns.Add("Station Order", GetType(String))
DTFinal.Columns.Add("Workstation", GetType(String))
DTFinal.Columns.Add("Total Complete", GetType(String))
DTFinal.Columns.Add("Passed FPY", GetType(String))
DTFinal.Columns.Add("Failed FPY", GetType(String))
DTFinal.Columns.Add("AVG Time", GetType(String))
DTFinal.Columns.Add("FPY%", GetType(String))
MyConnection.Open() 'Opens the SQL Connection
For Each row As DataRow In DTstation.Rows 'This is the DT for the Machine Names
For Each Cell As String In row.ItemArray 'This Loops Through the Machine Names
'Query to return the machine Name Utilization, and Date for each Machine on that day
Try
Dim cmdstring As String = "SELECT " & _
"CycleTime.STOrder AS [Station Order], " & _
"CycleTime.STATION [Station], " & _
"SUM(CycleTime.FirstPass) + SUM(CycleTime.Failed)AS [Total Units], " & _
"SUM(CycleTime.FirstPass) AS [QTY Passed FPY], " & _
"SUM(CycleTime.Failed) as [QTY Failed FPY], " & _
"Cast(AVG(CycleTime)as decimal(10,1)) as [Avg. Time Minutes], " & _
"Cast(COALesce(Cast(SUM(CycleTime.FirstPass)AS Real) " & _
"/ NullIF(Cast(SUM(CycleTime.FirstPass) + SUM(CycleTime.Failed)AS ReAL),0),0) * " & _
"100 AS DECIMAL(10,3)) AS [FPY %] " & _
"FROM CycleTime " & _
"Where ProductionLine = '" & Vline & "' " & _
"AND STATION = '" & Cell & "' " & _
"AND STOP_TIME >= '" & Vtime & "' " & _
"AND STOP_TIME <= '" & Vtime1 & "' " & _
"AND Sequence = '1' " & _
"GROUP BY STATION, STOrder " & _
"Order BY [STOrder] ASC"
Dim cmd As New OleDbCommand(cmdstring, MyConnection)
cmd.CommandType = CommandType.Text
Dim reader As OleDbDataReader = cmd.ExecuteReader()
Dim SO As String 'Station Order
Dim WS As String 'WorkStation
Dim TU As String 'Total Complete
Dim PF As String 'Passed FPY
Dim FF As String 'Failed FPY
Dim ACT As String 'AVG Cycle Time
Dim FPY As String 'FPY
Dim cmd1 As New OleDbCommand("", MyConnection)
'Opens Reader
reader.Read()
'This Stores the results in a variable
If reader.HasRows = False Then
'Try catch error checking For SQL Server Connection
'Verifies Sequence From Table
Using MyConnection As New OleDbConnection(ConText)
MyConnection.Open() 'Opens SQL Connection
Dim Station As String 'Employee Name
'Selects Sequence Name
cmd1.CommandText = "SELECT StationOrder from StationID where StationID = '" & Cell & "' " & _
"AND ProductLine = '" & Vline & "'"
Station = IIf(IsDBNull(cmd1.ExecuteScalar), "", cmd1.ExecuteScalar)
SO = Station
MyConnection.Close()
End Using
WS = Cell
TU = "No Data Collected"
PF = "No Data Collected"
FF = "No Data Collected"
ACT = "No Data Collected"
FPY = "0"
DTFinal.Rows.Add(SO, WS, TU, PF, FF, ACT, FPY)
'Clears the values befor the next loop
SO = ""
WS = ""
TU = ""
PF = ""
FF = ""
ACT = ""
FPY = ""
Else
SO = reader("Station Order").ToString()
WS = Cell
TU = reader("Total Units").ToString()
PF = reader("QTY Passed FPY").ToString()
FF = reader("QTY Failed FPY").ToString()
ACT = reader("Avg. Time Minutes").ToString()
FPY = reader("FPY %").ToString()
DTFinal.Rows.Add(SO, WS, TU, PF, FF, ACT, FPY)
'Clears the values befor the next loop
SO = ""
WS = ""
TU = ""
PF = ""
FF = ""
ACT = ""
FPY = ""
End If
'Closes Reader
reader.Close()
Catch ex As Exception
MessageBox.Show("Main View Display Error. " & " , " & ex.Message, _
"Database Retrieval Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Next
Next
MyConnection.Close() 'Closes the SQL Connection