Solved

Populate VB.net Checkbox list from SQL table

Posted on 2013-06-04
10
1,390 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

13 Experts available now in Live!

Get 1:1 Help Now