Anyone or Sir twalgrave,How to pass (Query) parameters to data report  in VB

muslimgirl
muslimgirl used Ask the Experts™
on
i want to give parameter to data report in VB . whenever idata report star it shows a input box in which it get value of name ,i pass for example name="Muslimgirl"  then it show only the record of "Muslimgirl" that record .plz  tell me events and coding related to this problem i am in gr8 problem.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If I'm correct, you are using a dataenvironment to populate your datareport.  If that's the case:

    DataEnvironment1.Command1 "muslimgirl"
   
    DataReport1.Show


Replace DataEnvironment1 with the name of your dataenvironment

Replace Command1 with the name of your command (make sure your command is built something like:
Using SQL Statement of select * from MyTable where UserID = ?

Top Expert 2012

Commented:
If I can make a slight correction, I believe you need the Refresh method as well, as in:
   DataEnvironment1.Command1 "muslimgirl"
   DataReport1.Refresh          'Add this line  
   DataReport1.Show

Anthony
Anthony,
You don't need it if you haven't opened up the datareport yet, but you will need it if you haven't.  It won't hurt if you haven't opened up the datareport so it is a good idea to do that.  Thanks for the suggestion.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012

Commented:
twalgrave,

You are absolutely correct.

Anthony

Author

Commented:
TWALGRAVE
  I DID THE SAME AS YOU GUIDE ME BUT NOW IT IS GIVING THAT ERROR MESSAGE. "PARAMETER  ?_1 HAS NO DEFAULT VALUE" I NEED ITS SOLUTION AT NIGHT I NEED IT.ANOTHER PROBLEM WITH REPORT IS THAT IS I WANT TO DISPLAY PARENT RECORD IN REPORT ONE TIME AND CHILD RECORDS MANY TIMES BUT I AM FAACIG A PROBLEM THAT EACH TIME CHILD RECORDS REPEAT ,PARENT RECORDS ALSO REPEAT
 I WILL BE VERY THANKFULL TO YOU  FOR THESE SOLUTIONS.
muslimgirl,
I tested the code that I gave you using my own DB connections and my own parameters.  I need to see your parameters and your query and all the code relating to starting up the datareport and all the code regarding setting up the dataenvironment you are using to be able to help.  It would also help if you described the fields on the database and any constraints you have on those fields if I am to be able to help further.  Thanks.

Author

Commented:
Actually, i am not handling dataencoirement by code but i am hading it by adding a connection in dataenvoirement window and then adding a command in it.i am giving a little bit info about my programming .I want to just see a input box when i start report .i have three tables in my  database ,One is Doctor(Master) ,One is PATIENT(Master table) and two of its child tables PAT_DOCTOR(detail table) and PAT_TEST(Detail table) these two tables have one to many relation with PATIENT table and PAT_DOCTOR has also one to many relation with doctor(if u know system of hospital then u will under stand my database easily that each patient is treated by many doctors(I normalize this tables and made a table PAt_DOCTOR which is related to both tables(This table is only for normalization) and each doctor treat many patients and similarly each patient has many test and each test is related to many patients(I also normalize it and made a table PAT_TEST b/w PAteint and Test just like in Dotor--Patient now i wanat to generate two reports in which i want to take input at start of report and take patient_id and then display that patients record(ITs format should like that Patient fields dispaly once and Pat_Test and Pat_Doctor fields agaist this record many time  now plz i am online only for 10 min if you answer now in five min i will be thank full to you.
muslimgirl,

you are handling the dataenvironment through code if you tried our suggestions of:
  DataEnvironment1.Command1 "muslimgirl"
  DataReport1.Refresh          'Add this line  
  DataReport1.Show

This refreshes the dataenvironment updates the command within the data environment.  Thanks for the info on the DB structure, but the problem lies in the parameters.  I need to see what the dataenvironment looks like.  If you can tell me all the settings on the dataenvironment (including the Parameters tab) that would be more helpful.
Muslimgirl,

Have you tried something more simple like:

Private Sub Report_Open(Cancel As Integer)
       Dim strSQL As String

       strSQL = "SELECT [Tracking].[Interview Completed?], [Basic Info].* FROM Tracking " & _
       "INNER JOIN [Basic Info] ON Tracking.CandidateID =  [Basic Info].CandidateID " & _
       "WHERE [Tracking].[Interview Completed?]<>0;"
       Me.RecordSource = strSQL
               

End Sub
Replace the strSQL with a SQL statement that works for your environment.  Somebody else solved their problem this way and I thought it might help you here.  It seems more straightforward than trying to manipulate the dataenvironment.

Author

Commented:
i wrote in SQL Query box that "Select * from patient where pat_code=?  " and this statement automatically generate a parameter "?" then i got the message that change paramter name .i set it equal to "param" and left value box empty in parameter tab.now paramter was "?", name was "param" and value was empty when i execute report it generate error that "PARAMETER  ?_1 HAS NO DEFAULT VALUE" so that i am now trapped here.plz i also ask another question in above comments that was related to my report FORMAT ,plz also  view it again , thank you for solution.i now check it then will response you
Delete and recreate the command in the dataenvironment (for some reason refresh doesn't seem to want to work).
If you make modifications to an existing command it seems to lose its brains.  Also don't use any special characters (including spaces) when you give the parameter ? a name.  Stick to a name like USERID.

Then in your code, do this:
    DataEnvironment1.Command1 "muslimgirl"
    MsgBox DataEnvironment1.rsCommand1.RecordCount

I've run this test about a dozen times and it works.

We can get to your formatting question once we determine that pulling up the report works.

Author

Commented:
twalgrave
Thnx very much ,bcoz your answer about my parameters was absolutly right ,i was doing a mistake now i check it ,it works properly,
i want this format or syntax of report if u can help me.
*******************************************
*******************************************
Patient_code=1105
patient_name=Robert
patient_age=56
patient_gender=Male

Test_id     |Test_name      |Test_desciption
------------------------------------------
  12        |Beating        |About heart speed
  45        |sugar in blood |about bood sugar
-------------------------------------------

Doctor_id|Doctor_name|Doctor_email
-------------------------------------------
   11    |Mr.John    |john@hot.com
   16    |Mr Tean    |Tean@hot.com
******************************************
*****************************************
if you can provide me query or anything else helpful about to create this like format of report (I am actually facing problem in displaying patient record one time bcoz each time i repaet test and doctor rows or records patient record also repaet.Plz if you can give me such a query)
Actually problem is with "PAGE HEADER" section bcoz,i want to place  patient all commaand in this section but it does not allow me to do that.I know that you will solve it
     THNX

Now we're down to the easy part!
With the dataenvironment you can make child commands.  These are linked back up to the parent command.  The way you do this is to select the command that contains the patient record information only as the parent command (we called it commad1 in the previous examples, so we'll keep it that way for the remainder.  So, Command1 will look something like this:

Command1:
   Select Patient_code, patient_name, patient_age, patient_gender from patient where LastName = ?

Right-click on Command1 and select Add CHILD Command.  rename this command as PatientTests.  Note: The PatientTests must be under the Command1 command not even with it in the tree.
For example:
This would be CORRECT
Connection1
   Command1
      Patient_code
      patient_name
      patient_age
      patient_gender
      PatientTests (new command object)
         Test_id
         Test_name
         Test_description


This would be NOT correct
Connection1
   Command1
      Patient_code
      patient_name
      patient_age
      patient_gender
   PatientTests (new command object)
      Test_id
      Test_name
      Test_description

OK, so now right-click on PatientTests.  Fill in the SQL query something like:
Select Test_id , Test_Name, Test_desciption from PatientTests.  Don't add a parameter.  Then go into the Relation Tab and set up the relationship between Command1 and PatientTests.  Set the parent command to Command1 (which it should already be.  If it isn't you probably have the PatientTests not as a child of command1 as in the bad example provided.  Then set the parent field and child field to the filed that links these two tables together and press add.  If more than one field is required to link the two tables together, then add those other fields as well.

Repeat the process for the third nd final command.  We will call this command DoctorInfo.  Keep in mind that DoctorInfo is a child of Command1, not a child of PatientTests (unless you allow for multiple doctors in your database design).  Either way put DoctorInfo as a child of whatever command object it is supposed to be and set it up just as I described for PatientTests except for the SQL query as it cwould be something like:
Select Doctor_id, Doctor_name, Doctor_email from patientdoctors. So now your dataenvironment tree should look like:
Connection1
   Command1
      Patient_code
      patient_name
      patient_age
      patient_gender
      PatientTests
         Test_id
         Test_name
         Test_description
      DoctorInfo (new command object)
         Doctor_id
         Doctor_name
         Doctor_email

OR this could also be correct (depending on your table relationships)

Connection1
   Command1
      Patient_code
      patient_name
      patient_age
      patient_gender
      PatientTests
         Test_id
         Test_name
         Test_description
         DoctorInfo (new command object)
            Doctor_id
            Doctor_name
            Doctor_email

Once all that is set up, you need to draft up a datareport.  If you want, you can use your old report, but it will wipe out the contents when you do what I tell you to do and I think it might be better to keep the old one around.

Once you create the new DataReport (we'll call it datareport2), set the datasource as dataenvironment1 and the datamember = command1
Then right-click in the report window and select "retrieve structure".  You should now see several report breakdown areas. (one for command1 header/footer, one for PatientTests header/footer, and one for DoctorInfo header/footer and finally a detail in the middle.  Now you can place your textboxes anywhere on the report hat you'd like.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial