How to update listbox after inserting new row

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

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.

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, Pages,AuthorKey, 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();

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 < 5; ++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

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(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();

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.

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.

i was a little more specific in my post after that

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

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.

Just gave it a shot. Doesnt like it :(

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(4,5,'dsadsdasd',100,2,'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

this is the whole code

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"]);
            }
           
                  

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