Categories: Asp.net
Tags:

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

  1. 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

  1. Dropdowns for Patients and Doctors:
    • Patient and Doctor names are loaded dynamically from their respective tables using the LoadPatients and LoadDoctors methods.
    • DataTextField is set to the full name (FirstName + LastName), and DataValueField is set to the IDs (PatientID, DoctorID).
  2. SQL Query:
    • The INSERT INTO OPD_Bookings query inserts data into the database using @parameters.
  3. Default “Pending” Status:
    • The Status column is hardcoded to “Pending” during insertion.
  4. Bootstrap for UI:
    • Used Bootstrap classes for a modern and responsive design.
  5. Alert Confirmation:
    • Displays a success message using JavaScript.