[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Populate CheckedListBox with results of query

Posted on 2008-10-02
5
Medium Priority
?
1,158 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
[X]
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
  • 3
  • 2
5 Comments
 
LVL 12

Assisted Solution

by:rionroc
rionroc earned 1000 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

649 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