WHY is - 'GENERATE INSERT UPDATE AND SELECT STATEMENT' greyed out?

Question

Why in the ADVANCE section when I 'configure data source' is the 'GENERATE INSERT UPDATE AND SELECT STATEMENT' greyed out? On some tables it isn't greyed out and works fine.

I know that a way around this can be achieved by changing the autogenerateeditbutton tag to true in properties and you can edit this way - but when this is done there is no update to the database when you attempt.

It was also mentioned that this could be to do with setting up a primary key when I looked on forums on this matter. I couldn't get a conclusive answer though.

The error in the browser is as follows:

NotSupportedException: Updating is not supported by data source 'AccessDataSource1' unless UpdateCommand is specified.

Any ideas how to update the database with these problematic tables? Why are they problematic?

How is the Primary Key alocated to the table. Should this be done in Access? Can it be done in VS08 and how?

Answer 1

In order for the insert/select/update statements to be automatically generated, the table has to have a primary key so that code to select the correct row upon insert or to update knows which row to select. If you don't have a column in the table that has unique values it is possible for more than one row to match the one that should be updated. Using a primary key allows designer to generate code that reliably chooses the correct row to update.

Answer 2

Make your data source with the view like this: mainkey is a primarykey. permission on view to insert,update,select,delete for such user:

" DeleteCommand="DELETE FROM [VW_Security_UK_UserAccess] WHERE [MainKey] = @MainKey"

                InsertCommand="INSERT INTO [VW_Security_UK_UserAccess] ([UserName], [HasAccess], [ApplicationDefinitionmainkey], [SortOrder]) VALUES (@UserName, @HasAccess, @ApplicationDefinitionmainkey, @SortOrder)" 
                SelectCommand="SELECT [MainKey], [UserName], [HasAccess], [ApplicationDefinitionmainkey], [SortOrder] FROM [VW_Security_UK_UserAccess]" 
                UpdateCommand="UPDATE [VW_Security_UK_UserAccess] SET [UserName] = @UserName, [HasAccess] = @HasAccess, [ApplicationDefinitionmainkey] = @ApplicationDefinitionmainkey, [SortOrder] = @SortOrder WHERE [MainKey] = @MainKey">
                <DeleteParameters>
                    <asp:Parameter Name="MainKey" Type="Int16" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="UserName" Type="String" />
                    <asp:Parameter Name="HasAccess" Type="String" />
                    <asp:Parameter Name="ApplicationDefinitionmainkey" Type="Byte" />
                    <asp:Parameter Name="SortOrder" Type="Int32" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="UserName" Type="String" />
                    <asp:Parameter Name="HasAccess" Type="String" />
                    <asp:Parameter Name="ApplicationDefinitionmainkey" Type="Byte" />
                    <asp:Parameter Name="SortOrder" Type="Int32" />
                    <asp:Parameter Name="MainKey" Type="Int16" />
                </UpdateParameters>
            </asp:SqlDataSource>

Answer 3

see tvanfosson's answer re primary key, that is one possibility. You will also get this behavior if your select statement uses a view instead of a direct table.

you can get around this by generating or hand-coding the xml in the .xsd file, but that is a fairly hardcore solution ;-)