Click here to Skip to main content
15,347,117 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Dear,
I am working on a project where I need to fetch "Id" from a stored procedure as a checkbox and pass the value to Grid view. The code below is working fine if i select a single checkbox. but when i select multiple it will only show me the selected value in the gridview.

and when i am unchecking the box its giving me error "
Object reference not set to an instance of an object.
"

Here what we need to achieve is selecting multiple values and passing them to grid view. and show the data for both checked values data in a single grid.

This the Asp. code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="OTDashBoardTV.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:CheckBoxList ID="CheckBoxList1"  runat="server" OnSelectedIndexChanged="CheckBoxList1_SelectedIndexChanged"
                            AutoPostBack="True" CellPadding="10" CellSpacing="10"         RepeatDirection="Horizontal" RepeatLayout="Flow" TextAlign="Right"
                           >
                           
                            

                        </asp:CheckBoxList>

            <hr />
            
            <asp:Label ID="Message" runat="server" Text="Label" Visible="True"></asp:Label>
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>

        </div>
    </form>
</body>
</html>


Coding i am using C# as shown below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace OTDashBoardTV
{
    public partial class Default : System.Web.UI.Page
    {
        string conditionThr = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {
            // This to define the procdure for checklist
            if (!Page.IsPostBack)
            {
                string mainconn = ConfigurationManager.ConnectionStrings["WardDisplayTV"].ConnectionString;
                SqlConnection sqlconn = new SqlConnection(mainconn);
                SqlCommand sqlcomm = new SqlCommand();
                sqlcomm.Connection = sqlconn;
                sqlcomm.CommandType = CommandType.StoredProcedure;
                sqlcomm.CommandText = "akhTVWardBind";
                sqlconn.Open();
                CheckBoxList1.DataTextField = "WardName";
                CheckBoxList1.DataValueField = "WardId";
                CheckBoxList1.DataSource = sqlcomm.ExecuteReader();
                CheckBoxList1.DataBind();
                sqlconn.Close();

            }
        }
      
protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)
        {

            
           
            string mainconn = ConfigurationManager.ConnectionStrings["WardDisplayTV"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(mainconn);
            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = CommandType.StoredProcedure;
            sqlcomm.CommandText = "TvWardwisePatientlist";
            sqlconn.Open();
            sqlcomm.Parameters.AddWithValue("WardId", CheckBoxList1.SelectedItem.Value);
            SqlDataAdapter sd = new SqlDataAdapter(sqlcomm);
            DataTable dt = new DataTable();
            sd.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
            sqlconn.Close();
            }

        

        
    }
}


This the Stored procedure where we are using.

ALTER PROCEDURE [dbo].[TvWardwisePatientlist]  
(  
  
@WardId varchar(max)  
)  
AS  
  
SET NOCOUNT ON  
  
DECLARE @STRSQL VARCHAR(4000)  
--BEGIN TRY  
--BEGIN TRANSACTION  
SET @STRSQL ='SELECT ROW_NUMBER() OVER (ORDER BY ad.RegistrationNo ASC) AS SrNo,'  
SET @STRSQL= @STRSQL + 'bm.BedNo,ad.RegistrationNo, '  
SET @STRSQL= @STRSQL + 'TM.Name+ ''  ''+ emp.FirstName as DoctorName, cmp.status  '  
--SET @STRSQL= @STRSQL + 'CASE WHEN ad.PatadType in(''A'',''T'') THEN ''Admitted''  END PatientStatus '  

SET @STRSQL= @STRSQL + 'From Admission ad '  
SET @STRSQL= @STRSQL + 'INNER JOIN Registration pr ON ad.RegistrationId=pr.Id '  
SET @STRSQL= @STRSQL + 'INNER JOIN Encounter enc ON ad.EncounterId =enc.Id '  
SET @STRSQL= @STRSQL + 'INNER JOIN BedMaster bm ON ad.CurrentBedId=bm.Id '  
SET @STRSQL= @STRSQL + 'INNER JOIN WardMaster wm ON ad.CurrentWardID=wm.WardId '  
SET @STRSQL= @STRSQL + 'LEFT JOIN Employee emp ON ad.AdmittingDoctorId=emp.ID '  
SET @STRSQL= @STRSQL + 'LEFT JOIN TitleMaster TM ON TM.TitleID=EMP.TitleId '
--SET @STRSQL= @STRSQL + 'LEFT JOIN Employee emp1 ON ad.ConsultingDoctorId=emp1.ID '  
SET @STRSQL= @STRSQL + 'LEFT JOIN StatusMaster cmp ON enc.StatusId=cmp.StatusId '  
  
--SET @STRSQL= @STRSQL + 'WHERE  ad.HospitalLocationId=''' + CONVERT(VARCHAR,@inyHospitalLocationId) + '''  '  
--IF @intRegistrationId>0  
 --SET @STRSQL= @STRSQL + ' and ad.RegistrationId=''' + CONVERT(VARCHAR,@intRegistrationId) + ''' '   
SET @STRSQL= @STRSQL + 'WHERE ad.CurrentWardID=''' + CONVERT(VARCHAR,@WardId) + ''' '  
SET @STRSQL= @STRSQL + 'and ad.DischargeDate IS NULL and ad.PatadType NOT IN(''D'',''C'') '  
  
EXEC (@STRSQL)


kindly let me know where i am having problem.

What I have tried:

I have tried to use this but it not working correctly, in the message i am able to see the print correctly its bringing values in like 8,11 but not loading in the grid.

string mainconn = ConfigurationManager.ConnectionStrings["WardDisplayTV"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(mainconn);
            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = CommandType.StoredProcedure;
            sqlcomm.CommandText = "TvWardwisePatientlist";
            sqlconn.Open();


            int i = 1;
            foreach (ListItem itm in CheckBoxList1.Items)
            {

                if (itm.Selected)
                {

                    if (i < 0)
                        Message.Text = Message.Text + itm.Value;
                    else
                    if (i == 1)
                        Message.Text = itm.Value;
                    else
                        Message.Text = Message.Text + "," + itm.Value;
                    i++;


                }


            }

            sqlcomm.Parameters.AddWithValue("WardId", CheckBoxList1.SelectedItem.Value);

            SqlDataAdapter sd = new SqlDataAdapter(sqlcomm);
            DataTable dt = new DataTable();
            sd.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
            sqlconn.Close();
            }

        }
Posted
Updated 24-May-22 22:28pm
Comments
Dave Kreskowiak 24-May-22 8:07am
   
Which line in that massive code dump throws the exception?

1 solution

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:
SQL
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 
        -- TODO: Use a different delimiter if the ward IDs could contain a comma:
        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:
C#
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";
        
        // TODO: Use a different delimiter if the ward IDs could contain a comma:
        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[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900