[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Updating a gridview with a joined table select statement

Using Visual Studio 2005 asp.net
I have a gridview that I populate with a select statement that joins two tables.
Select events.*, eventtype.label
From events, eventtype
Where events.eventtypeID = eventtype.eventtypeID
The update will update the events table.
I cannot get the update to work with the joined field.  Is there a property I can set to make this work or will I need to figure another way??
0
PEAKTT
Asked:
PEAKTT
1 Solution
 
vbsquickresponseCommented:
I think instead of using SQLDataSource, I suggest you to use ObjectDataSource where it gives you flexible of Select, Insert, updating as you need.

Do some expore on ObjectDataSource
0
 
Juan_BarreraCommented:
@vbsquickresponse:

But SqlDataSource does it as well (http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.updatecommand.aspx).

@PEAKTT
Can you please post the SQL statement you use for the SqlDataSource.UpdateCommand property?
0
 
lotusnotesnewbieCommented:
<asp:SqlDataSource ID="display" runat="server" ConnectionString="<%$ ConnectionStrings:myprojDB %>"
SelectCommand="SELECT customer.[UserId],[aspnet_Membership].[Email], aspnet_Users.[UserName], [fname] FROM [customer] JOIN [aspnet_Users] ON [customer].[UserId] = [aspnet_Users].[UserId]  JOIN [aspnet_Membership] ON [aspnet_Users].[UserId] = [aspnet_Membership].[UserId] WHERE (customer.[UserId] = @UserId)"
UpdateCommand="UPDATE [customer] SET [fname] = @fname WHERE [UserId] = @UserId">
                                            <UpdateParameters>
                                                <asp:Parameter Name="fname" Type="String" />
                                            </UpdateParameters>
                                            <SelectParameters>
                                                <asp:ControlParameter ControlID="grdCustomers" Name="UserId" PropertyName="SelectedValue" Type="Object" />
                                            </SelectParameters>
                                        </asp:SqlDataSource>

This works for me!!! I not so clear about your question. See if this helps or le me know what u r looking for exactly
0
 
PEAKTTAuthor Commented:
This is my SQL statement code:

I am using the select command to fill the gridview rows.  Each row will have an Edit button.
I then use the update command to edit the rows.

These statements are what I am using.  As is the update does not work.  If I take out the field from the joined table, it works just fine.  I have the gridview set with enable editing and the datakeynames set to the key field.

Any Ideas??
SelectCommand="SELECT SubpeonaExhibitFields.SubpeonaExhibitFieldsID, SubpeonaExhibitFields.SubpoenaID, SubpeonaExhibitFields.ProvideriD, SubpeonaExhibitFields.ExhibitID, SubpeonaExhibitFields.ExhibitFieldsID, SubpeonaExhibitFields.ExhibitFieldValue, SubpeonaExhibitFields.ModifiedDate, SubpeonaExhibitFields.ModifiedBy, ExhibitFields.ExhibitLabel FROM SubpeonaExhibitFields LEFT OUTER JOIN ExhibitFields ON SubpeonaExhibitFields.ExhibitFieldsID = ExhibitFields.ExhibitFieldsID WHERE (SubpeonaExhibitFields.SubpeonaExhibitFieldsID = @SubpeonaExhibitFieldsID) ORDER BY SubpeonaExhibitFields.ExhibitFieldsID" 
                    UpdateCommand="UPDATE SubpeonaExhibitFields SET SubpoenaID = @SubpoenaID, ProvideriD = @ProvideriD, ExhibitID = @ExhibitID, ExhibitFieldsID = @ExhibitFieldsID, ExhibitFieldValue = @ExhibitFieldValue, ModifiedDate = GETDATE(), ModifiedBy = @ModifiedBy WHERE (SubpeonaExhibitFieldsID = @SubpeonaExhibitFieldsID)" 
                    DeleteCommand="DELETE FROM [SubpeonaExhibitFields] WHERE [SubpeonaExhibitFieldsID] = @SubpeonaExhibitFieldsID" 
                    InsertCommand="INSERT INTO [SubpeonaExhibitFields] ([SubpoenaID], [ProvideriD], [ExhibitID], [ExhibitFieldsID], [ExhibitFieldValue], [ModifiedDate], [ModifiedBy]) VALUES (@SubpoenaID, @ProvideriD, @ExhibitID, @ExhibitFieldsID, @ExhibitFieldValue,getdate(), @ModifiedBy)">

Open in new window

0
 
PEAKTTAuthor Commented:
I found that if you add all the key fields to the DataKeyNames property you can have them hidden when displaying the gridview yet use them for editting.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now