Solved

Populate CheckedListBox with results of query

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
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…

707 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

16 Experts available now in Live!

Get 1:1 Help Now