ASP.Net: Update Child Id by Parent Id using C# and VB.Net

Hi Firuz,

Please refer below sample.

Database

CREATE TABLE Collection
(
    [id] INT,
    [type] VARCHAR(20),
    [parent] INT
)
INSERT INTO Collection VALUES (0,'collection',Null);
INSERT INTO Collection VALUES (1,'collection',0);
INSERT INTO Collection VALUES (2,'collection',1);
INSERT INTO Collection VALUES (3,'product',4);
INSERT INTO Collection VALUES (4,'collection',0);
INSERT INTO Collection VALUES (5,'product',2);
INSERT INTO Collection VALUES (6,'product',4);

HTML

<asp:GridView ID="gvCollection" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Type" HeaderText="Type" />
        <asp:BoundField DataField="Parent" HeaderText="Parent" />
    </Columns>
</asp:GridView>
<table>
    <tr>
        <td>Id:</td>
        <td>
            <asp:TextBox ID="txtId" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>IdUpdate:</td>
        <td>
            <asp:TextBox ID="txtIdUpdate" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td colspan="2" style="text-align:center">
            <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="Update" />
        </td>
    </tr>
</table>

Namespaces

C#

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

VB.Net

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

Code

C#

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

protected void Update(object sender, EventArgs e)
{
    int id = int.Parse(txtId.Text);
    int idUpdate = int.Parse(txtIdUpdate.Text);
    string query = "UPDATE Collection SET Id = @Parent WHERE Id = @Id";
    query += " UPDATE Collection SET Parent = @Parent WHERE Parent = @Id";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@Parent", idUpdate);
            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 Id, Type, Parent FROM Collection", con))
        {
            con.Open();
            gvCollection.DataSource = cmd.ExecuteReader();
            gvCollection.DataBind();
            con.Close();
        }
    }
}

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 Update(ByVal sender As Object, ByVal e As EventArgs)
    Dim id As Integer = Integer.Parse(txtId.Text)
    Dim idUpdate As Integer = Integer.Parse(txtIdUpdate.Text)
    Dim query As String = "UPDATE Collection SET Id = @Parent WHERE Id = @Id"
    query += " UPDATE Collection SET Parent = @Parent WHERE Parent = @Id"
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Id", id)
            cmd.Parameters.AddWithValue("@Parent", idUpdate)
            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 Id, Type, Parent FROM Collection", con)
            con.Open()
            gvCollection.DataSource = cmd.ExecuteReader()
            gvCollection.DataBind()
            con.Close()
        End Using
    End Using
End Sub