Here’s how you can create an ASP.NET Web Forms page to insert records into the OPD_Bookings table with dropdowns for selecting Patient and Doctor names but saving their respective IDs in the database.
Steps to Create the Form
- Add the Connection String in
Web.config
:
<connectionStrings>
<add name="hospitaldbConnectionString" connectionString="Data Source=DESKTOP-R22567N\SQLEXPRESS;Initial Catalog=hospitaldb;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Design the Web Form (InsertBooking.aspx
):
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="InsertBooking.aspx.cs" Inherits="HospitalManagement.InsertBooking" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Insert OPD Booking</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server" class="container mt-5">
<h2>Insert OPD Booking</h2>
<div class="mb-3">
<label for="ddlPatient" class="form-label">Patient Name</label>
<asp:DropDownList ID="ddlPatient" runat="server" CssClass="form-control">
<asp:ListItem Text="Select Patient" Value="" />
</asp:DropDownList>
</div>
<div class="mb-3">
<label for="ddlDoctor" class="form-label">Doctor Name</label>
<asp:DropDownList ID="ddlDoctor" runat="server" CssClass="form-control">
<asp:ListItem Text="Select Doctor" Value="" />
</asp:DropDownList>
</div>
<div class="mb-3">
<label for="txtBookingDate" class="form-label">Booking Date</label>
<asp:TextBox ID="txtBookingDate" runat="server" CssClass="form-control" TextMode="Date"></asp:TextBox>
</div>
<div class="mb-3">
<label for="txtVisitDate" class="form-label">Visit Date</label>
<asp:TextBox ID="txtVisitDate" runat="server" CssClass="form-control" TextMode="Date"></asp:TextBox>
</div>
<div class="mb-3">
<label for="txtVisitTime" class="form-label">Visit Time</label>
<asp:TextBox ID="txtVisitTime" runat="server" CssClass="form-control" TextMode="Time"></asp:TextBox>
</div>
<div class="mb-3">
<label for="txtSymptoms" class="form-label">Symptoms</label>
<asp:TextBox ID="txtSymptoms" runat="server" CssClass="form-control"></asp:TextBox>
</div>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" CssClass="btn btn-primary" OnClick="btnSubmit_Click" />
</form>
</body>
</html>
3. Code-behind (InsertBooking.aspx.cs
):
using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace HospitalManagement
{
public partial class InsertBooking : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadPatients();
LoadDoctors();
}
}
private void LoadPatients()
{
string connectionString = ConfigurationManager.ConnectionStrings["hospitaldbConnectionString"].ConnectionString;
string query = "SELECT PatientID, CONCAT(FirstName, ' ', LastName) AS FullName FROM Patients";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
ddlPatient.DataSource = reader;
ddlPatient.DataTextField = "FullName";
ddlPatient.DataValueField = "PatientID";
ddlPatient.DataBind();
}
}
ddlPatient.Items.Insert(0, new System.Web.UI.WebControls.ListItem("Select Patient", ""));
}
private void LoadDoctors()
{
string connectionString = ConfigurationManager.ConnectionStrings["hospitaldbConnectionString"].ConnectionString;
string query = "SELECT DoctorID, CONCAT(FirstName, ' ', LastName) AS FullName FROM Doctors";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
ddlDoctor.DataSource = reader;
ddlDoctor.DataTextField = "FullName";
ddlDoctor.DataValueField = "DoctorID";
ddlDoctor.DataBind();
}
}
ddlDoctor.Items.Insert(0, new System.Web.UI.WebControls.ListItem("Select Doctor", ""));
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["hospitaldbConnectionString"].ConnectionString;
string query = "INSERT INTO OPD_Bookings (PatientID, DoctorID, BookingDate, VisitDate, VisitTime, Symptoms, Status, CreatedAt, UpdatedAt) " +
"VALUES (@PatientID, @DoctorID, @BookingDate, @VisitDate, @VisitTime, @Symptoms, @Status, GETDATE(), GETDATE())";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@PatientID", ddlPatient.SelectedValue);
cmd.Parameters.AddWithValue("@DoctorID", ddlDoctor.SelectedValue);
cmd.Parameters.AddWithValue("@BookingDate", txtBookingDate.Text);
cmd.Parameters.AddWithValue("@VisitDate", txtVisitDate.Text);
cmd.Parameters.AddWithValue("@VisitTime", txtVisitTime.Text);
cmd.Parameters.AddWithValue("@Symptoms", txtSymptoms.Text);
cmd.Parameters.AddWithValue("@Status", "Pending");
conn.Open();
cmd.ExecuteNonQuery();
}
}
Response.Write("<script>alert('Booking added successfully!');</script>");
}
}
}
Explanation
- Dropdowns for Patients and Doctors:
- Patient and Doctor names are loaded dynamically from their respective tables using the
LoadPatients
andLoadDoctors
methods. DataTextField
is set to the full name (FirstName + LastName
), andDataValueField
is set to the IDs (PatientID
,DoctorID
).
- Patient and Doctor names are loaded dynamically from their respective tables using the
- SQL Query:
- The
INSERT INTO OPD_Bookings
query inserts data into the database using@parameters
.
- The
- Default “Pending” Status:
- The
Status
column is hardcoded to “Pending” during insertion.
- The
- Bootstrap for UI:
- Used Bootstrap classes for a modern and responsive design.
- Alert Confirmation:
- Displays a success message using JavaScript.