Save listbox items in database after ordering

Question

I have an asp.net page with two listboxes and a save button. The user needs to transfer items from listbox1 to listbox2 according to his order I mean that user can choose witch item come in the first place and which come next.

After that my system suggest the results (most commonly) that achieve the ordered items, I hope you understand

I did the transfer method between the two listboxes and it work good. But I'm stuck in the save method, it can save the items after being in listbox2 but not in the same order of transferring (user order).

Here is my code for save button:

protected void Button1_Click(object sender, EventArgs e)
{
    string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
    string str;

    OracleCommand com;
    OracleConnection con = new OracleConnection(strConnString);

    try
    {
        con.Open();

        for (int i = 0; i < ListBox2.Items.Count; i++)
        {
            str = "insert into Criteria values('" + ListBox2.Items[i].ToString() + "')";
            com = new OracleCommand(str, con);
            com.ExecuteNonQuery();
        }

        LabOut.Visible = true;
        LabOut.Text = "Upload succesful!";
        con.Close();
        //Response.Write("Inserted");
    }
    catch (Exception)
    {
        LabOut.Visible = true;
        LabOut.Text = "Upload failed!";
    }
    finally
    {
       con.Close();
       con.Dispose();
    }
}

It may seem like a small thing, but pay attention to spelling (especially in the title of your question) and to proper capitalization. You don't have a single upper-case letter in your question text. Attention to detail is an important asset for a developer. — Eric J.

John Wu John Wu · Accepted Answer · 2016-03-30T00:15:14

The problem here is that database tables do not inherently have a concept of order. Retrieving rows from a table never guarantees an order unless you use a order by clause.

You should add an additional column to your table (named, for example, "Sequence" or "Order"). It can be an int or some other sortable value. You would then need to modify your insert statement:

str = "insert into Criteria(ItemValue, Sequence) values('" + ListBox2.Items[i].ToString() + "'," + i.ToString() + ")";

and retrieve the list using

str = "Select ItemValue from Criteria order by Sequence"

Save listbox items in database after ordering

2 Answers