Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Visual Basic ( VB ) (http://www.go4expert.com/forums/visual-basic/)
-   -   Problem In DataReport in Visual Basic 6 (http://www.go4expert.com/forums/datareport-visual-basic-6-t19621/)

mayjune 29Sep2009 23:11

Problem In DataReport in Visual Basic 6
 
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


nimesh 2Oct2009 02:53

Re: Problem In DataReport in Visual Basic 6
 
Mj: Any specific reason that you want the output this way?

nimesh 2Oct2009 02:57

Re: Problem In DataReport in Visual Basic 6
 
Instead of delete from for2mins check if you could use truncate table for2mins

mayjune 2Oct2009 22:45

Re: Problem In DataReport in Visual Basic 6
 
Quote:

Originally Posted by nimesh (Post 58264)
Mj: Any specific reason that you want the output this way?

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

mayjune 2Oct2009 22:50

Re: Problem In DataReport in Visual Basic 6
 
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 ..?

nimesh 3Oct2009 13:01

Re: Problem In DataReport in Visual Basic 6
 
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.


All times are GMT +5.5. The time now is 00:06.