Inserting not committed to database

Question

I'm having some trouble getting my DataSet to work.

I have a MDB-Database in the background and created a DataSet out of it. Now I created a new method that lets me create a new user in the table.

But when I call it, nothing happens. No exceptions, no errors and I even get "1" returned as number of affected rows. But when I look in the database, no user was added.

I have the feeling that I miss to somehow tell the DataSet that I want to operate the Database itself rather than just the internal DataSet... How can I achieve this?

// DataSet1 uses the connection to the Database.mdb
// Created by the Designer

// The Users table has 3 columns, id, name and password

DataSet1 set = new DataSet1();
UsersTableAdapter adap = new UsersTableAdapter();
DataSet1.UsersRow row = set.Users.AddUsersRow("asd", "asd");
int count = adap.Insert("das", "das");
MessageBox.Show(row.RowState.ToString() + ": " + count.ToString());
count = adap.Update(set.Users);
set.AcceptChanges();
MessageBox.Show(row.RowState.ToString() + ": " + count.ToString());

// The Messagebox shows: "Added: 1"
// The second one: "Unchanged: 1"

MessageBox.Show(set.Users.Rows.Count.ToString());

// Returns "2"...

Answer 1

Not knowing too much about your code, try reading this how to from MSDN:

http://msdn.microsoft.com/en-us/library/ms233812(v=VS.80).aspx

Update: with data sets, what would normally happen (assuming the data set has been populated) is rows would be added, edited, or deleted in code. These rows would have a corresponding RowState signifying them as added, edited, deleted, or unmodified. During the .Update the associated table adapter, complete with associated insert/update/delete commands, will iterate the rows and execute the command on a given row depending on the row's state. When the rows have been iterated, .AcceptChanges is called, reverting the row state's to default.

If you call AcceptChanges before updating, then nothing will happen. The RowState of each row will be lost so the .Update will not have the required information it needs to perform the update to the database. If you have custom code for wrapping it all in a transaction, then you need to make sure you commit the transaction.

In your example, you seem to imply you are using the table adapter and not the data set itself. I would advise against this - the table adapters usually place methods on the tables in a data set that you should use.

Update 2: the code looks OK to me, though you don't need to call Insert on the adapter, or AcceptChanges after the update (the latter is done automatically). This leads me to believe there is a bug in your insert command SQL - try extracting the SQL used by the command and run it manually against the database.

Update 3: the following code works fine for me:

static void Main(string[] args)
    {
        db1DataSet set = new db1DataSet();
        set.Users.AddUsersRow("asd", "asd");

        foreach (DataRow row in set.Users.Rows)
        {
            object foo = row.RowState; // Confirm row state in debugger.
        }

        UsersTableAdapter adap = new UsersTableAdapter();
        adap.Update(set.Users);

        Console.Read();
    }