1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Problem In DataReport in Visual Basic 6

Discussion in 'Visual Basic ( VB )' started by mayjune, Sep 29, 2009.

  1. mayjune

    mayjune New Member

    Joined:
    Jun 14, 2009
    Messages:
    817
    Likes Received:
    33
    Trophy Points:
    0
    Occupation:
    Student
    Location:
    Pune,Delhi
    Hi,
    I am making a clinic Management. I have to generate a report of test to be conducted for a particular patient. Problem I am facing is, I am able to display the tests name and costs, But i want to display Patient Name and Doctor Name only ONCE!!
    i.e.

    Code:
    Doctor Name   Patient Name       Test         Cost of Test
    Doctor1           Patient 1
                                                test1           cost1
                                                test2           cost2
                                                test3           cost3
    
    (don't mind the identation, its not coming proper here for some reason i don't know...)

    But it is being displayed as

    Code:
    Doctor Name   Patient Name     Test         Cost of Test
    Doctor1          Patient 1          test1           cost1
    Doctor1          Patient 1          test2           cost2
    Doctor1          Patient 1          test3           cost3
    
    How do I do this?

    Also I thought of a little cheat way out of this. I thought i'll create a new table of the values required i.e table (doc name, patient name, test name, test cost) in which my first row will have doc name, patient name, null, null and subsequent rows will have null, null, test name(i), test cost(i)

    So it is expected that the values are inserted like this
    Code:
    DNAME    PNAME             TESTNAME          COST
       D1            p1                  -                     -
       -                -             CHEST XRAY          100
       -                -               URINE                 250
       -                -               BLOOD                100
    
    But it comes like this
    Code:
    DNAME    PNAME    TESTNAME               COST
       -                -             CHEST XRAY         100
       D1            p1                  -                  -
       -                -               URINE                250
       -                -               BLOOD                100
    
    I don't understand whats the problem...Also note
    If the table is created the first time the values get inserted properly,
    but if i insert values after deleting all values it goes random...

    Here's the code...

    Code:
    Private Sub cmdGivePresc_Click()
        On Error GoTo step1
        
        If txtInfo(7).Text <> "" Then
    
    step2:
            Set temp = New ADODB.Recordset
            
            temp.Open "delete from for2mins", Ado, adOpenKeyset, adLockOptimistic
            temp.Open "select * from for2mins", Ado, adOpenKeyset, adLockOptimistic
                    
            temp.AddNew
            temp(0) = doc_name
            temp(1) = pat_name
            temp(2) = Null
            temp(3) = Null
            
            Set Test = New ADODB.Recordset
            Test.Open "select tname, cost from test where test_id in (select test_id from test_result where test_result.presc_id in (select presc_id from appt a, doctor d where a.appt_time = '" & appt_time & "' and a.appt_date = '" & appt_date & "' and d.doc_id = " & doc_id & "))", Ado, adOpenKeyset, adLockOptimistic
    
            While Not Test.EOF
                temp.AddNew
                temp(0) = Null
                temp(1) = Null
                temp(2) = Test(0)
                temp(3) = Test(1)
                Test.MoveNext
            Wend
            
            temp.Update
                    
            DataEnvironment1.Connection1.Open
            DataEnvironment1.Command1
            DataReport1.Show
            
        Else
            MsgBox "Enter Patient ID or Select Appointment Date/Time", vbCritical
        End If
        Exit Sub
    step1:
            DataEnvironment1.Connection1.Close
            GoTo step2
    End Sub
     
  2. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    Mj: Any specific reason that you want the output this way?
     
  3. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    Instead of delete from for2mins check if you could use truncate table for2mins
     
  4. mayjune

    mayjune New Member

    Joined:
    Jun 14, 2009
    Messages:
    817
    Likes Received:
    33
    Trophy Points:
    0
    Occupation:
    Student
    Location:
    Pune,Delhi
    Because in a report doctors name and patients name comes only once, not repeatedly while tests-costs can be multivalued hence it has to display all of them
     
  5. mayjune

    mayjune New Member

    Joined:
    Jun 14, 2009
    Messages:
    817
    Likes Received:
    33
    Trophy Points:
    0
    Occupation:
    Student
    Location:
    Pune,Delhi
    no truncate didn't help....
    I found a cheat in it, I added another coloumn id, in which i add 1...2....3...as coloumn number and i use order by id....which sorts the problem out.
    But this isn't a right way to do it, someone told me to use grouping i tried that but that too didnt help ...I don't want to create another table and all....if thats possible ..?
     
  6. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    group by won't help
    I don't think this can be done using sql

    it should be done after the record is feteched, while adding the output to the report

    I don't think this should be a problem as long as you are just displaying and not storing them in that way.
     

Share This Page