Is it possible to populate a Farpoint Spread 6.0 vaSpread component using a SQL query in VB6?

Question

I have written a query using T-SQL on SQL Server 2008 R2 that provides the correct information that I need to display on a vaSpread component named SSlist on Visual Basic 6. I have already opened the connection to the database, but I am having difficulty finding resources on how to populate the vaSpread component directly using my T-SQL query. I just need to display it exactly as how it shows up when I execute it in Microsoft SQL Server Management Studio.

My query is:

SELECT QC.LINE_CD AS 'Line Code', QC.LINE_NM AS 'Line Name', PN.GUBUN, WO.WRK_QTY AS 'Work QTY', CM.LINE_TARGET AS 'Line Target',
    CM.RETURN_TARGET AS 'Return Target', SUM(PN.R_QTY) AS 'Rework QTY', SUM(PN.S_QTY) AS 'Scrap QTY',
    SUM(PN.UPRC_AMT) AS 'UPRC AMT', (SUM(COALESCE(PN.UPRC_AMT,0)*PN.S_QTY)+SUM(PN.R_QTY)*3.8) AS 'Cost'
FROM QC_LINE_MST AS QC

LEFT JOIN (SELECT PE.LINE_CD, PE.WRK_YMD, PE.CUST_CD, PE.GUBUN, PE.ITMNO, PE.R_QTY, PE.S_QTY, ND.UPRC_AMT FROM PROC_ERR AS PE
            INNER JOIN (SELECT ITMNO, CUST_CD, UPRC_AMT FROM NOW_DANGA) AS ND ON PE.ITMNO = ND.ITMNO AND PE.CUST_CD = ND.CUST_CD 
            WHERE PE.WRK_YMD BETWEEN '20161116' AND '20161201' AND (PE.R_QTY <> 0 OR PE.S_QTY <> 0) 
            ) AS PN ON QC.LINE_CD = PN.LINE_CD

LEFT JOIN (SELECT A.CODE, A.DSCP AS LINE_TARGET, B.DSCP AS RETURN_TARGET FROM COD_MST AS A
            INNER JOIN (SELECT CODE, DSCP FROM COD_MST WHERE GUBN='QC09'
            ) AS B ON A.CODE = B.CODE
            WHERE A.GUBN='QC08') CM ON QC.LINE_CD = CM.CODE

LEFT JOIN (SELECT LINE_CD, SUM(WRK_QTY) AS WRK_QTY FROM WRK_ORD
            WHERE WRK_YMD BETWEEN '20161116' AND '20161201' GROUP BY LINE_CD
            ) AS WO ON QC.LINE_CD = WO.LINE_CD

GROUP BY QC.LINE_CD, QC.LINE_NM, WO.WRK_QTY, PN.GUBUN, CM.LINE_TARGET, CM.RETURN_TARGET
ORDER BY QC.LINE_CD

I've searched online trying to figure out how to populate my vaSpread using this query, but either I am looking in the wrong place, or resources on Farpoint Spread 6.0 are scarce. If anyone has any ideas on how to implement this, or could direct me towards some helpful literature it would be much appreciated. Also, if anyone has any ideas on how to clean up my SQL query and make it more efficient, that's welcome as well. I'm pretty new to this. Thank you, and let me know if I need to provide any more information! I look forward to reading your suggestions.

Answer 1

After doing some more research, I learned that instead of using the FarPoint 6.0 vaSpread component (non-OLEDB), the FarPoint 6.0 FpSpread component (OLEDB capable) should be used in order to automatically populate the spread sheet. However, the method to automatically populate the new FpSpread component required:

1) an ADODC component connected to the database

2) a stored procedure on the database

Seeing as how I already had an active connection and also needed to use certain column records in calculations to populate other columns, I decided to go with a manual spreadsheet population method using FOR loops. My code is attached below so that anyone having any similar issues can use my code for ideas.

 With SSlist

    //SQL Query to USA_ERP.QC_LINE_MST Table to receive total number of Rows in Record Set 
    SqlStmt = CSQL("SELECT COUNT(*) AS 'Count' FROM QC_LINE_MST")
    Rs.Open SqlStmt, CN, adOpenForwardOnly, adLockReadOnly
    LastRow = Val(Rs.Fields("Count"))
    RowB4Last = Val(Rs.Fields("Count")) - 1
    .MaxRows = LastRow
    Rs.Close

    //Formatting for Last Row (Totals row)
    For RowCount = 1 To LastRow
        .Row = RowCount
        .RowHeight(.Row) = 18
        //Font and cell formatting for Line Columns
        For ColCount = 1 To 1
            .Col = ColCount
            .CellType = CellTypeStaticText
            .TypeHAlign = TypeHAlignCenter
            .FontBold = True
            .TypeVAlign = TypeVAlignCenter
        Next
        If .Row = LastRow Then
            //Merge for Totals label of Last Row (Totals row)
            For ColCount = 1 To 2
               .Col = ColCount
               .Text = "Totals"
               .RowMerge = MergeRestricted
            Next
            //Font and cell formatting for Last Row (Totals row)
            For ColCount = 1 To 15
              .Col = ColCount
              .CellType = CellTypeStaticText
              .TypeHAlign = TypeHAlignCenter
              .FontBold = True
              .TypeVAlign = TypeVAlignCenter
            Next
       End If
    Next

    //Main SQL Query to USA_ERP Database
    SqlStmt = CSQL("SELECT QC.LINE_CD AS 'Line Code', QC.LINE_NM AS 'Line Name', PN.GUBUN, WO.WRK_QTY AS 'Work QTY', CM.LINE_TARGET AS 'Line Target', " & _
                            "CM.RETURN_TARGET AS 'Return Target', SUM(PN.R_QTY) AS 'Rework QTY', SUM(PN.S_QTY) AS 'Scrap QTY', " & _
                            "SUM(PN.UPRC_AMT) AS 'UPRC AMT', (SUM(COALESCE(PN.UPRC_AMT,0)*PN.S_QTY)+SUM(PN.R_QTY)*3.8) AS 'Cost' " & _
                    "FROM QC_LINE_MST AS QC " & _
                        "LEFT JOIN (SELECT PE.LINE_CD, PE.WRK_YMD, PE.CUST_CD, PE.GUBUN, PE.ITMNO, PE.R_QTY, PE.S_QTY, ND.UPRC_AMT FROM PROC_ERR AS PE " & _
                                    "INNER JOIN (SELECT ITMNO, CUST_CD, UPRC_AMT FROM NOW_DANGA) AS ND ON PE.ITMNO = ND.ITMNO AND PE.CUST_CD = ND.CUST_CD  " & _
                                    "WHERE PE.WRK_YMD BETWEEN '$S' AND '$S' AND (PE.R_QTY <> 0 OR PE.S_QTY <> 0) " & _
                                    ") AS PN ON QC.LINE_CD = PN.LINE_CD " & _
                        "LEFT JOIN (SELECT A.CODE, A.DSCP AS LINE_TARGET, B.DSCP AS RETURN_TARGET FROM COD_MST AS A " & _
                                    "INNER JOIN (SELECT CODE, DSCP FROM COD_MST WHERE GUBN='QC09' " & _
                                    ") AS B ON A.CODE = B.CODE " & _
                                    "WHERE A.GUBN='QC08') CM ON QC.LINE_CD = CM.CODE " & _
                        "LEFT JOIN (SELECT LINE_CD, SUM(WRK_QTY) AS WRK_QTY FROM WRK_ORD " & _
                                    "WHERE WRK_YMD BETWEEN '$S' AND '$S' GROUP BY LINE_CD " & _
                                    ") AS WO ON QC.LINE_CD = WO.LINE_CD " & _
                    "GROUP BY QC.LINE_CD, QC.LINE_NM, WO.WRK_QTY, PN.GUBUN, CM.LINE_TARGET, CM.RETURN_TARGET " & _
                    "ORDER BY QC.LINE_CD " _
                    , Format(DTPDate(0).Value, "YYYYMMDD"), Format(DTPDate(1).Value, "YYYYMMDD"), Format(DTPDate(0).Value, "YYYYMMDD"), Format(DTPDate(1).Value, "YYYYMMDD"))

    Rs.Open SqlStmt, CN, adOpenForwardOnly, adLockReadOnly

    While Not Rs.EOF

        //Start at First Row for First Record from RecordSet (Rs), loop through all Records from RecordSet (Rs)
        For RowCount = 1 To LastRow
            .Row = RowCount
            //Initialize/Re-initialize calculation variables for every Record
            LineScrap = 0
            CustomerScrap = 0
            ResidentScrap = 0
            ReworkQTY = 0
            FailCost = 0

            //Check to see if LastRow (Totals Row)
            If .Row = LastRow Then

                //If LastRow, populate columns with Total values
                For ColCount = 1 To 15
                .Col = ColCount
                    If .Col = 1 Then

                    ElseIf .Col = 2 Then
                        .ColMerge = MergeRestricted
                    ElseIf .Col = 3 Then
                        .Text = TotalProduction
                    ElseIf .Col = 4 Then
                        .Text = Val(Rs.Fields("Line Target"))
                    ElseIf .Col = 5 Then
                        .Text = TotalRework
                    ElseIf .Col = 6 Then
                        .Text = TotalScrap
                    ElseIf .Col = 7 Then
                        .Text = TotalReworkPPM
                    ElseIf .Col = 8 Then
                        .Text = TotalScrapPPM
                    ElseIf .Col = 9 Then
                        .Text = TotalFailCosts
                    ElseIf .Col = 10 Then
                        .Text = Val(Rs.Fields("Return Target"))
                    ElseIf .Col = 11 Then
                        .Text = TotalCustReturn
                    ElseIf .Col = 12 Then
                        .Text = TotalOnSiteReturn
                    ElseIf .Col = 13 Then
                        .Text = TotalCustReturnPPM
                    ElseIf .Col = 14 Then
                        .Text = TotalOnSiteReturnPPM
                    ElseIf .Col = 15 Then
                        .Text = TotalScrapPPM
                    Else
                    End If
                Next
                //Close database connection
                Rs.Close
                //Exit Subroutine logic
                Exit Sub

            End If

            //Choose the correct variable to store "Scrap QTY" value from RecordSet (Rs) based on "GUBUN" value of Record
            If IsNull(Rs.Fields("Scrap QTY")) = False Then
                If Trim(Rs.Fields("GUBUN")) = "Customer" Then
                    CustomerScrap = Val(Rs.Fields("Scrap QTY"))
                ElseIf Trim(Rs.Fields("GUBUN")) = "On Site" Then
                    ResidentScrap = Val(Rs.Fields("Scrap QTY"))
                ElseIf Trim(Rs.Fields("GUBUN")) = "MIP NG" Then
                    LineScrap = Val(Rs.Fields("Scrap QTY"))
                End If
            //If "Scrap QTY" is NULL then set correct variable to 0 based on "GUBUN" value of Record
            Else
                 If Trim(Rs.Fields("GUBUN")) = "Customer" Then
                    CustomerScrap = 0
                 ElseIf Trim(Rs.Fields("GUBUN")) = "On Site" Then
                    ResidentScrap = 0
                 Else
                    LineScrap = 0
                 End If
            End If

            //Store "Rework QTY" in correct variable
            //If "Rework QTY" is NULL, store 0
            If IsNull(Rs.Fields("Rework QTY")) = False Then
                ReworkQTY = Val(Rs.Fields("Rework QTY"))
            Else
                ReworkQTY = 0
            End If


            //Populate spread (SSList) with correct values using RecordSet (Rs) and calculated variables
            //Line Column
            .Col = 1
                .Text = Rs.Fields("Line Code")
            //Model Column
            .Col = 2
                .Text = Rs.Fields("Line Name")
            //Prod (EA) Column
            .Col = 3
                //If "Work QTY" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Work QTY")) = False Then
                    .Text = Trim(Val(Rs.Fields("Work QTY")) + LineScrap)
                Else
                    .Text = 0
                End If
                //Calculate running total for 'Prod (EA)' Column through all Records/loops
                TotalProduction = TotalProduction + Val(.Text)
            //In Line Target (PPM) Column
            .Col = 4
                //If "Line Target" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Line Target")) = False Then
                    .Text = Trim(Val(Rs.Fields("Line Target")))
                Else
                    .Text = 0
                End If
            //In Line Rework QTY Column
            .Col = 5
                //If "Rework QTY" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Rework QTY")) = False Then
                    .Text = ReworkQTY
                Else
                    .Text = 0
                End If
                //Calculate running total for 'In Line Rework QTY' Column through all Records/loops
                TotalRework = TotalRework + Val(.Text)
            //In Line Scrap QTY Column
            .Col = 6
                //Set cell value to LineScrap variable
                .Text = LineScrap
                //Calculate running total for 'In Line Scrap QTY' Column through all Records/loops
                TotalScrap = TotalScrap + Val(.Text)
            //In Line Rework PPM QTY Column
            .Col = 7
                //If "Work QTY" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Work QTY")) = False Then
                    .Text = Round(ReworkQTY / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 6)
                Else
                    .Text = 0
                End If
                //Calculate running total for 'In Line Rework PPM QTY' Column through all Records/loops
                TotalReworkPPM = TotalReworkPPM + Val(.Text)
            //In Line Scrap PPM QTY Column
            .Col = 8
                //If "Work QTY" is Null set cell value to 0
                If IsNull(Rs.Fields("Work QTY")) = False Then
                    .Text = Round(LineScrap / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 6)
                Else
                    .Text = 0
                End If
                //Calculate runing total for 'In Line Scrap PPM QTY' Column through all Records/loops
                TotalScrapPPM = TotalScrapPPM + Val(.Text)
            //In Line Fail Costs ($) Column
            .Col = 9
                //If "GUBUN" Record is "MIP NG" and "Cost" Record is Not Null set cell value to "Cost" Record
                //Otherwise, set cell value to 0
                If Trim(Rs.Fields("GUBUN")) = "MIP NG" Then
                    If IsNull(Trim(Rs.Fields("Cost"))) = False Then
                        .Text = Val(Rs.Fields("Cost"))
                    Else
                        .Text = 0
                    End If
                Else
                    .Text = 0
                End If
                //Calculate running total for 'In Line Fail Costs ($)' Column through all Records/loops
                TotalFailCosts = TotalFailCosts + Val(.Text)
            //Customer Return Target PPM QTY Column
            .Col = 10
                //If "Return Target" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Return Target")) = False Then
                    .Text = Trim(Val(Rs.Fields("Return Target")))
                Else
                    .Text = 0
                End If
            //Customer Return QTY Column
            .Col = 11
                //Set cell value to CustomerScrap variable
                .Text = CustomerScrap
                //Calculate running total for 'Customer Return QTY' Column through all Records/loops
                TotalCustReturn = TotalCustReturn + Val(.Text)
            //On Site Return QTY Column
            .Col = 12
                //Set cell value to ResidentScrap variable
                .Text = ResidentScrap
                //Calculate running total for 'On Site Return QTY' Column through all Records/loops
                TotalOnSiteReturn = TotalOnSiteReturn + Val(.Text)
            //Customer Return PPM QTY Column
            .Col = 13
                //If "Work QTY" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Work QTY")) = False Then
                    .Text = Round(CustomerScrap / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 2)
                Else
                    .Text = 0
                End If
                //Calculate running total for 'Customer Return PPM QTY' Column through all Records/loops
                TotalCustReturnPPM = TotalCustReturnPPM + Val(.Text)
            //On Site Return PPM QTY Column
            .Col = 14
                //If "Work QTY" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Work QTY")) = False Then
                    .Text = Round(ResidentScrap / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 2)
                Else
                    .Text = 0
                End If
                //Calculate running total for 'On Site Return PPM QTY' Column through all Records/loops
                TotalOnSiteReturnPPM = TotalOnSiteReturnPPM + Val(.Text)
            //Total Loss PPM Column
            .Col = 15
                //If "Work QTY" Record is Null set cell value to 0
                If IsNull(Rs.Fields("Work QTY")) = False Then
                    .Text = Round((CustomerScrap + LineScrap) / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 0)
                Else
                    .Text = 0
                End If
                //Calculate running total for 'Total Loss PPM' Column through all Records/loops
                TotalLossPPM = TotalLossPPM + Val(.Text)
            //Move to the next Record in RecordSet (Rs)
            Rs.MoveNext
        Next

    Wend
    End With

This code is run with an active connection to a database, CN, with a RecordSet, Rs. The FOR loop basically goes through every column of every row and populates each cell with the correct values needed based on the logic, moving to the next Record in the RecordSet after every row. The last row in my SQL query RecordSet is a totals row that has data only in certain columns. When reaching this last row, it populates the cells with either the calculated running totals or, when available, the values in the RecordSet. After populating the last row of the table, the subroutine ends.

I don't know if anyone has any interest in this problem, but hopefully this can help someone. This may not be the ideal or most efficient way of populating a FarPoint vaSpread component, but it works 100% of the time and depending on your SQL query you can make this future proof. In particular, I have my query set up so all of the joins occur on a single reference table (QC.LINE_MST) populated with line codes or "Line_CD"'s that I would like to see on the table. This enables me to just add new "Line_CD"'s to that reference table so that my query and thus my program will pick it up on the next inquiry. This logic also handles NULL values from the SQL table, setting all NULL values to 0 before any calculations are made or cells are populated. The only time that this logic needs to be updated is when you would like to add new information columns to the table, something that I personally won't need to do.

If anyone has any suggestions for the code, ways to make it more efficient or have cleaner formatting, please leave a comment below.