Set GridView CheckBox Checked on page load based on database table record using C# and VB.Net in ASP.Net

lingers
 
on Sep 08, 2021 10:53 PM
Sample_185975.zip
508 Views

i have this gridview, i want their checkbox checked if their posi no is in the database on page load from the table cylinder

i have three(3) cylinders available with posino on issuesheet N'['A101','A102','A103']

On page load i want the available cylinders whose posino appears on the issue sheet to be checked (check box)

My code

CREATE TABLE [dbo].[issuesheet](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[pid] [int] NULL,
	[machine] [nvarchar](255) NULL,
	[orderquantity] [float] NULL,
	[workdocket] [nvarchar](50) NULL,
	[cylinders] [nvarchar](255) NULL,
	
	[comment] [nvarchar](255) NULL,
	[solvents] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[issuesheet] ON 
GO
INSERT [dbo].[issuesheet] ([id], [pid], [machine], [orderquantity], [workdocket], [cylinders]) VALUES (1, 206, N'Griller', 67543, N'DCK001', N'['A101','A102','A103'])
GO
SET IDENTITY_INSERT [dbo].[issuesheet] OFF
GO
ALTER TABLE [dbo].[issuesheet] ADD  DEFAULT ('[]') FOR [cylinders]
GO
/////////////////////////////////////////////////////////////////////
Cylinder Table

CREATE TABLE [dbo].[cylinder](
	[id] [int] NULL,
	[posino] [nvarchar](50) NULL,
	[pid] [nvarchar](50) NULL,
	[diameter] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (1, N'A101', N'206', N'25mm')
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (2, N'A102', N'206', N'24mm')
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (3, N'A103', N'206', N'26mm')
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (4, N'A104', N'206', N'27mm')

 

public partial class gridcontinue : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindUserDetails1();
        }
    }

    protected void BindUserDetails1()
    {
        string constr1 = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con1 = new SqlConnection(constr1))
        {
            using (SqlDataAdapter sda1 = new SqlDataAdapter(" SELECT  * from cylinde ", con1))
            {

                // Response.Write(sda1);
                using (DataTable dt1 = new DataTable())
                {
                    sda1.Fill(dt1);
                    avDetails.DataSource = dt1;
                    avDetails.DataSource = dt1;
                    avDetails.DataBind();
                }
            }
        }

        //Required for jQuery DataTables to work.
        if (avDetails.Rows.Count > 0)
        {
            avDetails.UseAccessibleHeader = true;
            avDetails.HeaderRow.TableSection = TableRowSection.TableHeader;
        }
    }

    protected void OnChckedChanged(object sender, EventArgs e)
    {
        CheckBox chk = (CheckBox)sender;
        GridViewRow row = (GridViewRow)chk.NamingContainer;

        string number = row.Cells[1].Text;
        string pid ="206";

        string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            string acceptedCylinder = GetAcceptedCylinder(pid);
            if (chk.Checked)
            {
                if (acceptedCylinder == "[]")
                {
                    using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='206'", con))
                    {
                        cmd.Parameters.AddWithValue("@cylinders", "['" + number.Trim() + "']");
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                    ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('Record Inserted successfully.');", true);
                }
                else
                {
                    List<string> cylinderNo = acceptedCylinder.Replace("[", "").Replace("]", "").Replace("'", "").Split(',').ToList();
                    cylinderNo.Add(number);
                    using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='206'", con))
                    {
                        cmd.Parameters.AddWithValue("@cylinders", "['" + string.Join("','", cylinderNo.Distinct()) + "']");
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                    ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('Record Updated Successfully.');", true);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(acceptedCylinder))
                {
                    List<string> cylinderNo = acceptedCylinder.Replace("[", "").Replace("]", "").Replace("'", "").Split(',').ToList();
                    cylinderNo.Remove(number);
                    using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='206' ", con))
                    {
                        cmd.Parameters.AddWithValue("@cylinders", cylinderNo.Distinct().Count() > 0 ? "['" + string.Join("','", cylinderNo.Distinct()) + "']" : "[]");
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                    ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('Record Deleted Successfully.');", true);
                }
            }
        }
    }

    private string GetAcceptedCylinder(string pid)
    {
        string acceptCylinder = "";
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "SELECT cylinders FROM issuesheet WHERE pid=@pid ";
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@pid", 206);
                con.Open();
                acceptCylinder = Convert.ToString(cmd.ExecuteScalar());
                con.Close();
            }
        }
        return acceptCylinder;
    }   
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 11, 2021 12:20 AM

Hi lingers,

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView ID="avDetails" runat="server" AutoGenerateColumns="False" Font-Names="Candara"
    Font-Size="8px" DataKeyNames="pid" Width="100%" Style="margin-bottom: 0px"
    CssClass="grid" CellPadding="2" ForeColor="#333333" GridLines="None" Font-Bold="True"
    OnRowDataBound="OnRowDataBound">
    <AlternatingRowStyle BackColor="White" />
    <Columns>
        <asp:BoundField DataField="id" HeaderText="ID" />
        <asp:BoundField DataField="posino" HeaderText="Posino" />
        <asp:BoundField DataField="pid" HeaderText="PID" />
        <asp:BoundField DataField="diameter" HeaderText="DIAMETER" />
        <asp:TemplateField HeaderText="USE">
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <EditRowStyle BackColor="#2461BF" />
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#EFF3FB" />
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#F5F7FB" />
    <SortedAscendingHeaderStyle BackColor="#6D95E1" />
    <SortedDescendingCellStyle BackColor="#E9EBEF" />
    <SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindUserDetails1();
    }
}

protected void BindUserDetails1()
{
    string constr1 = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con1 = new SqlConnection(constr1))
    {
        using (SqlDataAdapter sda1 = new SqlDataAdapter("SELECT * from cylinder", con1))
        {

            using (DataTable dt1 = new DataTable())
            {
                sda1.Fill(dt1);
                avDetails.DataSource = dt1;
                avDetails.DataSource = dt1;
                avDetails.DataBind();
            }
        }
    }
}

private string GetAcceptedCylinder(string pid)
{
    string acceptCylinder = "";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT cylinders FROM issuesheet WHERE pid=@pid ";
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@pid", pid);
            con.Open();
            acceptCylinder = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
        }
    }

    return acceptCylinder;
}

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        string id = e.Row.Cells[2].Text;
        string[] cylinders = GetAcceptedCylinder(id).Replace("[", "").Replace("]", "").Split(',');
        CheckBox chkSelect = e.Row.FindControl("chkSelect") as CheckBox;
        if (cylinders.Contains("'" + e.Row.Cells[1].Text + "'"))
        {
            chkSelect.Checked = true;
        }
        else
        {
            chkSelect.Checked = false;
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindUserDetails1()
    End If
End Sub

Protected Sub BindUserDetails1()
    Dim constr1 As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con1 As SqlConnection = New SqlConnection(constr1)
        Using sda1 As SqlDataAdapter = New SqlDataAdapter("SELECT * from cylinder", con1)
            Using dt1 As DataTable = New DataTable()
                sda1.Fill(dt1)
                avDetails.DataSource = dt1
                avDetails.DataSource = dt1
                avDetails.DataBind()
            End Using
        End Using
    End Using
End Sub

Private Function GetAcceptedCylinder(ByVal pid As String) As String
    Dim acceptCylinder As String = ""
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As SqlConnection = New SqlConnection(constr)

        Using cmd As SqlCommand = New SqlCommand()
            cmd.CommandText = "SELECT cylinders FROM issuesheet WHERE pid=@pid "
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@pid", pid)
            con.Open()
            acceptCylinder = Convert.ToString(cmd.ExecuteScalar())
            con.Close()
        End Using
    End Using

    Return acceptCylinder
End Function

Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim id As String = e.Row.Cells(2).Text
        Dim cylinders As String() = GetAcceptedCylinder(id).Replace("[", "").Replace("]", "").Split(","c)
        Dim chkSelect As CheckBox = TryCast(e.Row.FindControl("chkSelect"), CheckBox)

        If cylinders.Contains("'" & e.Row.Cells(1).Text & "'") Then
            chkSelect.Checked = True
        Else
            chkSelect.Checked = False
        End If
    End If
End Sub

Screenshot