SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select and dispaly only one record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eugz
Posting Yak Master

193 Posts

Posted - 09/18/2013 :  12:53:11  Show Profile  Reply with Quote
Hi All.

I have view with Patients name and Appointment table where I save those patients. In form I have 2 comboboxes. For PatientComboBox source is store procedure based on Patient view. For PatientAppintmentComboBox I would like to create store procedure.

How to create store procedure to display only one PatierntName record in PatientAppointmentComboBox if Patient_Id selected from PatientComboBox exist or not exist in Appointment?

Thanks.

Thanks.

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 09/18/2013 :  14:04:47  Show Profile  Reply with Quote
When the user selects a patient in the PatientComboBox, capture the patientid of the selected item. Then call another stored procedure with that patientid as the parameter to get all the PatientAppointments. Use that to populate the PatientAppointmentComboBox.

If there are no appointments, it will return no rows, so your PatientAppointmentComboBox will be empty.

Depending on what client application you are using you can also use binding between the Patient Records and PatientAppointment Records to achieve the same. If you go that route, you would load all the patients and all the appointments into the client code as two collections and then bind them to the controls.
Go to Top of Page

eugz
Posting Yak Master

193 Posts

Posted - 09/18/2013 :  14:41:48  Show Profile  Reply with Quote
Hi James. Thanks for replay.

I have store procedure
quote:

Alter Procedure [dbo].[sp_GetPateintAppointment]
(
@Patient_Id as bigint
)
as

Select
PatientAppointment_Id
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.PatientAppointment
where Patient_Id = @Patient_Id
union
select
0
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.v_Patient p
where Patient_Id = @Patient_Id


But if in PatientAppointment table one of the patient has some record so same amount of record I have when open PatientAppointmentComboBox. I would like to display only one record for according Patient_Id. How to get that?

Thanks.

Edited by - eugz on 09/18/2013 14:45:42
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 09/18/2013 :  15:11:05  Show Profile  Reply with Quote
If you have more than one records that get displayed in the PatientAppointmentComboBox, and you want to display only one of those, which of those records do you want to display?

First, make this change to the stored procedure. That will show only one record, although you won't be able to predict which record will show up.
Alter Procedure [dbo].[sp_GetPateintAppointment]
(
@Patient_Id as bigint
)
as

Select TOP (1)
PatientAppointment_Id
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.PatientAppointment
where Patient_Id = @Patient_Id
union
select
0
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.v_Patient p
where Patient_Id = @Patient_Id
If that looks like what you are trying to get, now decided which record to show by adding an "ORDER BY" clause. Suppose you had a column named AppointmentDate in the table, and you wanted to show the row which has the latest AppointmentDate. Then, the order by clause would be like shown below:.
Alter Procedure [dbo].[sp_GetPateintAppointment]
(
@Patient_Id as bigint
)
as

Select TOP (1)
PatientAppointment_Id
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.PatientAppointment
where Patient_Id = @Patient_Id
union
select
0
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.v_Patient p
where Patient_Id = @Patient_Id
order by AppointmentDate DESC
But, none of what I described above may be what you are looking for. I didn't quite follow what you meant by you want to show only one record. When you have a combobox, isn't that really meant to list many appointments, with only one of those visible in the control, and the others visible when you click on it?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000