Click here to Skip to main content
15,882,152 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I am using VB.Net Windows form with a Datagridview and a retrieve button. The form is used by end users who paste job number numbers in column 0, then click the retrieve button and the data from the database will display in the grid. The code I have is pulling the first job in column 0 on a new line (which should be on the same row as the job) but not the others. Can you help with creating a loop though column 0 if there is an input value?

What I have tried:

Dim s As Integer

     s = 0
     Dim str As String

     Dim strRelease = gridUserEntries.Rows(s).Cells(0).Value
     str = "Server=TEST01;Database=TEST01;User Id=TEST01;Password=TEST01t"

     Dim sqlcon As New SqlConnection(str)

     Dim SqlCmd As New SqlCommand

     SqlCmd.CommandText = "Select  U.SearchKey, pj.LOCATION_CODE,pj.PRODUCT_LINE,pj.PAINT_CODE ,pj.DATE_PROMISED,(select AlphaValue From JobscopeMeyer.dbo.UserDefinedFields Where SearchKey in ('" + strRelease + "'+ '    001') And DataItem ='WORK ORDER' Group by AlphaValue) as Work_Order,sj.BILL_CODE as 'Bill Code',sj.WIP_CODE as 'WIP Code', Max(case when U.dataitem = '12INTISSUE' then U.AlphaValue else '' end) as '12INTISSUE', Max(case when U.dataitem = '13PRT STAT' then U.AlphaValue else '' end) as '13PRT_STAT', Max(Case When U.dataitem = '14E/D/C/C' then U.AlphaValue else '' end) as '14E_D_C_C', Max(case when U.DataItem = '15RCP CMP' then U.AlphaValue else '' end) as '15RCP_CMP', Max(case WHEN U.DataItem = '16EST COMP' THEN U.AlphaValue else '' END) AS '16EST_COMP', MAX(CASE WHEN U.DataItem = '17AP CK SK' then U.AlphaValue else '' end) AS '17AP CK SK', MAX(CASE WHEN U.DataItem = '18AP CK CM' then U.AlphaValue else '' end) AS '18AP CK CM', MAX(CASE WHEN U.DataItem = '19AP OUTSK' then U.AlphaValue else '' end) AS '19AP OUTSK', MAX(CASE WHEN U.DataItem = '20AP OUTCM' then U.AlphaValue else '' end) AS '20AP OUTCM', MAX(CASE WHEN U.DataItem = '21AP RTNSK' then U.AlphaValue else '' end) AS '21AP RTNSK', MAX(CASE WHEN U.DataItem = '22AP RTNCM' then U.AlphaValue else '' end) AS '22AP RTNCM', MAX(CASE WHEN U.DataItem = '23FB CHKSK' then U.AlphaValue else '' end) AS '23FB CHKSK', MAX(CASE WHEN U.DataItem = '24FB CHKCM' then U.AlphaValue else '' end) AS '24FB CHKCM', MAX(CASE WHEN U.DataItem = '25FB SCH' then U.AlphaValue else '' end) AS '25FB SCH',MAX(CASE WHEN U.DataItem = '26FB CMP' then U.AlphaValue else '' end) AS '26FB CMP', MAX(CASE WHEN U.DataItem = '59FOO CMP'then U.AlphaValue else '' end) AS '59FOO CMP' From UserDefinedFields U Left Join( Select LOCATION_CODE , paint_code, PRODUCT_LINE, DATE_PROMISED, RELEASE As RELEASE_PJ, MAX(DATE_REQUESTED) As DATE_REQ From JobscopeMeyer.dbo.PPJOBD Where DATE_REQUESTED Not In (0, 99999999) Group BY RELEASE, LOCATION_CODE, paint_code, PRODUCT_LINE, DATE_PROMISED ) PJ On RELEASE_PJ = SearchKey Left Join( Select BILL_CODE, WIP_CODE, JOB_NUMBER From JobscopeMeyer.dbo.IPJOBM  Where JOB_NUMBER In ('" + strRelease + "')) sj On U.SearchKey = sj.JOB_NUMBER where U.SearchKey  In ('" + strRelease + "') And U.dataitem in ('12INTISSUE','13PRT STAT','14E/D/C/C','15RCP CMP','16EST COMP','17AP CK SK','18AP CK CM','19AP OUTSK','20AP OUTCM','21AP RTNSK','22AP RTNCM','23FB CHKSK','24FB CHKCM','25FB SCH','26FB CMP','27 PRODWK','28 SHIPMON','30 %COMPL','31 TGTSHIP','34GI CMP','59FOO CMP','Work Order' ) Group by U.SearchKey, PJ.DATE_REQ, PJ.LOCATION_CODE, pj.paint_code, pj.PRODUCT_LINE, pj.DATE_PROMISED, sj.BILL_CODE, sj.WIP_CODE"

     SqlCmd.CommandType = CommandType.Text

     SqlCmd.Connection = sqlcon

     Dim DR As SqlDataReader

     sqlcon.Open()

     Dim i As Integer

     i = 0

     DR = SqlCmd.ExecuteReader

     While (DR.Read())
         ' DR.Item(1).ToString.Trim()
         gridUserEntries.Rows.Add(DR.GetString(0), DR.GetString(1), DR.GetString(2), DR.GetString(3), DR.GetString(4))
         'gridUserEntries.Rows.Insert(DR.GetValue(0), DR.GetString(1), DR.GetString(2), DR.GetString(3), DR.GetString(4))
         i = i + 1
         s = s + 1
     End While

     DR.Close()
 End Sub
Posted
Updated 27-Oct-22 14:13pm

1 solution

Are you sure that SQL statement is good and returning the proper rows? Somehow, I doubt it.

This is what it looks like when you properly format the SQL and capitalize all the keywords:
SQL
SELECT U.SearchKey
    , pj.LOCATION_CODE
    , pj.PRODUCT_LINE
    , pj.PAINT_CODE
    , pj.DATE_PROMISED
    , (SELECT AlphaValue
        FROM JobscopeMeyer.dbo.UserDefinedFields
        WHERE SearchKey IN
            ('" + strRelease + "'+ '    001')
            AND DataItem ='WORK ORDER'
            GROUP BY AlphaValue
      ) AS Work_Order
    , sj.BILL_CODE AS 'Bill Code'
    , sj.WIP_CODE AS 'WIP Code'
    , MAX(
        CASE WHEN U.dataitem = '12INTISSUE'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '12INTISSUE'
    , MAX(
        CASE WHEN U.dataitem = '13PRT STAT'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '13PRT_STAT'
    , MAX(
        CASE WHEN U.dataitem = '14E/D/C/C'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '14E_D_C_C'
    , MAX(
        CASE WHEN U.DataItem = '15RCP CMP'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '15RCP_CMP'
    , MAX(
        CASE WHEN U.DataItem = '16EST COMP'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '16EST_COMP'
    , MAX(
        CASE WHEN U.DataItem = '17AP CK SK'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '17AP CK SK'
    , MAX(
        CASE WHEN U.DataItem = '18AP CK CM'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '18AP CK CM'
    , MAX(
        CASE WHEN U.DataItem = '19AP OUTSK'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '19AP OUTSK'
    , MAX(
        CASE WHEN U.DataItem = '20AP OUTCM'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '20AP OUTCM'
    , MAX(
        CASE WHEN U.DataItem = '21AP RTNSK'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '21AP RTNSK'
    , MAX(
        CASE WHEN U.DataItem = '22AP RTNCM'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '22AP RTNCM'
    , MAX(
        CASE WHEN U.DataItem = '23FB CHKSK'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '23FB CHKSK'
    , MAX(
        CASE WHEN U.DataItem = '24FB CHKCM'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '24FB CHKCM'
    , MAX(
        CASE WHEN U.DataItem = '25FB SCH'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '25FB SCH'
    ,MAX(
        CASE WHEN U.DataItem = '26FB CMP'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '26FB CMP'
    , MAX(
        CASE WHEN U.DataItem = '59FOO CMP'
            THEN U.AlphaValue
        ELSE ''
        END
      ) AS '59FOO CMP'
FROM UserDefinedFields U
LEFT JOIN (
    SELECT LOCATION_CODE
        , paint_code
        , PRODUCT_LINE
        , DATE_PROMISED
        , RELEASE AS RELEASE_PJ
        , MAX(DATE_REQUESTED) AS DATE_REQ
    FROM JobscopeMeyer.dbo.PPJOBD
    WHERE DATE_REQUESTED NOT IN (0, 99999999)
    GROUP BY RELEASE, LOCATION_CODE, paint_code, PRODUCT_LINE, DATE_PROMISED
) PJ
ON RELEASE_PJ = SearchKey
LEFT JOIN (
    SELECT BILL_CODE
        , WIP_CODE
        , JOB_NUMBER
    FROM JobscopeMeyer.dbo.IPJOBM
    WHERE JOB_NUMBER IN
        ('" + strRelease + "')
) sj
ON U.SearchKey = sj.JOB_NUMBER
WHERE U.SearchKey IN
    ('" + strRelease + "')
AND U.dataitem IN
    ('12INTISSUE','13PRT STAT','14E/D/C/C','15RCP CMP','16EST COMP','17AP CK SK','18AP CK CM','19AP OUTSK','20AP OUTCM','21AP RTNSK','22AP RTNCM','23FB CHKSK','24FB CHKCM','25FB SCH','26FB CMP','27 PRODWK','28 SHIPMON','30 %COMPL','31 TGTSHIP','34GI CMP','59FOO CMP','Work Order')
GROUP BY U.SearchKey, PJ.DATE_REQ, PJ.LOCATION_CODE, pj.paint_code, pj.PRODUCT_LINE, pj.DATE_PROMISED, sj.BILL_CODE, sj.WIP_CODE"

The SQL looks written to compensate for bad table design and bad data.

But, I'll clue you in where I think the problem is going to be:
SQL
SELECT U.SearchKey
    , pj.LOCATION_CODE
    , pj.PRODUCT_LINE
    , pj.PAINT_CODE
    , pj.DATE_PROMISED
    , (SELECT AlphaValue
        FROM JobscopeMeyer.dbo.UserDefinedFields
        WHERE SearchKey IN
            ('" + strRelease + "'+ '    001')       <---- WHAT'S THIS?
            AND DataItem ='WORK ORDER'
            GROUP BY AlphaValue
      ) AS Work_Order
    , sj.BILL_CODE AS 'Bill Code'
    ...
 
Share this answer
 
Comments
NPetey 30-Oct-22 20:18pm    
@Dave Kreskowiak thanks for helping with improving the query. Do you know how I can loop this query to display the data in each row in the datagridview?
Dave Kreskowiak 30-Oct-22 22:16pm    
Why? That's the hard way of doing it. You can ditch the DataReader and use a DataAdapter instead to do the query, fill a DataTable object with it, then just do gridUserEntries.DataSource = myDataTable.
NPetey 2-Nov-22 5:32am    
@Dave Kreskowiak

Here is what I changed it to:

Dim sql As String =
"Select U.SearchKey as RELEASE, pj.LOCATION_CODE,pj.PRODUCT_LINE,pj.PAINT_CODE ,pj.DATE_PROMISED,
(select AlphaValue From JobscopeMeyer.dbo.UserDefinedFields Where SearchKey in ('42439V'+ ' 001')
And DataItem ='WORK ORDER'Group by AlphaValue) as Work_Order,sj.BILL_CODE as 'Bill Code',sj.WIP_CODE as 'WIP Code',
Max(case when U.dataitem = '12INTISSUE' then U.AlphaValue else '' end) as '12INTISSUE',
Max(case when U.dataitem = '13PRT STAT' then U.AlphaValue else '' end) as '13PRT_STAT',
Max(Case When U.dataitem = '14E/D/C/C' then U.AlphaValue else '' end) as '14E_D_C_C',
Max(case when U.DataItem = '15RCP CMP' then U.AlphaValue else '' end) as '15RCP_CMP',
Max(case WHEN U.DataItem = '16EST COMP' THEN U.AlphaValue else '' END) AS '16EST_COMP',
MAX(CASE WHEN U.DataItem = '17AP CK SK' then U.AlphaValue else '' end) AS '17AP CK SK',
MAX(CASE WHEN U.DataItem = '18AP CK CM' then U.AlphaValue else '' end) AS '18AP CK CM',
MAX(CASE WHEN U.DataItem = '19AP OUTSK' then U.AlphaValue else '' end) AS '19AP OUTSK',
MAX(CASE WHEN U.DataItem = '20AP OUTCM' then U.AlphaValue else '' end) AS '20AP OUTCM',
MAX(CASE WHEN U.DataItem = '21AP RTNSK' then U.AlphaValue else '' end) AS '21AP RTNSK',
MAX(CASE WHEN U.DataItem = '22AP RTNCM' then U.AlphaValue else '' end) AS '22AP RTNCM',
MAX(CASE WHEN U.DataItem = '23FB CHKSK' then U.AlphaValue else '' end) AS '23FB CHKSK',
MAX(CASE WHEN U.DataItem = '24FB CHKCM' then U.AlphaValue else '' end) AS '24FB CHKCM',
MAX(CASE WHEN U.DataItem = '25FB SCH' then U.AlphaValue else '' end) AS '25FB SCH',
MAX(CASE WHEN U.DataItem = '26FB CMP' then U.AlphaValue else '' end) AS '26FB CMP',
MAX(CASE WHEN U.DataItem = '59FOO CMP'then U.AlphaValue else '' end) AS '59FOO CMP'
From UserDefinedFields U Left Join( Select LOCATION_CODE , paint_code, PRODUCT_LINE, DATE_PROMISED, RELEASE As RELEASE_PJ,
MAX(DATE_REQUESTED) As DATE_REQ From JobscopeMeyer.dbo.PPJOBD Where DATE_REQUESTED Not In (0, 99999999)
Group BY RELEASE, LOCATION_CODE, paint_code, PRODUCT_LINE, DATE_PROMISED ) PJ On RELEASE_PJ = SearchKey
Left Join( Select BILL_CODE, WIP_CODE, JOB_NUMBER From JobscopeMeyer.dbo.IPJOBM Where JOB_NUMBER In ('42439V')) sj On U.SearchKey = sj.JOB_NUMBER where U.SearchKey In ('42439V') And U.dataitem in ('12INTISSUE','13PRT STAT','14E/D/C/C','15RCP CMP','16EST COMP','17AP CK SK','18AP CK CM','19AP OUTSK','20AP OUTCM','21AP RTNSK','22AP RTNCM','23FB CHKSK','24FB CHKCM','25FB SCH','26FB CMP','27 PRODWK','28 SHIPMON','30 %COMPL','31 TGTSHIP','34GI CMP','59FOO CMP','Work Order' ) Group by U.SearchKey, PJ.DATE_REQ, PJ.LOCATION_CODE, pj.paint_code, pj.PRODUCT_LINE, pj.DATE_PROMISED, sj.BILL_CODE, sj.WIP_CODE "
Dim connection As New SqlConnection(connectionString)
Dim dataadapter As New SqlDataAdapter(sql, connection)
Dim ds As New DataSet()
connection.Open()
dataadapter.Fill(ds)
connection.Close()
gridUserEntries.DataSource = ds


This doesn't display any data in the grid.
Dave Kreskowiak 2-Nov-22 8:02am    
Change DataSet to DataTable. DataSets are for a collection of multiple tables.

Then set a breakpoint on the connection.Close line and run the code again. Make sure the query is actually returning what you think it should be returning.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900