I am adding a database write facility to my DetailsView control. Already I have a selectcommand driven from the select parameter in the parent GridView control.
In the Wrox Press book "Beginning ASP.NET 2.0", the ease with which writing facilities could be added is described using an example where the Configure Data Source wizard is fetching data from a single table. In this particular case, the Advanced button automatically adds relevant code for Update, Insert & Delete commands. However, if the stored procedures option is used, this Advanced button is no longer available.
My select command is extracting data from more than one table in the database. I used the Query Designer within the Configure wizard to build it. My problem is how to construct an equivalent Update command to affect fields in more than one table, e.g.:
Events.EventName, Events.Location, Events.StartDate,
Events.EventDescription, Events.EventID,
Travel.Travel, [Group Photos].ImageURL
Later I will need to modify the delete and insert commands, but once I figure out the update command I'm hoping that the others will fall into place.
Here is my source code. The update command sql shown below is generated by the wizard using a single-table implementation. It needs to change, but it's a starting point:
Code:
SelectCommand="SELECT Events.EventName, Events.Location,
Events.StartDate, Events.StartTime, Events.EventDescription,
Events.EventID, Travel.Travel, [Group Photos].ImageURL
FROM Events
INNER JOIN [Group Photos] ON Events.EventID = [Group Photos].EventID
INNER JOIN Travel ON Events.EventID = Travel.EventID
WHERE (Events.EventID = @EventID)"
UpdateCommand="UPDATE [Events] SET [EventName] = @EventName,
[Location] = @Location,
[StartDate] = @StartDate,
[EventDescription] = @EventDescription
WHERE [EventID] = @original_EventID"
ConflictDetection="CompareAllValues"
InsertCommand="INSERT INTO [Events] ([EventName], [Location],
[StartDate], [EventDescription])
VALUES (@EventName, @Location, @StartDate, @EventDescription)"
DeleteCommand="DELETE FROM [Events]
WHERE [EventID] = @original_EventID ...
I can provide more code if it would help. As I'm a newbie, I'm not delving into code-behind yet.
Richard
Rugeley, UK
WinXP Pro SP2
VBW Express + SQL Server 2005 Express
VB Adding UpdateCommand
