Solved

Populate VB.net Checkbox list from SQL table

Posted on 2013-06-04
10
1,404 Views
Last Modified: 2013-06-04
I have this SQL query:

SELECT PAYRCORD, DSCRIPTN FROM UPR40600 ORDER BY PAYRCORD

On my user form I want a checkbox list that will show:

Checkbox      PAYRCORD       DSCRIPTN

What is the code necessary to populate the checkbox list like this from the SQL table in the query?
0
Comment
Question by:thoecherl
  • 5
  • 5
10 Comments
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39220348
Is this windows forms or web as the approach is different as a checkboxlist is a web control, there is not a windows forms equivalent so we have to create our own user control.
0
 

Author Comment

by:thoecherl
ID: 39220380
Windows form
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39220393
OK, well as I said the checkboxlist is only for Web,

You can either use a set of standard check boxes that are added dynamically to a frame with the text of the combo box being made up from your data, or you can create a new user control that has a check box and a text box to allow you to nicely format the data into two apparent columns.

I'm presuming that you know how to create the query and loop through the records.

Which way do you want to go?
0
 

Author Comment

by:thoecherl
ID: 39220409
I'm a bit confused.  I'm using Visual Studio 2010 and I created the project as a vb.net Windows Form project.  CheckedListBox is one of the available forms, which I have dragged onto my form.

My preference is your second scenario, though I need three columns, one for the check box and one for each of the fields in the SQL query - PAYRCORD and DSCRIPTN.  I can make the SQL connection and execute the query, but do I use a datareader to loop through the results of the query or table fill, or what?
0
 

Author Comment

by:thoecherl
ID: 39220536
I've requested that this question be closed as follows:

Accepted answer: 0 points for thoecherl's comment #a39220409

for the following reason:

I got it to work using this:

        Dim str As String = "Data Source = T-PC;Initial Catalog = TWO;Persist Security Info= True;User ID=sa;Password=123!"
        Dim con As New SqlConnection(str)
        Dim cmd As String = "SELECT RTRIM(PAYRCORD) AS PAYCODE, RTRIM(DSCRIPTN) AS DESCRIPTION FROM UPR40600 ORDER BY PAYRCORD"
        Dim adpt As New SqlDataAdapter(cmd, con)
        Dim myDataSet As New DataSet()
        adpt.Fill(myDataSet, "UPR40600")
        Dim myDataTable As DataTable = myDataSet.Tables(0)
        Dim tempRow As DataRow
        For Each tempRow In myDataTable.Rows
            'ListBox1.Items.Add((tempRow("ControlNo") & " (" & tempRow("EmpNo") & ")" & " (" & tempRow("CheckOutDate") & ")" & " (" & tempRow("CheckOutTime") & ")" & " (" & tempRow("TaxiNo") & ")" & " (" & tempRow("PlateNo") & ")" & " (" & tempRow("Model") & ")" & " (" & tempRow("Make") & ")"))
            'ListBox1.Items.Add((tempRow("ControlNo") & " (" & tempRow("EmpNo") & ")"))
            chkPayCodes.Items.Add(tempRow("PAYCODE") & "            " & tempRow("DESCRIPTION"))
        Next

Thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 15

Expert Comment

by:ChloesDad
ID: 39220438
I was going to use the text part of the check box for one column, then a text box for the other.

I see the confusion now, There are two controls, checkedlistbox (WF) and CheckBoxList (Web)

I misread your original post and searched for the wrong one.....

Can you post your code for executing the query and then I can tell you what to do next
0
 

Author Comment

by:thoecherl
ID: 39220443
I accepted my comment as the solution, rather than the contributor's solution, by mistake.  I would like to fix that
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39220444
Glad you got it working and again sorry for the confusion over the control names
0
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
ID: 39220460
In this case, I see no problem in doing what you did. I didn't actually give you a solution, although I may have helped you in finding it yourself
0
 

Author Closing Comment

by:thoecherl
ID: 39220537
You sent me in the right direction.  Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now