Update Column value based on Id using C# and VB.Net in ASP.Net

mahesh213
 
on Oct 02, 2021 12:24 AM
Sample_180081.zip
527 Views

Hi,

I have one table

Employee

EmpId   EmpName       CountryId's

1                  aa            1,2     

2                  bb             2,3

I have one method

public void Save()
{
    string s = "1,3";//coming from UI
    int empId = 2;   
}

currently my requirement is that based upon empId need to update values in database

my exact requirement after doing operation

Employee

EmpId   EmpName       CountryId's

1                  aa            1,2     

2                  bb             1,3

can you please help me

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Oct 02, 2021 01:45 AM

hi Mahesh, 

Please refer below sample.

HTML

<table>
    <tr>
        <td>EmpId:</td>
        <td>
            <asp:TextBox runat="server" ID="txtEmpId"></asp:TextBox></td>
        <td></td>
    </tr>
    <tr>
        <td>CountryId's:</td>
        <td>
            <asp:TextBox runat="server" ID="txtCountryId"></asp:TextBox></td>
        <td></td>
    </tr>
    <tr>
        <td>
            <asp:Button runat="server" ID="btnUpdate" Text="Update" OnClick="OnUpdate"/></td>
    </tr>
</table>
<hr />
<asp:GridView runat="server" ID="gvEmployeees" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="Employee ID" DataField="EmployeeId" />
        <asp:BoundField HeaderText="Employee Name" DataField="EmployeeName" />
        <asp:BoundField HeaderText="Employee Country ID" DataField="CountryId" />
    </Columns>
</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 (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

protected void OnUpdate(object sender, EventArgs e)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE Employees SET CountryId=@CountryID WHERE EmployeeId =@ID", con))
        {
            cmd.Parameters.AddWithValue("@CountryID", txtCountryId.Text);
            cmd.Parameters.AddWithValue("@ID", txtEmpId.Text);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    this.BindGrid();
}

private void BindGrid()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT EmployeeId, EmployeeName, CountryId FROM Employees", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvEmployeees.DataSource = dt;
                    gvEmployeees.DataBind();
                }
            }
        }
    }
}

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

    Protected Sub OnUpdate(ByVal sender As Object, ByVal e As EventArgs)
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Using cmd As SqlCommand = New SqlCommand("UPDATE Employees SET CountryId=@CountryID WHERE EmployeeId =@ID", con)
                cmd.Parameters.AddWithValue("@CountryID", txtCountryId.Text)
                cmd.Parameters.AddWithValue("@ID", txtEmpId.Text)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using

        Me.BindGrid()
    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 EmployeeId, EmployeeName, CountryId FROM Employees", con)
                Using sda As SqlDataAdapter = New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        gvEmployeees.DataSource = dt
                        gvEmployeees.DataBind()
                    End Using
                End Using
            End Using
        End Using
    End Sub

Screenshot