Results 1 to 6 of 6

Thread: [RESOLVED] Populating Labels From SQL Database

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    South Carolina
    Posts
    51

    Resolved [RESOLVED] Populating Labels From SQL Database

    I know it's got to be simple.
    I got 10 Labels and I want to fill them from a SQL database.

    Code:
                'Set Server
                SQLconn = New SqlConnection("server=NALCNTSB;User ID=badge; Password=badge;database=procreport")
                'Open Server
                SQLconn.Open()
    
                cmd = New SqlCommand("SELECT Top 10 qc_pdate FROM [procreport].[dbo].[qc_chem_record] where qc_pos = 'DIOXANE' Order By qc_pdate DESC", SQLconn)
                dr = cmd.ExecuteReader
    
                While dr.Read()
                lbldate1.text = dr("qc_pdate")
                lbldate2.text = dr("qc_pdate")
                lbldate3.text = dr("qc_pdate")

    All the way to 10 but they are only populated from the first reading.
    I have tried using for i but that doesn't work either

    Code:
                End While
                dr.Close()
                SQLconn.Close()

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Populating Labels From SQL Database

    You are never moving to the next record. Only reading the first one
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Populating Labels From SQL Database

    If you set every Label on every iteration then of course they will all show the last value. Put your Labels in an array and use a For loop. Each iteration, call Read and then get the appropriate Label from the array using the loop counter as the index. Only set that one Label on each iteration.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Populating Labels From SQL Database

    One possible way to do it:
    Code:
                ...
                dr = cmd.ExecuteReader
    
                Dim i as Integer
                Dim theLabels as Label() = {lbldate1, lbldate2, lbldate3, ... }
                Do While dr.Read() And i < 10
                    theLabels(i).text = dr("qc_pdate")
                    i +=1
                Loop
    
                dr.Close()
                ...
    (you may want to also have a loop to clear the labels, in case there are less than 10 values this time)

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    South Carolina
    Posts
    51

    Thumbs up Re: Populating Labels From SQL Database

    Quote Originally Posted by si_the_geek View Post
    One possible way to do it:
    Code:
                ...
                dr = cmd.ExecuteReader
    
                Dim i as Integer
                Dim theLabels as Label() = {lbldate1, lbldate2, lbldate3, ... }
                Do While dr.Read() And i < 10
                    theLabels(i).text = dr("qc_pdate")
                    i +=1
                Loop
    
                dr.Close()
                ...
    (you may want to also have a loop to clear the labels, in case there are less than 10 values this time)


    Thanks, I see what I was doing wrong now.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Populating Labels From SQL Database

    Quote Originally Posted by si_the_geek View Post
    One possible way to do it:
    Code:
                ...
                dr = cmd.ExecuteReader
    
                Dim i as Integer
                Dim theLabels as Label() = {lbldate1, lbldate2, lbldate3, ... }
                Do While dr.Read() And i < 10
                    theLabels(i).text = dr("qc_pdate")
                    i +=1
                Loop
    
                dr.Close()
                ...
    (you may want to also have a loop to clear the labels, in case there are less than 10 values this time)
    Given that the query guarantees that there cannot be more than 10 results, the test for the value of i is superfluous.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width