I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown.
I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:
----------------------------------------
SELECT:
SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName
FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID)
WHERE (People.PersonID = ?)
UPDATE:
UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ?
----------------------------------------
The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed.
If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.
Thank you in advance for any time, help, and/or advice you can give.
Brian
I just wanted to add that I am working with Visual Web Developer 2005 Express. If any one knows how I can get around this issue please let me know, or even if you might know of a place that could possibly have a document that approaches this I have no problem doing the research. I have spent hours searching forums and doing google and microsoft searches for tutorials and have had no luck. I definately havn't come across anything say this is not possible, I just need to know what I am missing.
Thanks for your time in advance,
Brian
|||You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.
That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".
|||
Motley:
You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.
The question marks were put in place by the software. I was trying to get the UPDATE to work on my own and i was setting the fields I wanted to be updated equal to @.fieldName. I could not get this to update so I used the "Advanced" feature in the quesry wizard (I guess it's called) and selected the button to auto-generate the SQL statement and this worked. The UPDATE statement it generated is the one I pasted above. My database is an access database for the time being, but I am using the SqlDataSource method to connect to my database, must be thats why it used the question marks?
That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".
The way I have it setup is so that the field contains a INT value, but the value it shows to the user is a text value. The drop down list you are suggesting is exactly what I am using. When the user is in Read Only mode, everything is shown as a label. When they enter Edit mode, all the fields with foreign keys turn into drop down lists. The drop down list shows a string value, but when the choice is made, its value is and integer. I believe this is working right because the technique I use in order to select a user to edit is to select a name from a drop down list that is external from my gridview (same thing with my detailview). The value of the drop down list is numerical based on the primary key of the PersonID, but the value shown in the drop down list is the name that corrisponds to the given PersonID. This works great and only shows me the record of the person I want to see. Because this worked, I just assumed that useing the same technique in the Edit view would be working the same way. I think this is what you mentioned in what you mentioned above. I will paste all the acctual code from the page I am working on that uses DetailsView.
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
DeleteCommand="DELETE FROM [People] WHERE [PersonID] = ?" InsertCommand="INSERT INTO [People] ([FirstName], [LastName], [FullName], [PropertyID], [InviteTypeID], [RSVP], [Wheelchair], [MealID], [TransportationID]) VALUES (?, ?, ?, ?, ?, ?, ?,?,?)"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT People.*, Property.[House/Day Hab], InviteType.InviteTypeName, Meals.MealName, Transportation.TransportationName
FROM Transportation INNER JOIN (Meals INNER JOIN (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) ON Meals.MealID=People.MealID) ON Transportation.TransportationID=People.TransportationID
WHERE (PersonID = ?)"
UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?">
<DeleteParameters>
<asp:Parameter Name="PersonID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="FullNameDDL" Name="People.PersonID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="PropertyID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="MealID" Type="Int32" />
<asp:Parameter Name="TransportationID" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
<asp:DropDownList ID="FullNameDDL" runat="server" DataSourceID="GetNames" DataTextField="FullName"
DataValueField="PersonID" Style='left: 5px; position: relative; top: 190px' AutoPostBack='True'>
</asp:DropDownList><asp:AccessDataSource ID="GetNames" runat="server" DataFile="H:\Visual Studio 2005\WebSites\test\App_Data\DinnerGuest.mdb"
SelectCommand="SELECT [PersonID], [FullName] FROM [People]"></asp:AccessDataSource>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
Height="50px" Style='left: 201px; position: relative; top: 152px' Width='125px'>
<RowStyle Wrap="False" />
<Fields>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" ConvertEmptyStringToNull="False" NullDisplayText="<NULL>" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" ConvertEmptyStringToNull="False" />
<asp:TemplateField HeaderText="PropertyID" SortExpression="PropertyID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" SelectedValue='<%# Bind("PropertyID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("[House/Day Hab]") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="InviteTypeID" SortExpression="InviteTypeID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" SelectedValue='<%# Bind("InviteTypeID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList4" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("InviteTypeName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CheckBoxField DataField="RSVP" HeaderText="RSVP" SortExpression="RSVP" />
<asp:CheckBoxField DataField="Wheelchair" HeaderText="Wheelchair" SortExpression="Wheelchair" />
<asp:TemplateField HeaderText="MealID" SortExpression="MealID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList5" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" SelectedValue='<%# Bind("MealID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList6" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("MealName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TransportationID" SortExpression="TransportationID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList7" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" SelectedValue='<%# Bind("TransportationID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList8" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("TransportationName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
</div>
</form>
Yes, using access would require using ?'s since it doesn't support named parameters.
The DropDownField control I mentioned makes things a bit easier for you because you don't have to create templated fields (They become DropDownFields), your main select is kept simple if you want (No joins needed, it can pull the text values for you if you want).
But since you've already gone through the trouble, it appears the only problem I can find is that your update parameters do not match your update command.
UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?"
UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>
LocationID should be PropertyID. MealID needs to be added. TransportationID needs to be added. PersonID should either be a controlparameter -- pulling it's value from the person dropdown, or you should add it to the detailview's datakeynames property. All the parameters need to be in the exact order they are mentioned in the update command.
|||Thank you so much for all your help. I was definately only relying on Visual Web Developer to get this going which seems to be my down fall. As I made changes to my data source and changed my queries, I guess the code that is automatically developed doesn't follow along with the changes I make very well. Thanks again for taking the time to look through my code, I will definately make sure I am checking it more, instead of just relying on the software. I guess if the software could do everything I wanted without me seeing the code then I probably would have a much more difficult time finding work haha. Live and learn I guess.
Thanks again,
Brian
No comments:
Post a Comment