Member Avatar for 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?

  • 3 Contributors
  • 4 Replies
  • 89 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ITchimes

Dani AI

Generated

Short version: treat consultation rows as links to doctor and patient records (store their IDs), show human names with JOINs when listing, and in the DetailsView replace the autogenerated ID textboxes with lookup controls so the doctor can pick from lists.

Normalization and display
As suggested, store DoctorID and PatientID as foreign keys in Consultation rather than duplicating names. Enforce FK constraints and index those columns. When you show existing consultations use a SELECT that joins Doctor and Patient to get readable names (for example, join on DoctorID/PatientID and select the display name fields).

DetailsView workflow
Put the DetailsView into insert mode and replace the BoundField(s) for the FK columns with TemplateField(s) containing DropDownList controls bound to separate data sources that return ID + display name from the Doctor and Patient tables. For insertion you can either:

  • use ControlParameter entries on the Consultation SqlDataSource so InsertParameters take the DropDownList.SelectedValue, or
  • handle DetailsView.ItemInserting in code-behind and copy the selected values into the insert values.

Example (VB code-behind approach):

Protected Sub DetailsView1_ItemInserting(sender As Object, e As DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
    Dim ddlDoc As DropDownList = CType(DetailsView1.FindControl("ddlDoctor"), DropDownList)
    Dim ddlPat As DropDownList = CType(DetailsView1.FindControl("ddlPatient"), DropDownList)
    If ddlDoc IsNot Nothing Then e.Values("DoctorID") = ddlDoc.SelectedValue
    If ddlPat IsNot Nothing Then e.Values("PatientID") = ddlPat.SelectedValue
End Sub

Troubleshooting
If the DetailsView still shows textboxes it means the field is autogenerated—convert it to a TemplateField. If inserts fail, check parameter names/types, ensure SelectedValue is present (no empty selection), and validate types (Int32 vs string). As hinted, JOINs are the right way to show names; a UNION is not needed here.

Recommended Answers

All 4 Replies

Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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>
Edited by jbisono because: n/a
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.