Reorder ListBox items and save in database using C# and VB.Net in ASP.Net

Hi gabylopez,

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

Database

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

<div class="container">
    <asp:ListBox ID="lstLocations" runat="server" CssClass="form-control" Height="180px" Width="180px"
        SelectionMode="Multiple"></asp:ListBox>
    <br />
    <asp:Button ID="btnUp" runat="server" Text="UP" OnClick="btnUp_Click" CssClass="btn btn-primary" />
    <asp:Button ID="btnDown" runat="server" Text="DOWN" OnClick="btnDown_Click" CssClass="btn btn-primary" />
    <br />
    <br />
    <asp:Button Text="Update Preference" runat="server" OnClick="OnUpdate" CssClass="btn btn-success" />
</div>

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)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "SELECT * FROM HolidayLocations ORDER BY Preference";
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand(query);
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                lstLocations.DataSource = dt;
                lstLocations.DataTextField = "Location";
                lstLocations.DataValueField = "Id";
                lstLocations.DataBind();
            }
        }
    }
}
protected void btnUp_Click(object sender, EventArgs e)
{
    if (lstLocations.SelectedIndex != -1)
    {
        int selectedItemIndex = lstLocations.SelectedIndex;
        List<ListItem> selectedItems = new List<ListItem>();
        int repositionIndex = 0;

        foreach (ListItem item in lstLocations.Items)
        {
            if (item.Selected)
            {
                selectedItems.Add(item);
            }
        }
        repositionIndex = selectedItemIndex - 1;
        foreach (ListItem item in selectedItems)
        {
            lstLocations.Items.Remove(item);
            if (selectedItemIndex != 0)
            {
                lstLocations.Items.Insert(repositionIndex, item);
            }
            repositionIndex++;
        }
        if (selectedItemIndex == 0)
        {
            repositionIndex = lstLocations.Items.Count;
            foreach (ListItem item in selectedItems)
            {
                lstLocations.Items.Insert(repositionIndex, item);
                repositionIndex++;
            }
        }
    }
}

protected void btnDown_Click(object sender, EventArgs e)
{
    if (lstLocations.SelectedIndex != -1)
    {
        int selectedItemIndex = lstLocations.SelectedIndex;
        List<ListItem> selectedItems = new List<ListItem>();
        int repositionIndex = 0;
        bool isContainLastElement = false;
        foreach (ListItem item in lstLocations.Items)
        {
            if (item.Selected)
            {
                if (lstLocations.Items.IndexOf(item) == lstLocations.Items.Count - 1)
                {
                    isContainLastElement = true;
                }
                selectedItems.Add(item);
            }
        }
        repositionIndex = selectedItemIndex + 1;
        foreach (ListItem item in selectedItems)
        {
            lstLocations.Items.Remove(item);
        }

        repositionIndex = isContainLastElement ? 0 : repositionIndex;
        foreach (ListItem item in selectedItems)
        {
            lstLocations.Items.Insert(repositionIndex, item);
            repositionIndex++;
        }
    }
}

protected void OnUpdate(object sender, EventArgs e)
{
    int preference = 1;
    foreach (ListItem item in lstLocations.Items)
    {
        this.UpdatePreference(Convert.ToInt32(item.Value), preference);
        preference += 1;
    }

    Response.Redirect(Request.Url.AbsoluteUri);
}

private void UpdatePreference(int locationId, int preference)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE HolidayLocations SET Preference = @Preference WHERE Id = @Id"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@Id", locationId);
                cmd.Parameters.AddWithValue("@Preference", preference);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT * FROM HolidayLocations ORDER BY Preference"
        Using con As SqlConnection = New SqlConnection(conString)
            Dim cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                lstLocations.DataSource = dt
                lstLocations.DataTextField = "Location"
                lstLocations.DataValueField = "Id"
                lstLocations.DataBind()
            End Using
        End Using
    End If
End Sub

Protected Sub btnUp_Click(ByVal sender As Object, ByVal e As EventArgs)
    If lstLocations.SelectedIndex <> -1 Then
        Dim selectedItemIndex As Integer = lstLocations.SelectedIndex
        Dim selectedItems As List(Of ListItem) = New List(Of ListItem)()
        Dim repositionIndex As Integer = 0
        For Each item As ListItem In lstLocations.Items
            If item.Selected Then
                selectedItems.Add(item)
            End If
        Next
        repositionIndex = selectedItemIndex - 1
        For Each item As ListItem In selectedItems
            lstLocations.Items.Remove(item)
            If selectedItemIndex <> 0 Then
                lstLocations.Items.Insert(repositionIndex, item)
            End If
            repositionIndex += 1
        Next
        If selectedItemIndex = 0 Then
            repositionIndex = lstLocations.Items.Count
            For Each item As ListItem In selectedItems
                lstLocations.Items.Insert(repositionIndex, item)
                repositionIndex += 1
            Next
        End If
    End If
End Sub

Protected Sub btnDown_Click(ByVal sender As Object, ByVal e As EventArgs)
    If lstLocations.SelectedIndex <> -1 Then
        Dim selectedItemIndex As Integer = lstLocations.SelectedIndex
        Dim selectedItems As List(Of ListItem) = New List(Of ListItem)()
        Dim repositionIndex As Integer = 0
        Dim isContainLastElement As Boolean = False
        For Each item As ListItem In lstLocations.Items
            If item.Selected Then
                If lstLocations.Items.IndexOf(item) = lstLocations.Items.Count - 1 Then
                    isContainLastElement = True
                End If
                selectedItems.Add(item)
            End If
        Next

        repositionIndex = selectedItemIndex + 1
        For Each item As ListItem In selectedItems
            lstLocations.Items.Remove(item)
        Next

        repositionIndex = If(isContainLastElement, 0, repositionIndex)
        For Each item As ListItem In selectedItems
            lstLocations.Items.Insert(repositionIndex, item)
            repositionIndex += 1
        Next
    End If
End Sub

Protected Sub OnUpdate(ByVal sender As Object, ByVal e As EventArgs)
    Dim preference As Integer = 1
    For Each item As ListItem In lstLocations.Items
        Me.UpdatePreference(Convert.ToInt32(item.Value), preference)
        preference += 1
    Next

    Response.Redirect(Request.Url.AbsoluteUri)
End Sub

Private Sub UpdatePreference(ByVal locationId As Integer, ByVal preference As Integer)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("UPDATE HolidayLocations SET Preference = @Preference WHERE Id = @Id")
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@Id", locationId)
                cmd.Parameters.AddWithValue("@Preference", preference)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Using
End Subc

Screenshot

Database record after update