How to use ListView + Views + Edit?

ken evans · January 31st, 2010, 10:27 AM

Having studied the book for over a month, I'm now trying to build my own application and I have found a problem with "editable views" .

When I try to set up the ListView control with a "view" the editing options are greyed out.

This also happens with the SqlDataSource control.
Everything works OK when I try to access a table directly.

How can I use the ListView, DetailsView and SqlDataSource controls to insert,update and delete data columns that are exposed via a view?

Note: This is a step along my road towards embedding the "editable views" in stored procedures. So any warnings of what other data access problems I might encounter would be most welcome/.

Thanks
Ken

Imar · January 31st, 2010, 11:03 AM

Does you table have an Primary Key / Identity column?

Imar

ken evans · January 31st, 2010, 11:38 AM

Yes it has a primary key.
I am using a view on just one table as a test.
The table has a primary key using an "int" data type and is set to be not-null.

When I use ListView with the standard just "select" feature (without any editing features) I get a "no data" message. Which is correct because there is as yet, no data in the table.

Ken

Imar · January 31st, 2010, 11:55 AM

Ah, a database view. I thought you were referring to the ListView....

I am not 100% sure, but I don't think editable Views are supported by SqlDataSource in the Designer.

What works though is this:

1. Drag a table in Design View. VWD creates a GridView and SqlDataSource
2. Manually update your SQL statements and replace the table name with the view name.

This only works if your view and table look a lot like each other....

Cheers,

Imar

ken evans · January 31st, 2010, 12:36 PM

OK - Sorry for my vague question. Here is a clarification.

My Database Views
I'm using SQL Server 2008 SP1.
I have a number of tables set up in the database.
I used the SQL Server Management Studio "Views>New View" feature to create my test views in the database.

Visual Studio 2008 Sp1
With the .aspx page open in Visual Studio, I drag an SqlDataSource control into the cpMainContent area in design view and proceed as follows:
* Choose "Configure Data Source" in the smart panel of the SqlDataSource control.
* Choose New Connection then select my SQL 2008 server in the "Server Name" drop down box.
* Select my database in the "Select or enter database name" drop down box.
* Pressing the "Test Connection" button gives a "Test connection succeeded" message
* I save the connection string shown in the next dialog box and then the "Configure the Select Statement" dialog box appears.

* choose the radio button "Specify columns from a table or view".
All of the tables and views in my database are listed in the "Name" drop down box.

PROCEDURE DIVERGENCE

Option 1:
* choose the database table that I created in SQL Server Management Studio.
* Select "*"
* click on the "Advanced" button and I can choose the option "Generate INSERT, UPDATE and DELETE statements.

Option 2:
choose the database view that I created in SQL Server Management Studio. (This is a view on the same table as Option 1 but with fewer columns)
* Select "*"
* click on the "Advanced" button and all options are greyed out.

COMMENTS
The database view includes the primary key column.

Any clues as to why the options are greyed out in option 2?
Note: there is no data in the table.

Thanks
Ken

*

Imar · January 31st, 2010, 12:51 PM

Yes, I understood all that once I knew you were talking about database views. But take another look at my reply:

Quote:

I am not 100% sure, but I don't think editable Views are supported by SqlDataSource in the Designer.

In other words: you can't make editable views in Design View. Instead, start with a table so you get the code set up, and then manually modify the SQL statements to target your views. Alternatively, write the entire code for SqlDataSource by hand....

Imar

ken evans · February 3rd, 2010, 09:57 AM

Ok Imar. Thanks for that.
It was important for me to be sure that you and I were on the same page.

Please note that I was just testing database views as a step before moving on to using nested views within a stored procedure.
So as a next step, I studied stored procedures. Now I have three stored procedures for one of my tables. (one each for Select, Insert, Update).

Now I have hit another point of confusion in the "Define Parameters" dialog box of the SqlDataSource- Configure Data Source procedure.

My problem is that I'm not sure what to do with the "Parameter Source" drop down box.
The "Parameters" box correctly lists all of the columns in my stored procedure.

So what do I put into the "Parameter source" drop down box?
When I choose "Control" in the drop down box, the next dialog box asks for the ControlID.

However, I have not yet added any UI controls to my aspx page.
- I was hoping to be able to use the ListView or DetailsView because they seem to offer lots of time saving. But I can't figure out how to do this.

So do I have to design a UI with lots of controls (each with an ID) on the aspx page before I can complete the "Define Parameters" dialog box?
Or am I missing something?

And finally. when I try to use nested views within my stored procedures, will I hit the same problem of "not possible"?

Thanks

Ken

Imar · February 3rd, 2010, 10:24 AM

Hi ken,

Since this is not directly related to the book, can you please post this in a more generic ASP.NET category? You can pick one here: http://p2p.wrox.com/asp-net-3-5-436/

This makes it easier for others to join the discussion as well and keeps the book's forum target at its content only.

Cheers,

Imar