How to update listbox after inserting new row

Question

HI all

Im trying to get my from to insert new information into a database and display it. right now the information inputted goes into the database file. but the lstbox i have on my form displaying the current records doesnt get updated.

here is the coding for the button:

string dbconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
            OleDbConnection con = new OleDbConnection(dbconnection);
            OleDbCommand cmd = new OleDbCommand("INSERT INTO Books(BookKey,Title,Pages,AuthorKey,PublisherID) VALUES (?,?,?,?,?)", con);

            cmd.Parameters.Add("@BookKey", OleDbType.VarChar, 50, "BookKey").Value = textBox1.Text;
            cmd.Parameters.Add("@Title", OleDbType.VarChar).Value = textBox2.Text;
            cmd.Parameters.Add("@Pages", OleDbType.VarChar).Value = textBox3.Text;
            cmd.Parameters.Add("@AuthorKey", OleDbType.VarChar).Value = textBox4.Text;
            cmd.Parameters.Add("@PubliherID", OleDbType.VarChar).Value = textBox5.Text;

            con.Open();
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            con.Close();

            refreshlist();

Hope someone can help

Regards

John · Answer

the reason for the primary key is for table relationship. My main Table has the foreign key for a column (AuthorKey) and my second table had its primary. Same name and datatype I dont know if im being to complicated trying to do it the way i want with just one textbox in input the author key I was looking into autoincrement but unsure on how to implement it

Suthish Nair · Answer

John What is the importance of Primary Key in a table? If you want a fast and simple solution then set the AuthorKey as identity column and do auto increment by one. Every time a new number/key will get saved. If you going for the the other way then you need to do a select query to check whether AuthorKey already exists or not before actual insert command. If exists then update the records or else check for the max value + 1 of AuthorKey then insert new ones.

John · Answer

Hi Guys I have a slight problem which ive just relized. I have 2 tables in which one has a foriegn key and the other with a primary key. Both are called AuthorKey and both are numbers(not auto). Just noticed that if i entered a key which is already in the table with the primary key it appears to show an error. is there any way round this? this is my new code: string dbconnection = Provider=Microsoft.Jet.OLEDB.4.0; + @data source=Book.mdb; OleDbConnection con = new OleDbConnection(dbconnection); OleDbCommand com = new OleDbCommand(); OleDbCommand com2 = new OleDbCommand(); com.Connection = con; com2.Connection = con; con.Open(); //tell the compiler and database that we're using parameters com.CommandText = (INSERT INTO Books (BookKey Title PagesAuthorKey PublisherID) VALUES (@Book @Title @page@AuthorKey @PublisherID)); com2.CommandText = (INSERT INTO Authors (First_Name Surname AuthorKey) VALUES (@First_Name @Surname @AuthorKey)); //add our parameters to our command object com.Parameters.AddWithValue(@Book Book); com.Parameters.AddWithValue(@Title Title); com.Parameters.AddWithValue(@page Page); com.Parameters.AddWithValue(@Authorkey AuthorKey); com2.Parameters.AddWithValue(@Authorkey AuthorKey); com.Parameters.AddWithValue(@PublisherID PublisherID); com2.Parameters.AddWithValue(@First_Name First_Name); com2.Parameters.AddWithValue(@Surname Surname); com.ExecuteNonQuery(); com2.ExecuteNonQuery(); con.Close();

Crish · Answer

Hi You can refer this following link to solve the issue of How to update listbox after inserting new row. Example: using System; using System.Drawing; using System.Data; using System.Windows.Forms; public class AdoTest : Form { DataSet _data = new DataSet(); AdoTest() { DataTable order = _data.Tables.Add(order); DataColumn order_id = order.Columns.Add(id typeof (int)); // add more fields later for example customer foreign key order.PrimaryKey = new DataColumn[] { order_id }; DataTable item = _data.Tables.Add(item); DataColumn item_id = item.Columns.Add(id typeof (int)); item.Columns.Add(description typeof (string)); item.PrimaryKey = new DataColumn[] { item_id }; DataTable link = _data.Tables.Add(link); DataColumn link_order = link.Columns.Add(order typeof (int)); DataColumn link_item = link.Columns.Add(item typeof (int)); link.PrimaryKey = new DataColumn[] { link_order link_item }; _data.Relations.Add(order_link order_id link_order); _data.Relations.Add(item_link item_id link_item); link.Columns.Add(description typeof (string) Parent(item_link).description); for (int n = 0; n order.Rows.Add(new object[] { n }); } item.Rows.Add(new object[] { 0 stuff }); item.Rows.Add(new object[] { 1 other stuff }); item.Rows.Add(new object[] { 2 more stuff }); item.Rows.Add(new object[] { 3 useless junk }); link.Rows.Add(new object[] { 0 1 }); link.Rows.Add(new object[] { 0 3 }); link.Rows.Add(new object[] { 1 1 }); link.Rows.Add(new object[] { 1 2 }); link.Rows.Add(new object[] { 2 2 }); link.Rows.Add(new object[] { 3 2 }); link.Rows.Add(new object[] { 3 3 }); link.Rows.Add(new object[] { 4 1 }); link.Rows.Add(new object[] { 4 2 }); Label order_label = new Label(); order_label.Text = Order; order_label.Location = new Point(8 8); order_label.Size = new Size(72 16); ComboBox order_list = new ComboBox(); order_list.DataSource = _data; order_list.DisplayMember = order.id; order_list.Location = new Point(80 8); order_list.Size = new Size(192 21); Label item_label = new Label(); item_label.Text = Items in order; item_label.Location = new Point(8 48); item_label.Size = new Size(100 16); ListBox item_list = new ListBox(); item_list.DataSource = _data; item_list.DisplayMember = order.order_link.description; item_list.Location = new Point(8 72); item_list.Size = new Size(272 95); Button test_button = new Button(); test_button.Text = Add item to order 0; test_button.Location = new Point(144 176); test_button.Size = new Size(136 23); test_button.Click += new EventHandler(AddItemTest); SuspendLayout(); ClientSize = new Size(288 206); Controls.Add(order_label); Controls.Add(order_list); Controls.Add(item_label); Controls.Add(item_list); Controls.Add(test_button); ResumeLayout(false); } static void Main() { Application.Run(new AdoTest()); } void AddItemTest(object sender System.EventArgs e) { _data.Tables[link].Rows.Add(new object[] { 0 0 }); } } If you want to more detail about this code just go through this below link. http://www.dotnetmonster.com/Uwe/Forum.aspx/winform-data-binding/3/Data-bound-list-box-displays-empty-item-after-adding

Mahesh Chand · Answer

Put this code in RefreshList. What this will do it will connect to database everytime you refreshes the list and get the latest data. Also make sure your changes are being updated in the database. string dbconnection = Provider=Microsoft.Jet.OLEDB.4.0; + @data source=Book.mdb; OleDbConnection con = new OleDbConnection(dbconnection); OleDbCommand cmd = new OleDbCommand(INSERT INTO Books(BookKeyTitlePagesAuthorKeyPublisherID) VALUES (?????) con); cmd.Parameters.Add(@BookKey OleDbType.VarChar 50 BookKey).Value = textBox1.Text; cmd.Parameters.Add(@Title OleDbType.VarChar).Value = textBox2.Text; cmd.Parameters.Add(@Pages OleDbType.VarChar).Value = textBox3.Text; cmd.Parameters.Add(@AuthorKey OleDbType.VarChar).Value = textBox4.Text; cmd.Parameters.Add(@PubliherID OleDbType.VarChar).Value = textBox5.Text; con.Open(); cmd.Connection = con; cmd.ExecuteNonQuery(); con.Close(); Alternative : The better approach is when you add an item add to the database and add to the List in the code. There is no need to get the data from the database if is being added by the same user.

Sam Hobbs · Answer

Yes you are more specific in that and I assume that you edited that specific message since I looked at it quite closely and did not see the clarification. I probably opened the thread for viewing moments before you edited it. I looked closely at that message because I was trying to decide if I should post a clarification to Shahan about shot; Shahan might not understand that shot means try as in give it a try. I also looked closely at your comment because I was trying to figure out the comments about midnight. I assume it is now about 6:30 AM in India and 1:00 AM in the UK so Shahan will likely be starting his day soon. It is about 5 PM for me. I would help you but I have certainly spent a large share of time today attempting to help others.

John · Answer

i was a little more specific in my post after that

Sam Hobbs · Answer

John whenever we say something such as Doesn't like it or Doesn't work it is usually necessary to be more specific. If you can add a clarification before ending your day then that will give others time whose day is just beginning to help you.

John · Answer

Sorry Lol Midnight here too. Ive add the database im working with here. im getting Number of query values and destination fields are not the same. Theres Two Tables One has a primary key and the other has a the foriegn key which is AuthorKey

Shahan Ayyub · Answer

Hmmm Whats' hurt you ??? Its a midnight time here ??? reply me then we will see what can be be done. I can upload the project as well.

John · Answer

Just gave it a shot. Doesnt like it :(

Shahan Ayyub · Answer

Hi! This is your Insert Button code: string dbconnection = @Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Shahan\Documents\Visual Studio 2008\Projects\UpdateListview\UpdateListview\bin\Debug\LibraryDB.mdb; string dbcommand = insert into Authors values(45'dsadsdasd'1002'sdsad''sdsad') ; OleDbConnection con = new OleDbConnection(dbconnection); OleDbCommand com = new OleDbCommand(dbcommand con); con.Open(); com.ExecuteNonQuery(); con.Close(); dbcommand = Select BookKey Title Pages PublisherID  Books.AuthorKey First_Name Surname FROM Books Authors WHERE Authors.AuthorKey = Books.AuthorKey ORDER BY BookKey; con = new OleDbConnection(dbconnection); com = new OleDbCommand(dbcommand con); con.Open(); apt = new OleDbDataAdapter(com); setdata.Clear(); apt.Fill(setdata); con.Close(); dataTable = setdata.Tables[0]; listBox1.Items.Clear(); foreach (DataRow row in dataTable.Rows) { listBox1.Items.Add(row[BookKey] +  |  + row[title] +  |  + row[pages]); } Here I execute insert query then execute select query in on the updated results then populate the Listbox. regards -Shahan

John · Answer

this is the whole code

John · Answer

in the refreshlist() . it basically acts as my start button and retrives the information from the database to display in the list box string dbconnection = Provider=Microsoft.Jet.OLEDB.4.0; + @data source=Book.mdb; string dbcommand = Select BookKey Title Pages PublisherID  Books.AuthorKey First_Name Surname FROM Books Authors WHERE Authors.AuthorKey = Books.AuthorKey ORDER BY BookKey; OleDbConnection con = new OleDbConnection(dbconnection); OleDbCommand com = new OleDbCommand(dbcommand con); apt = new OleDbDataAdapter(com); setdata.Clear(); con.Open(); apt.Fill(setdata); con.Close(); dataTable = setdata.Tables[0]; listBox1.Items.Clear(); foreach (DataRow row in dataTable.Rows) { listBox1.Items.Add(row[BookKey] +  |  + row[title] +  |  + row[pages]); }

Suthish Nair · Answer

You needs to rebind the control with new records from DB. How you refreshing the control