I have a table with with some column name includes a space. for example [Product ID] [Product Name] Instead of Product_ID, Product_Name. when I try to create a gridview and enable delete, insert. It just won't work.
I've been trying for several hours without success. When I click on delete. the page postback without any error, but the record doesn't get deleted or updated.
<asp:SqlDataSource id="sourceProducts" runat="server"
SelectCommand="SELECT [Product ID], [Product Name] FROM Products" ConnectionString="<%$ ConnectionStrings:mydb %>"
DeleteCommand="Delete from Products where [Product ID]=@.ProductID
UpdateCommand="UPDATE Products SET [Product Name]=@.ProductName WHERE [Product ID]=@.ProductID"
<UpdateParameters>
<asp:Parameter Name="ProductName" />
<asp:Parameter Name="ProductID" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32"/>
</DeleteParameters>
</asp:SqlDataSource
<asp:GridView ID="GridView2" runat="server" DataSourceID="sourceProducts"
AutoGenerateColumns="False" DataKeyNames="Product ID" >
<Columns>
<asp:BoundField DataField="Product ID" HeaderText="ID" ReadOnly="True" />
<asp:BoundField DataField="Product Name" HeaderText="Product Name"/>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="True">
</asp:GridView>
Another testing I did was to use another table with no space in the Column name, Product_ID, Product_Name. and I can't name my parameter as PID, PNAME. I have to name it as @.Product_ID, @.Product_Name in order for Delete, update to work. My understanding is if I declare the parameter explicitly(<asp:Parameter Name="PID" />, I can use any name I want. Did I must missed something?
I'm new to ASP.NET, could someone help me?
Thanks.
First off, you're setting yourself up for trouble by using field names with spaces. In this case, the problem is with the discrepancy between your Parameter name "ProductID" and your DataKeyNames setting of "Product ID". When you are attempting to update or delete a record, your primary key Parameter is not being set as it's name differs from that of your DataKeyNames setting. The two need to be the same in order for this to work, but you also can't use a Parameter with a space in it at least in good practice. Therefore, switch back to using unspaced field names and make sure the primary key Parameter matches that of the DataKeyNames setting.
|||Thanks for explaining the cause of the problem, it really helps. The above code is a simple demo, and I don't have control over the existing Database.
Is there a way to work arround it?
ddpp
|||Your workaround will be to set these Parameters in code. By using the SqlDataSource.Updating and Deleting events, you can manually extract the DataKey from the GridView and set the command Parameter to its value. With this method, the parameters can have different names.
|||Ed, thanks for the tip. It guides me to the solution
<script runat="server"> void SqlDataSource1_Updating(Object sender, System.Web.UI.WebControls.SqlDataSourceCommandEventArgs e) { e.Command.Parameters["@.id"].Value = e.Command.Parameters["@.ContactID"].Value; e.Command.Parameters["@.name"].Value = e.Command.Parameters["@.ContactName"].Value; e.Command.Parameters.Remove(e.Command.Parameters["@.ContactID"]); e.Command.Parameters.Remove(e.Command.Parameters["@.ContactName"]); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Parameter Name Mapping</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView AutoGenerateColumns="False" DataKeyNames="ContactID" DataSourceID="SqlDataSource1" ID="GridView1" runat="server"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ContactID" HeaderText="ContactID" InsertVisible="False" ReadOnly="True" SortExpression="ContactID" /> <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" /> </Columns> </asp:GridView> <asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:Contacts%>" ID="SqlDataSource1" runat="server" SelectCommand="SELECT [ContactID], [ContactName] FROM [Contacts]" UpdateCommand="UpdateContactName" UpdateCommandType="StoredProcedure" OnUpdating="SqlDataSource1_Updating"> <UpdateParameters> <asp:Parameter Name="id" Type="Int32" /> <asp:Parameter Name="name" Type="String" /> </UpdateParameters> </asp:SqlDataSource> </div> </form></body></html> sql
