Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Populate CheckedListBox with results of query

Posted on 2008-10-02
5
1,144 Views
Last Modified: 2012-05-05
Experts,

I perform a query that can return more than one row. It will look like this:

select fname, lname, city, job, letter, letterdate, letterbody from tblletter where name = 'joe' and letter like '%office%';

When this query returns more than one row i would like to pop up a small form for the user with a single checkedlistboxcontrol, and iterate through the records and populate that checkedlistbox with all of the letter, letterdate, letterbody values (that is, one row in the list box that includes all three values per record). Also, if only one row is returned from the query, then forget the whole exercise...

Also, can someone please tell me what the property is for the SqlDataReader that returns a row count?

partial code'''''
        Dim conReader2 As New SqlConnection(strConnect)
        Dim conString2 As New SqlCommand(FileSQL2, conReader2)
        conReader2.Open()

        Dim drReader2 As SqlDataReader
        drReader2 = conString2.ExecuteReader(CommandBehavior.CloseConnection)
        drReader2.Read()

TIA,
crafuse
0
Comment
Question by:crafuse
  • 3
  • 2
5 Comments
 
LVL 12

Assisted Solution

by:rionroc
rionroc earned 250 total points
ID: 22627496
Hello

Hope this help:


Dim drReader1 as SqlDataReader
Dim drReader2 as SqlDataReader

CheckedListBox1.Sorted = True

While drReader1.Read ''sql read name = 'joe' and letter like '%office%'
CheckedListBox1.Items.Add(drReader1.GetValue(0) & " , " & drReader1.GetValue(1), True)
End While
drReader1.Close()

While drReader2.Read ''sql read name <> 'joe' and letter like '%office%'
CheckedListBox1.Items.Add(drReader2.GetValue(0) & " , " & drReader2.GetValue(1), False)
End While
drReader2.Close()

Label1.Text = CheckedListBox1.Items.Count



:)
0
 

Author Comment

by:crafuse
ID: 22627748
Sorry, but I guess I wasn't very clear...

within the following code snippet (that is, where x > 1), I need to open a new form (form2) with a checked list box (lst1) and load the values into that checked list box. So...where I have put in my comment line, I need this even to happen...


If x > 1 Then

            FileSQL3 = "Select qdocall.doctyp, qdocall.datdue, qdocall.actdes from " & _
            "(qclient inner join qmatter on qclient.clinum = qmatter.clinum) left outer join qdocall on qmatter.clinum = qdocall.clinum and qmatter.matnum = qdocall.matnum " & _
            "where qclient.clinum = " & Me.txtFileNo.Text & " and qmatter.matnum = '" & Me.txtMatterNum.Text & "' and qdocall.doctyp like '%office letter%';"

            Dim conReader3 As New SqlConnection(strConnect)
            Dim conString3 As New SqlCommand(FileSQL3, conReader3)
            conReader3.Open()

            Dim drReader3 As SqlDataReader
            drReader3 = conString3.ExecuteReader(CommandBehavior.CloseConnection)
            drReader3.Read()

            'put in code to launch little form with checkedlistbox and populate that listbox with returned items.
            'after user selects one of the items in the list, throw that value back here

        End If
0
 
LVL 12

Expert Comment

by:rionroc
ID: 22631832
Hello

Can you just attach the file in zip format and I can assure to help you debug it.
It's hard to know the things that you can't completely see.


Great is our GOD.
:)
0
 

Accepted Solution

by:
crafuse earned 0 total points
ID: 22671500
Solved this myself -

Dim x As Integer
        Do While drReader.Read()
            x += 1
            If x = 1 Then
                strClinum = drReader.Item("clinum").ToString
                stringClname = drReader.Item("clname").ToString
                strFirmco = drReader.Item("firmco").ToString
                strMatnum = drReader.Item("matnum").ToString
                stringClAttn = drReader.Item("clattn").ToString
                stringApplno = drReader.Item("applno").ToString
                stringPreown = drReader.Item("preown").ToString
                stringMatattn = drReader.Item("matattn").ToString
                stringAppntn = drReader.Item("appntn").ToString
                stringTitinv = drReader.Item("titinv").ToString
                stringFirinv = drReader.Item("firinv").ToString
                stringAddress = drReader.Item("claddr").ToString
                stringCliref = drReader.Item("cliref").ToString
                stringDueDate = drReader.Item("duedate").ToString
            End If
        Loop

        'this counts the number of records returned. if more than one, we need to offer the user the option of selecting the correct letter\due date
        If x > 1 Then

            stringDueDate = ""
            Dim frmLetter As New frmLetter
            strThrowClinum = Me.txtFileNo.Text
            strThrowMatnum = Me.txtMatterNum.Text

            frmLetter.strnewclinum = strThrowClinum
            frmLetter.strnewmatnum = strThrowMatnum

            'Bring Form up
            frmLetter.ShowDialog()
            stringDueDate = frmLetter.strDueDate
        End If

In the scenario where we pop up the second form, that's where i iterate through records and populate the checkedlistbox.

0
 
LVL 12

Expert Comment

by:rionroc
ID: 22701643
Hello

Thanks for the assisted points.

Now I know what you really mean with that code you show.


Great is our GOD.
:)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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