AccessDataSource is thick

Well it might not be thick, but its certainly not that intuitive for someone who has previously enjoyed the freedom of writing their own queries in good old classic asp or executing nonqueries in asp.net.

Here is why i have been bashing my head against a wall for the last couple of hours:

<asp:AccessDataSource ID="AccessDataSource3" runat="server"
        DataFile="~/App_Data/database.mdb"        
        UpdateCommand="UPDATE [table1] SET [field1]= @field1  WHERE [ID] = @ID"
        SelectCommand="SELECT [ID], [field1], [field2] FROM [table1] WHERE ([ID] = ?)">
        <SelectParameters>
            <asp:ControlParameter ControlID="GridView1" Name="ID"
                PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>      
    </asp:AccessDataSource>

So what was I doing? Very simple: a grid view allows a user to select a record, that record is then displayed in a form view. The form view is using the AccessDataSource shown above. The form views default mode is set to edit.

So whats all the fuss about?

When you press update it doesn't. Thats what the fuss is about. Having rewritten all the relevant bits a couple of times and it still not working, i decided to try and isolate the problem. The first thing i tried was to hardcode the ID being used by the WHERE in the UPDATE statement.

Ah ha! some success, hard coding the ID worked, but of course now my fantastic grid view form view combo could only update on record.

Just out of interest i changed SET [field1]= @field1 -> SET [field1]= @ID

How strange - i was getting the field1 written instead of the ID; so the reason it wouldn't update before was because field1 was being sent for both the field1 value and the ID value.

I guessed that this sort of thing was going to happen when there weren't enough parameters being sent. I noticed that he selectCommand was retrieving field1 and field2 and the updateCommand was only updating field1. 

In good old classic asp you can update what ever you like and select whatever you like no problem. But not apparently with the AccessDataSource; you have to update whatever you select. So the correct code should be:

SelectCommand="SELECT [ID], [field1], [field2] FROM [table1] WHERE ([ID] = ?)"
UpdateCommand="UPDATE [table1] SET [field1]= @field1, [field2] = @field2  WHERE [ID] = @ID"

And now everything works. But thats still Classic ASP 1 - 0  ASP.NET (of course generating the gridview that allows for the population of the formview only took about 5 mins, so i guess they are even)