List Box And Adodb Combination

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • darrel
    New Member
    • Nov 2006
    • 72

    #1

    List Box And Adodb Combination

    Hey guys i have a question am having trouble in displaying record in a list box, what i want to achieve is this... i have 2 check boxes, checkbox1 is name MEASLES and checkbox is name "CHICKEN POX". i have a database name MEDICALDB and has 4 tables named STUDENT NUMBER, NAME, CHIKEN POX AND MEASLE..

    i have this example database record:

    Student Number Name Chiken Pox Measles
    200013253 Darrel NO NO
    200013254 Gary YES NO
    200013255 Norman NO YES
    200013256 Larry YES YES

    The scenarion i wanted to search for the names that have value of YES in the Chicken Pox Field and display the name in a listbox..

    I'am having trouble on how to implement it in my program so far i have this codes:

    [CODE=vb]
    Dim rsShowrec1 As New ADODB.Recordset
    Dim cnn As New ADODB.Connectio n

    Set cnn = New ADODB.Connectio n
    cnn.ConnectionS tring = _
    "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
    "Data Source=" & App.Path & "\MedicalDB.mdb ;"
    cnn.Open

    If check1.value = 1 Then
    Label1.caption = "YES"

    On Error Resume Next
    rsShowrec1.Sour ce = "SELECT * FROM MecidalData where [CHICKEN POX] = ' " & Label1.caption & " '
    rsShowrec1.Open , cnn, adOpenStatic, adLockOptimisti c

    If rsShowrec1.EOF <> True Then
    List1.AddItem rsShowrec1.Fiel ds("Name").Valu e
    rsShowrec1.Move Next
    end if
    End Sub
    [/CODE]

    So far its not working... Any help will do... Thank you... i hope your getting wat i want to achieve... By d way am using ADODB for my database connection.
    Last edited by debasisdas; Oct 26 '07, 05:51 AM. Reason: Added code=vb tag
  • debasisdas
    Recognized Expert Expert
    • Dec 2006
    • 8119

    #2
    try this
    [code=vb]
    sqlstr= "SELECT * FROM MecidalData where [CHICKEN POX] = ' " & Label1.caption & " '
    rsShowrec1.Open sqlstr, cnn, adOpenStatic, adLockOptimisti c

    Do While rsShowrec1.EOF = False
    List1.AddItem rsShowrec1!name
    rsShowrec1.Move Next
    Loop
    [/code]

    Comment

    • Killer42
      Recognized Expert Expert
      • Oct 2006
      • 8429

      #3
      My guess is, this is VB6 - right?

      So what's wrong with your current code? "Not working" is pretty vague. Is it producing an error? If so, what error and at what line of code? If not, in what way does it not match what you expect?

      Comment

      • darrel
        New Member
        • Nov 2006
        • 72

        #4
        Theres no error on runtime my problem is the name are not populated in the list box. i will try another sequence. ill keep you posted . Many thanx.

        Comment

        • darrel
          New Member
          • Nov 2006
          • 72

          #5
          Hello, ive tried what debasisdas suggestion, no sad to say no luck, there no error on runtime, but it doesnt populate my database record in the listbox and it has infinite loop.

          Any more suggestion am badly need to finish this task so that my system will be implemented... Thank you..

          Comment

          • 9815402440
            New Member
            • Oct 2007
            • 180

            #6
            hi

            the problam is in your query. if datatype of the [CHICKEN POX] is YesNo then
            then your query must like this

            SELECT * FROM MecidalData where [CHICKEN POX] = " & iif(Label1.capt ion = 'Yes',True,Fals e)


            regards
            manpreet singh dhillon hoshiarpur

            Comment

            • darrel
              New Member
              • Nov 2006
              • 72

              #7
              Thank you for all the advice and tips. I figured out how I can populate database records in my list box, I just used the long method procedure.

              Now, I have a new question for you guys. Now that I'm able to populate database records in my lists, what I wanted to do now is to populate all the items in my lists to a datareport to produce a hard copy.

              Can you give me sometimes, I'm nearly close in finishing my Medical and Dental Record System.

              Thank you, it's good to belong here... hooray!
              Last edited by Killer42; Oct 28 '07, 03:14 AM.

              Comment

              • QVeen72
                Recognized Expert Top Contributor
                • Oct 2006
                • 1445

                #8
                Hi,

                In DataEnvironment , add a Connection object and set the parameters to the database and test the connection. Right click Conn and add a Command object. Change the SQL query as "Select * From MyTable".

                Add a new DataReport to the project. Open the Command Object node. You will see all the fields. Drag them and drop them on the DataReport. Adjust the lines. And in VB6 to show the report:

                DataReport1.Sho w

                Regards
                Veena
                Last edited by Killer42; Oct 28 '07, 03:17 AM.

                Comment

                Working...