Advanced SQL Generation Options Grayed Out

Question

Hi,

I am using Visual Studio 2005. When I attempt to use the Configure Data Source wizard to set up advanced SQL Generation Options the options (i.e. add addtional INSERT, UPDATE... statements) are grayed out. I don't know why. Any ideas?

Thanks,

Keith

Answer 1

 

I have the same problem, hopefully someone can help with this.

I tried it with connections to a SQL Express Database and with a MDB file connection.
Nothing seems to work, it is still grayed out.

Answer 2

Hi-

Here's a link to a blog that goes into this (search for "advanced", it's almost halfway down a long page). Apparently far and away the biggest cause is that there is no primary key for the table defined in the db (my problem too. I had a unique key, but not a primary. A problem with converting from Firebird.). A couple of other things to check but they're not really substantiated. Just stuff to try. Good luck.

http://www.mikepope.com/blog/blogfeed.asmx/GetRSS?category=aspnet

-Gary

Answer 3

Hi,

Has anyone cracked it yet?
My database resides in F:\dev\testWeb\App_Data and the only table in it has an Identity field. I select all the fields and the damn thing is still grayed out, any workarounds?

Cheers

Answer 4

Perhaps you didn't have the primary key SELECTed in the connection string?

SELECT [Text] FROM [TextTable]

Wouldn't work as the code wouldn't know which record to UPDATE.

At minimum one would need something like:

SELECT [Text], [id] FROM [TextTable]

Answer 5

Yes, I have confirmed that the Primary Key is essential.  I tried to configure a data source for a table without a Primary Key, and the advanced options were disabled.  I went straight to the table, added an ID field, marked it as the Primary Key, and set it as an auto-incrementing identity field. 

Then, I came back to VS 2005, entered the cofigure data source wizard and the advanced options were enabled.

I guess there could be other reasons, but the lack of a Primary Key is definitely a show-stopper.

 

Answer 6

Be sure to check the primary key field in the columns list before you hit the Advanced option button.

 

Answer 7

I've been having the same problem with the Advanced SQL Generation Options being greyed out, but my problem is NOT not having a primary key assigned. My tables DO have primary keys. However, I'm using MySQL, and not SQL Server. Can THAT be the problem? If so, is there a workaround for users of MySQL?

Answer 8

As a summary of what has been talked about and something else I've found out. Please make sure:

1. You have a primary key column that is selected in the list of columns
2. Make sure that column does not have a default value. Came across this while playing with the aspnet_MembershipUsers table.

Answer 9

To add to jwwishart's list. Please make sure that:

3. Return only unique rows is not checked.

Answer 10

make sure your query includes the primary kew column.