Update Null value in database when ASP.Net DropDownList is empty using C# and VB.Net

kankon
 
on Jan 18, 2022 12:01 AM
2179 Views

hello,

When i change user group name from DropDownList then update it in SQL DB there is 2 groups

Second DropDownList user if nothing NULL saves in SQL DB.

<asp:GridView ID="GridView1" runat="server" AlternatingRowStyle-BackColor="white"
    AutoGenerateColumns="False"
    CssClass="Grid" Font-Names="Arial" Font-Size="11pt" DataKeyNames="Id" ShowHeaderWhenEmpty="True"
    Style="font-size: medium" Width="100%" OnRowDataBound="OnRowDataBound" BackColor="White"
    BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal">
    <AlternatingRowStyle BackColor="#CCCCCC"></AlternatingRowStyle>
    <Columns>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:CheckBox ID="checkAll" runat="server" Checked="True" onclick="checkAll(this);" />
            </HeaderTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="CheckBox1" runat="server" onclick="Check_Click(this)" Checked='<%# Eval("IsSelected2") %>' />
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" Width="5px" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Groups" ItemStyle-Width="120">
            <ItemTemplate>
                <asp:DropDownList ID="ddlCountries" runat="server" AutoPostBack="true" OnSelectedIndexChanged1="SelectedIndexChanged" SelectedValue='<%# Eval("groups") %>'>
                    <asp:ListItem></asp:ListItem>
                    <asp:ListItem Text="الاشرافيين" Value="الاشرافيين"></asp:ListItem>
                    <asp:ListItem Text="المالى" Value="section2"></asp:ListItem>
                    <asp:ListItem Text="السكرتارية" Value="section3"></asp:ListItem>
                    <asp:ListItem>الشئون الهندسية أ</asp:ListItem>
                    <asp:ListItem Text="الشئون الهندسية ب" Value="الشئون الهندسية ب"></asp:ListItem>
                    <asp:ListItem>النقليات أ</asp:ListItem>
                    <asp:ListItem>النقليات ب</asp:ListItem>
                    <asp:ListItem>التغذية أ</asp:ListItem>
                    <asp:ListItem>التغذية ب</asp:ListItem>
                    <asp:ListItem>الأمن-والحراسة أ</asp:ListItem>
                    <asp:ListItem>الأمن-والحراسة ب</asp:ListItem>
                    <asp:ListItem>الصيانه أ</asp:ListItem>
                    <asp:ListItem>الصيانه ب</asp:ListItem>
                    <asp:ListItem>النظافة-والزراعة أ</asp:ListItem>
                    <asp:ListItem>النظافة-والزراعة ب</asp:ListItem>
                    <asp:ListItem>إعفاء طبي</asp:ListItem>
                    <asp:ListItem>التجمع الرابع أ</asp:ListItem>
                    <asp:ListItem>التجمع الرابع ب</asp:ListItem>
                    <asp:ListItem>التجمع الاول أ</asp:ListItem>
                    <asp:ListItem>التجمع الاول ب</asp:ListItem>
                    <asp:ListItem>التجمع الثالث أ</asp:ListItem>
                    <asp:ListItem>التجمع الثالث ب</asp:ListItem>
                </asp:DropDownList>
                <asp:DropDownList ID="ddlCountries2" runat="server" AutoPostBack="true" OnSelectedIndexChanged2="SelectedIndexChanged" SelectedValue='<%# Eval("groups2") %>'>
                    <asp:ListItem></asp:ListItem>
                    <asp:ListItem Text="الاشرافيين" Value="الاشرافيين"></asp:ListItem>
                    <asp:ListItem Text="المالى" Value="section2"></asp:ListItem>
                    <asp:ListItem Text="السكرتارية" Value="section3"></asp:ListItem>
                    <asp:ListItem>الشئون الهندسية أ</asp:ListItem>
                    <asp:ListItem Text="الشئون الهندسية ب" Value="الشئون الهندسية ب"></asp:ListItem>
                    <asp:ListItem>النقليات أ</asp:ListItem>
                    <asp:ListItem>النقليات ب</asp:ListItem>
                    <asp:ListItem>التغذية أ</asp:ListItem>
                    <asp:ListItem>التغذية ب</asp:ListItem>
                    <asp:ListItem>الأمن-والحراسة أ</asp:ListItem>
                    <asp:ListItem>الأمن-والحراسة ب</asp:ListItem>
                    <asp:ListItem>الصيانه أ</asp:ListItem>
                    <asp:ListItem>الصيانه ب</asp:ListItem>
                    <asp:ListItem>النظافة-والزراعة أ</asp:ListItem>
                    <asp:ListItem>النظافة-والزراعة ب</asp:ListItem>
                    <asp:ListItem>إعفاء طبي</asp:ListItem>
                    <asp:ListItem>التجمع الرابع أ</asp:ListItem>
                    <asp:ListItem>التجمع الرابع ب</asp:ListItem>
                    <asp:ListItem>التجمع الاول أ</asp:ListItem>
                    <asp:ListItem>التجمع الاول ب</asp:ListItem>
                    <asp:ListItem>التجمع الثالث أ</asp:ListItem>
                    <asp:ListItem>التجمع الثالث ب</asp:ListItem>
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ملاحظات">
            <ItemTemplate>
                <asp:Label ID="neworderdatatxt" Text='<%# Eval("neworderdata2") %>' runat="server" ForeColor="Red" Font-Size="Medium" />
                <br />
                <asp:Label ID="notemedicaltxt" Text='<%# Eval("notemedical") %>' runat="server" />
                <br />
                <asp:Label ID="LabelNamefile2" Text='<%# Eval("Namefile2") %>' runat="server" Visible="False" />
                <asp:LinkButton ID="lnkDownload2" runat="server" Text="المرفقات" OnClick="DownloadFile2"
                                CommandArgument='<%# Eval("Id") %>' Visible='<%# !string.IsNullOrEmpty(Eval("Namefile2").ToString()) ? true : false %>'></asp:LinkButton>
            </ItemTemplate>
            <ItemStyle BackColor="White" Width="100px" HorizontalAlign="Center"></ItemStyle>
        </asp:TemplateField>
        <asp:BoundField DataField="blockrasme">
            <ItemStyle BackColor="White" Font-Size="2pt" ForeColor="White" Width="2px" />
        </asp:BoundField>
    </Columns>
    <EmptyDataTemplate>
        <div align="center">
            لم يتم العثور على أي سجلات.
        </div>
    </EmptyDataTemplate>
</asp:GridView>
Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Jan 20, 2022 03:46 AM

Hi Kankon,

Please refer below Sample.

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:DropDownList ID="ddlCountries" runat="server">
                    <asp:ListItem Text="" Value="0"></asp:ListItem>
                    <asp:ListItem Text="United States" Value="United States"></asp:ListItem>
                    <asp:ListItem Text="India" Value="India"></asp:ListItem>
                    <asp:ListItem Text="France" Value="France"></asp:ListItem>
                    <asp:ListItem Text="Russia" Value="Russia"></asp:ListItem>
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button Text="Update" ID="btnUpdate" runat="server" OnClick="Update" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Namespaces

C#

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

VB.Net

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

Code

C# 

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

private void BindGrid()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name From Customers", con))
        {
            cmd.CommandType = CommandType.Text;
            con.Open();
            this.gvCustomers.DataSource = cmd.ExecuteReader();
            this.gvCustomers.DataBind();
            con.Close();
        }
    }
}

protected void Update(object sender, EventArgs e)
{
    GridViewRow gvRow = (sender as Button).NamingContainer as GridViewRow;
    int id = int.Parse(gvRow.Cells[0].Text);
    string country = (gvRow.FindControl("ddlCountries") as DropDownList).SelectedItem.Text;

    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Country = @Country WHERE CustomerId = @Id", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", id);
            if (!string.IsNullOrEmpty(country))
            {
                cmd.Parameters.AddWithValue("@Country", country);
            }
            else
            {
                cmd.Parameters.AddWithValue("@Country", DBNull.Value);
            }
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    this.BindGrid();
}

VB.Net 

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

Private Sub BindGrid()
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, Name From Customers", con)
            cmd.CommandType = CommandType.Text
            con.Open()
            Me.gvCustomers.DataSource = cmd.ExecuteReader()
            Me.gvCustomers.DataBind()
            con.Close()
        End Using
    End Using
End Sub

Protected Sub Update(ByVal sender As Object, ByVal e As EventArgs)
    Dim gvRow As GridViewRow = TryCast((TryCast(sender, Button)).NamingContainer, GridViewRow)
    Dim id As Integer = Integer.Parse(gvRow.Cells(0).Text)
    Dim country As String = (TryCast(gvRow.FindControl("ddlCountries"), DropDownList)).SelectedItem.Text
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("UPDATE Customers SET Country = @Country WHERE CustomerId = @Id", con)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Id", id)
            If Not String.IsNullOrEmpty(country) Then
                cmd.Parameters.AddWithValue("@Country", country)
            Else
                cmd.Parameters.AddWithValue("@Country", DBNull.Value)
            End If

            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Me.BindGrid()
End Sub

Screenshot

Output