Looping using a datagridview in VB.NET

Question

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 27-Oct-22 12:19pm

NPetey

Updated 27-Oct-22 14:13pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Answer 1 · 2022-10-27T14:13:00

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'
    ...