Option to update, delete not available when creating SqlDataSource

[Jesse Liberty]

I created a new database (tried both in 2005 and in SQL 2000) and then
created a SqlDataSource control in an asp.net application. Clicked on the
smart tag and whose configure data source. When I click Advanced should see
the Generate Insert, Update and Delete statements check box but it is grayed
out. Any idea what might cause this?

I can work around with this code, which does work:

SqlDataSource1.InsertCommand = "Insert into myTable Values (...)"
SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.Text
SqlDataSource1.Insert()

 

[S. Justin Gengo [MCP]]

Jesse,

You have to set the select command to be able to autogenerate the update and
delete commands. Also the select command may not be a stored procedure. One
way to do this if you want to use stored procedures would be to use the
wizard to create a select command also autogenerating the insert and delete
commands and then copy each of them to a stored procedure afterwards
switching the commands to use said stored procs.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[Jesse Liberty]

Thanks. Here's what I did:

1. Deleted existing data source
2. Created new data source
3. Clicked "configure data source
4. Created new connection (used Windows Authentication and tested
connection)
Data Source=BACH\SQLSERVER2005;Initial Catalog=XXX;Integrated
Security=True
5. Saved connection string
6. On Configure the select, choose Specify columns from a table or view
7. Set the name to the (one) table in the db
8. Click on *
9. Click on Advanced

Both options are grayed out.

Thanks.

-Jesse

PS: I'm sure I'm doing something stupid, but I've tried this using SQL 2005
and SQL 2000 with and without an identity column in the table. There are no
other tables, nor triggers, constraints etc. that I've added.

 

[S. Justin Gengo [MCP]]

Jesse,

Save the datasource only having created the select statement. Then re-open
it and click advanced.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[SoftwareLogic]

Easy fix

I know this is an old post, but I stumbled upon it so others may too.

The solution is to add a Primary Key to the table and then the option will be available.