help with data retrieving from database

[Izzy123]

Hi, I'm having a question about retrieving data. I'm using VS 2008 and I have to create a new consultation record for a patient and I've already created a database with 3 tables, which are doctor, patient and consultation.

I have 10 records saved in consultation table, and I've made a aspx form which contains a DetailsView that ask for new consultation details to be added. But I have to be able to get the doctor and patient information from the database and put in the DetailsView, then fill in the rest of the consultation details. The information entered have to be written to the database in the consultation table. But I don't know how to get doctor and patient information from database and apply them into the DetailsView so that doctor who wants to add new consultation details can select patient and doctor information from the database.

I was thinking maybe I need to do a query with insert statement and apply into the DetailView

this is the query i have:

INSERT INTO [Consultation] ([ConsultationID], [GPname], [PatientFirstName], [PatientLastName], [Height], [Weight], [IllnessDescription], [Diagnosis], [Date]) VALUES (@ConsultationID, @GPname, @PatientFirstName, @PatientLastName, @Height, @Weight, @IllnessDescription, @Diagnosis, @Date)

as you can see, I need to get the GPname, PatientFirstName and PatientLastName from the database tables and the rest will be filled in by the doctor. Can anyone help please?

[jbisono]

Is this C# or Visual Basic? second if you have separate table for doctor and patient in the consultation table you do not need to add gpname, patientfirstname, patientlastname instead you save the id for both so you table for consultation will end like ConsultationId, DoctorId, PatientId, Height, etc.

I understand the Height and weight put it on the consultation table because that can change over the time, i will help you but first i would like to help you normalize your database a little bit more.

another thing is do you want to show all patient always or all patient that belongs to a particular doctor?

regards

[Izzy123]

hey thanks for the reply. It's Visual Basic and I'm doing in Visual Studio 2008.
The patient record will be displayed in a separate aspx form where doctor can delete, add or edit the patient record.
The consultation record basically just have to display all the consultation information from all doctors and patients. Now the problem is that I've already created the Add Consultation DetailsView in an aspx form, but I can't seem to fetch data from the Patient and Doctor table and put in the PatientID and DoctorID field, it just showing a simple text field and ask me to enter the IDs.

[jbisono]

are you using sqldatasource controls to bind the detailsview? if that is the case just replace the textfields for dropdownlist, create to sqldatasource one for the doctor table, and the other one for patient. then your dropdownlist you bind it like this.

<asp:DropDownList ID="ddlDoctor" runnat="server" DataSourceID="YourSqlDataSourceForDoctor" DataTextField="NameOfDoctorField" DataValueField="DoctorIDField" SelectedValue='<%# Eval("DoctorIdFromConsultationTable") %>'></asp:DropDownList>

[ITchimes]

First of all you will have to apply INNER JOIN between patient and doctor table.later it UNION it to consultant table .Then fill them in DATASET .Use dataset as data source for detail view.