views:

89

answers:

1

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"...
+2  A: 

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();
    }
Adam
I did the exact same thing and it still doesn't save any changes. I can use select methods perfectly, but can't change anything...
ApoY2k
Does your DataSet have it's insert command set? You can see the commands in the designer.
Adam
Yes, it has inserts, updates, deletes, everything. Even if I change their execution modes to direct (what I understand as directly on the database), nothin is saved...
ApoY2k
Are you using any transactions? Do you get any exceptions? Do you call .AcceptChanges before trying to update the DataSet?
Adam
No. It just doesn't save it. I now tried accessing the table via the DataSet using `data.users.AdduserRow()` but that won't work either... Aren't there any good tutorials on that stuff?
ApoY2k
@apoY2k when you said you tried `data.users.AdduserRow()`, did you subsequently call `data.Update()`?
Adam
Yes. And I gave it the data (DataSet) as parameter to update. Still no change...
ApoY2k
@ApoY2k can you confirm that the row state of the new rows inside the table have the value `RowState.Added`?
Adam
I added some new code above - It seems to add the rows and the state also is correct...
ApoY2k
I've amended my answer. I'll need to see the SQL in the insert command, because your sample code looks OK.
Adam
`INSERT INTO \`Users\` (\`name\`, \`password\`) VALUES (?, ?)` Thats the SQL of the "Insert"-Query of the Adapter. If I run that do the database, the values are added without a hassle...
ApoY2k
Unfortunately, I'm out of ideas without a sample application. Try looking into these links: http://msdn.microsoft.com/en-us/library/bz9tthwx(VS.80).aspx and http://msdn.microsoft.com/en-us/library/ms171933(v=VS.80).aspx
Adam
Well, I uploaded a small TestApp that displays the error... I would be very very thankful if you could somehow manage to take a look at it... http://vault.apoy2k.de/TestApp.zip
ApoY2k
@ApoY2k I'll take a look when I get home.
Adam
Ok, now this is really emberassing, but I found the error - it was my stupidity. I checked the file in my project explorer not the one that is copied to the Debug-Directory upon compiling. There everything was inserted just the right way. Still, thanks for your help...
ApoY2k
I had just tested this on my machine and it worked for me, I'll post the code I used anyway. The only odd thing for me was the Access driver wasn't working under AnyCPU on a x64 machine, so I set the build to x86. Don't worry, picnic errors still happen to me sometimes too :-)
Adam