Hello,

I posted yesterday regarding my issues with adding rows to a database, this is now solved and I am grateful. Once the rows were added to the database the list box that displays one column of the database did not update so I included the following code after the addition had been made:

cnn.ConnectionString = "Data Source=FYPSERVER;Initial Catalog=FYP;Integrated Security=True";
cnn.Open();
da.Fill(fypDataSet, "category");
cnn.Close();

I assume this refilled the data adapter with the updated information and it worked, my list box and its values were updated.

However the same bit of code does not work after I delete a value from the database.
Here is my code:

delCat(lstCategories.Text); //this deletes the value from the database
lstCategories.SelectedIndex = -1;
cnn.ConnectionString = "Data Source=FYPSERVER;Initial Catalog=FYP;Integrated Security=True";
cnn.Open();
da.Fill(fypDataSet, "category");
cnn.Close();

The row is deleted from the database but it is not removed from the list box. If I close the form and re-open then the values are updated but they don't change until then, which is not very ideal.

I have done some seraching on here and in google but nothing made a difference.
I have tried the following but I don't really have a clue what I am doing:

lstCategories.Refresh(),
categoryBindingSource.ResetBindings(false), and
categoryBindingSource.ResetBindings(true).

I am using Visual Studio 2010 ultimate.

Thank you

Recommended Answers

All 7 Replies

Look at this code how it should be done:

public partial class Form1 : Form
    {
        List<string> list;
        BindingList<string> bindingList;
        public Form1()
        {
            InitializeComponent();
                               
            string[] array = new string[] { "A", "B", "C" };
            list = new List<string>(array);
            bindingList = new BindingList<string>(list);

            listBox1.DataSource = bindingList;
            listBox1.SelectedIndex = -1;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string item = listBox1.SelectedItem.ToString();
            bindingList.Remove(item);
        }
    }

Hi liam,
Here there is noprob in ur code. But the record is deleted in ur database but not in your listbox.
You have two options:
1. Delete the selected record from the listbox too.
2. Recall and assign the items from database to listbox.

And here is the example (better suitable to you) which using dataTable (dataSet) as dataSoucrce. Take a look how it has to be done. The code automatically removed the item from listBox (becuase it was removed from dataTable and its data bound):

public partial class Form1 : Form
    {
        BindingSource bs;
        DataSet ds;
        public Form1()
        {
            InitializeComponent();
                               
            string[] array = new string[] { "A", "B", "C" };
            ds = new DataSet();
            DataTable table = new DataTable("table1");
            table.Columns.Add(new DataColumn("letters", typeof(string)));
            DataRow dr;
            for (int i = 0; i < array.Length; i++)
            {
                dr = table.NewRow();
                dr["letters"] = array[i];
                table.Rows.Add(dr);
            }
            ds.Tables.Add(table);

            //set datasource:
            bs = new BindingSource();
            bs.DataSource = ds.Tables["table1"];
            listBox1.DataSource = bs;
            listBox1.DisplayMember = "letters";
            listBox1.ValueMember = listBox1.DisplayMember;
            listBox1.SelectedIndex = -1;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int rowIndex = listBox1.SelectedIndex;
            ds.Tables["table1"].Rows[rowIndex].Delete();
        }
    }

I hope it helps,
Mitja

Just watch in quick watch whether the record is deleted properly otherwise you will see an red color alert symbol in place of deleted row. If happens so just delete the row by using .removeAt method of datatable.

You can also try this step
Clear all item from list box and re add from database

Thank you all for your responses,
I have not tried them all as my time is limited (I have work soon), I stopped at the first one that worked
so my code may not be the most effecient.

For anyone that may be interested this is my now working code for the user selecting an item from a list box and then deleting that item from both the list box and the database.
Bear in mind that this is not fully tested:

string del = lstCategories.Text; //save the currently selected item in a variable
int rowIndex = lstCategories.SelectedIndex; //get the row index for deletion from the list
cnn.Open(); //open database connection
//removes the data from the list but has the unusual effect of not removing it from the actual database
fypDataSet.Tables["category"].Rows[rowIndex].Delete();
cnn.Close(); // close database connection
delCat(del); //a function removing the data from the database

I could not use the following on its own as that does not update the database, which was my first problem found here http://www.daniweb.com/forums/thread349494.html:

fypDataSet.Tables["category"].Rows[rowIndex].Delete();

So I combined Mitja Bonca code with my own :)

Thank you all very much

Anytime :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.