when should dataset reflect the updated rows count after an insert in vb.net

Question

i have a sql database, and have created a dataset for a sample table in it.

The dataset is HotelDataSet2 and data table adapter to insert to it is Various_itemsTableAdapter.

 MsgBox(HotelDataSet2.various_items.Rows.Count)
 Various_itemsTableAdapter.Insert(Item_nameTextBox.Text, itemcode, PackingTextBox.Text, UomTextBox.Text, PriceTextBox.Text, RemarksTextBox.Text, TaxTextBox.Text, StatusCheckBox.Checked, Rate_inclusiveCheckBox.Checked)
 MsgBox(HotelDataSet2.various_items.Rows.Count)

This always reflects the same count, before and after inserting..But if i execute this

MsgBox(HotelDataSet2.various_items.Rows.Count)
Various_itemsTableAdapter.Insert(Item_nameTextBox.Text, itemcode, PackingTextBox.Text, UomTextBox.Text, PriceTextBox.Text, RemarksTextBox.Text, TaxTextBox.Text, StatusCheckBox.Checked, Rate_inclusiveCheckBox.Checked)
Various_itemsTableAdapter.Fill(HotelDataSet2.various_items)
MsgBox(HotelDataSet2.various_items.Rows.Count)

it shows the new count to be +1 of the old one. So i concluded that everytime i change some data to the table via table adapter, i have to always refill the dataset?? How is that useful then??

Answer 1

You're going about this the wrong way. You should be using the DataSet to make your changes (ie, create DataRow's with its tables and insert those rows into the table), then pass the appropriate DataTable from the DataSet to your TableAdapter in order to persist the changes to the database by calling the Update function on the TableAdapter

tableAdapter.Update(dataSetName);

Calling the Insert function directly only inserts that data into the table in the database itself; it does not have any effect on any local copies of the data (such as your DataSet).

As a side note, try to avoid using language-specific functions like MsgBox. Instead, to show a message box try calling MessageBox.Show(...), as that is language-independent.

Edit

To clarify, the proper way to do this is to create a new DataRow, populate its columns with the new values, add that DataRow to the appropriate table, then pass the table to the TableAdapter through the Update function.

For example, I have...

A DataSet named myDataSet
A DataTable in myDataSet called MyTable
A TableAdapter for MyTable called myTableAdapter
Two columns in that table, FirstName and LastName
Two TextBoxes called txtFirstName and txtLastName

To insert a row with those values, I would do this:

DataRow row = myDataSet.MyTable.NewRow(); // creates a new row with the correct columns

row["FirstName"] = txtFirstName.Text;
row["LastName"] = txtLastName.Text;

myDataSet.MyTable.Rows.Add(row); // adds the new row to the in-memory table

myTableAdapter.Update(myDataSet); // persists all of the changes to the DataSet