Solved

Populate CheckedListBox with results of query

Posted on 2008-10-02
5
1,138 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

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

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 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

20 Experts available now in Live!

Get 1:1 Help Now