The
CheckBoxList1.SelectedItem
property will return either the first selected item, or
null
if no items are selected.
Thus if you select multiple items, you will only ever display the details for the first selected item. And if you deselect all items, you will get a
NullReferenceException
trying to access
CheckBoxList1.SelectedItem.Value
.
Also, your stored procedure is vulnerable to
SQL Injection[
^].
NEVER use string concatenation/interpolation to build a SQL query.
Rewrite your stored procedure to avoid the vulnerability, and change it to accept multiple values - for example:
ALTER PROCEDURE dbo.TvWardwisePatientlist
(
@WardId varchar(max)
)
As
SET NOCOUNT ON;
SELECT
ROW_NUMBER() OVER (ORDER BY ad.RegistrationNo ASC) AS SrNo,
bm.BedNo,ad.RegistrationNo,
TM.Name + ' ' + emp.FirstName as DoctorName,
cmp.status
FROM
Admission ad
INNER JOIN Registration pr ON ad.RegistrationId = pr.Id
INNER JOIN Encounter enc ON ad.EncounterId = enc.Id
INNER JOIN BedMaster bm ON ad.CurrentBedId = bm.Id
INNER JOIN WardMaster wm ON ad.CurrentWardID = wm.WardId
LEFT JOIN Employee emp ON ad.AdmittingDoctorId = emp.ID
LEFT JOIN TitleMaster TM ON TM.TitleID = emp.TitleId
LEFT JOIN StatusMaster cmp ON enc.StatusId = cmp.StatusId
WHERE
ad.CurrentWardID In (SELECT Value FROM String_Split(@WardId, ','))
And
ad.DischargeDate Is Null
And
ad.PatadType Not In ('D', 'C')
Change your code to pass a concatenated list of ward IDs to your stored procedure:
protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> selectedWards = new List<string>(CheckBoxList1.Items.Count);
foreach (ListItem item in CheckBoxList1.Items)
{
if (item.Selected)
{
selectedWards.Add(item.Value);
}
}
string mainconn = ConfigurationManager.ConnectionStrings["WardDisplayTV"].ConnectionString;
using (SqlConnection sqlconn = new SqlConnection(mainconn))
using (SqlCommand sqlcomm = new SqlCommand())
{
sqlcomm.Connection = sqlconn;
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.CommandText = "TvWardwisePatientlist";
sqlcomm.Parameters.AddWithValue("WardId", string.Join(",", selectedWards));
SqlDataAdapter sd = new SqlDataAdapter(sqlcomm);
DataTable dt = new DataTable();
sd.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
NB: Be sure to wrap disposable objects like
SqlConnection
and
SqlCommand
in
using
blocks to ensure they're always disposed of properly.
And when you're using a
SqlDataAdapter
, it will open and close the connection for you; there's no need to call
sqlConn.Open()
before calling
sd.Fill()
.
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]