Answered by:
Gridview can't delete

Question
-
User1493865241 posted
Hey guys, I'm stuck again. All the anwsers on the forums lead to Cs solutions, since I don't use Cs I find myself in need of help. This shouldn't be to complicated:
Problem:
When clicking on the DELETE button of a record inside my GridView I get:
System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.Extra info:
- my sql2008 user has permission to delete because of: 'db_datareader'
- If I start a new page and put up a new gridview the problem remains.
- CS file I did not include because it's empty :)
Here is some freshly generated code that I haven't touched yet:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="Product_naam" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Width="950px"> <AlternatingRowStyle BackColor="White" /> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="Product_naam" HeaderText="Product_naam" ReadOnly="True" SortExpression="Product_naam" /> <asp:BoundField DataField="Product_hoeveelheid" HeaderText="Product_hoeveelheid" SortExpression="Product_hoeveelheid" /> <asp:BoundField DataField="product_beschrijving" HeaderText="product_beschrijving" SortExpression="product_beschrijving" /> <asp:CheckBoxField DataField="Product_hebbenVan" HeaderText="Product_hebbenVan" SortExpression="Product_hebbenVan" /> <asp:BoundField DataField="Onderneming_naam" HeaderText="Onderneming_naam" SortExpression="Onderneming_naam" /> </Columns> <EditRowStyle BackColor="#7C6F57" /> <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" /> <RowStyle BackColor="#E3EAEB" /> <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" /> <SortedAscendingCellStyle BackColor="#F8FAFA" /> <SortedAscendingHeaderStyle BackColor="#246B61" /> <SortedDescendingCellStyle BackColor="#D4DFE1" /> <SortedDescendingHeaderStyle BackColor="#15524A" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:BR_ConnetionString1 %>" DeleteCommand="DELETE FROM [Pelikaan_producten] WHERE [Product_naam] = @original_Product_naam AND (([Product_hoeveelheid] = @original_Product_hoeveelheid) OR ([Product_hoeveelheid] IS NULL AND @original_Product_hoeveelheid IS NULL)) AND (([product_beschrijving] = @original_product_beschrijving) OR ([product_beschrijving] IS NULL AND @original_product_beschrijving IS NULL)) AND (([Product_hebbenVan] = @original_Product_hebbenVan) OR ([Product_hebbenVan] IS NULL AND @original_Product_hebbenVan IS NULL)) AND (([Onderneming_naam] = @original_Onderneming_naam) OR ([Onderneming_naam] IS NULL AND @original_Onderneming_naam IS NULL))" InsertCommand="INSERT INTO [Pelikaan_producten] ([Product_naam], [Product_hoeveelheid], [product_beschrijving], [Product_hebbenVan], [Onderneming_naam]) VALUES (@Product_naam, @Product_hoeveelheid, @product_beschrijving, @Product_hebbenVan, @Onderneming_naam)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Pelikaan_producten]" UpdateCommand="UPDATE [Pelikaan_producten] SET [Product_hoeveelheid] = @Product_hoeveelheid, [product_beschrijving] = @product_beschrijving, [Product_hebbenVan] = @Product_hebbenVan, [Onderneming_naam] = @Onderneming_naam WHERE [Product_naam] = @original_Product_naam AND (([Product_hoeveelheid] = @original_Product_hoeveelheid) OR ([Product_hoeveelheid] IS NULL AND @original_Product_hoeveelheid IS NULL)) AND (([product_beschrijving] = @original_product_beschrijving) OR ([product_beschrijving] IS NULL AND @original_product_beschrijving IS NULL)) AND (([Product_hebbenVan] = @original_Product_hebbenVan) OR ([Product_hebbenVan] IS NULL AND @original_Product_hebbenVan IS NULL)) AND (([Onderneming_naam] = @original_Onderneming_naam) OR ([Onderneming_naam] IS NULL AND @original_Onderneming_naam IS NULL))"> <DeleteParameters> <asp:Parameter Name="original_Product_naam" Type="String" /> <asp:Parameter Name="original_Product_hoeveelheid" Type="String" /> <asp:Parameter Name="original_product_beschrijving" Type="String" /> <asp:Parameter Name="original_Product_hebbenVan" Type="Boolean" /> <asp:Parameter Name="original_Onderneming_naam" Type="String" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="Product_naam" Type="String" /> <asp:Parameter Name="Product_hoeveelheid" Type="String" /> <asp:Parameter Name="product_beschrijving" Type="String" /> <asp:Parameter Name="Product_hebbenVan" Type="Boolean" /> <asp:Parameter Name="Onderneming_naam" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Product_hoeveelheid" Type="String" /> <asp:Parameter Name="product_beschrijving" Type="String" /> <asp:Parameter Name="Product_hebbenVan" Type="Boolean" /> <asp:Parameter Name="Onderneming_naam" Type="String" /> <asp:Parameter Name="original_Product_naam" Type="String" /> <asp:Parameter Name="original_Product_hoeveelheid" Type="String" /> <asp:Parameter Name="original_product_beschrijving" Type="String" /> <asp:Parameter Name="original_Product_hebbenVan" Type="Boolean" /> <asp:Parameter Name="original_Onderneming_naam" Type="String" /> </UpdateParameters> </asp:SqlDataSource>
It's probebly going to be something very simple but I'm to much of a dumb dumb to notice :(
Here's the sql2008 table I've cooked up on my server:
Product_naam =Varchar(50)
Product_hoeveelheid =Varchar(50)
product_beschrijving =text
proudct_hebbenVan =bit
Onderneming_naam =varchar(50)Thanks in advance !
Thursday, March 21, 2013 11:09 AM
Answers
-
User1241621633 posted
Read these articles hope it'll help
http://www.codeproject.com/Articles/23471/Editable-GridView-in-ASP-NET-2-0
http://wiki.asp.net/page.aspx/1360/add-edit-update-delete-gridview/
thanx
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, March 30, 2013 10:21 AM
All replies
-
User-1716253493 posted
<asp:Parameter Name="original_Product_ hoeveelheid" Type ="Integer" />
don't use string type for number field
Thursday, March 21, 2013 11:19 AM -
User2028823583 posted
Some of your code is chopped off, so I'm not entirely sure. But, in the past, I had a similar problem, that my delete wasn't working because I hadn't set any DataKeyNames. Have you done that? DataKeyNames="xxxID"
Thursday, March 21, 2013 11:19 AM -
User260886948 posted
Hi,
I see that the column product_beschrijving is text.
Handling TEXT datatypes is slightly different than regular columns.
Please try to change the text to varchar(max) and your issue should go away.
For more information, please try to refer to:
http://forums.asp.net/t/950661.aspx/1 .
Hope it can help you.
Best Regards,
Amy Peng
Thursday, March 28, 2013 4:19 AM -
User-1716253493 posted
You need to have one primary field Auto Increament for example ID
[ID] [int] IDENTITY(1,1) NOT NULL
Then use this unique id as gridview datakeynames
<asp:GridView ID="GridView1" DataKeyNames="ID" ... >
Then use this datakeynames value as DELETE/UPDATE WHERE condition
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BR_ConnetionString1 %>" DeleteCommand="DELETE FROM [Pelikaan_producten] WHERE [ID] = @ID" InsertCommand="INSERT INTO [Pelikaan_producten] ([Product_naam], [Product_hoeveelheid], [product_beschrijving], [Product_hebbenVan], [Onderneming_naam]) VALUES (@Product_naam, @Product_hoeveelheid, @product_beschrijving, @Product_hebbenVan, @Onderneming_naam)" SelectCommand="SELECT * FROM [Pelikaan_producten]" UpdateCommand="UPDATE [Pelikaan_producten] SET [Product_hoeveelheid] = @Product_hoeveelheid, [product_beschrijving] = @product_beschrijving, [Product_hebbenVan] = @Product_hebbenVan, [Onderneming_naam] = @Onderneming_naam WHERE [ID] = @ID"> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="Product_naam" Type="String" /> <asp:Parameter Name="Product_hoeveelheid" Type="String" /> <asp:Parameter Name="product_beschrijving" Type="String" /> <asp:Parameter Name="Product_hebbenVan" Type="Boolean" /> <asp:Parameter Name="Onderneming_naam" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Product_hoeveelheid" Type="String" /> <asp:Parameter Name="product_beschrijving" Type="String" /> <asp:Parameter Name="Product_hebbenVan" Type="Boolean" /> <asp:Parameter Name="Onderneming_naam" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource>
Thursday, March 28, 2013 4:42 AM -
User1493865241 posted
Hi Oned_gk,
Ok, I didn't know I was allowed to change the Type attribute ... I'll start experimenting and post my findings/
ThanksSaturday, March 30, 2013 7:17 AM -
User1493865241 posted
Hi Funluckykitty,
I don't know what a datakeyname is but I haven't needed it so far.
I'll read up on datakeynames however, you should know that i'm also having the data server next to me where I've told the sql table to automatically generate ID values. It has worked so far but I'll keep my eye on it and experiment. I'll post my findings as soon as I know more.
Thanks!Saturday, March 30, 2013 7:20 AM -
User1241621633 posted
Read these articles hope it'll help
http://www.codeproject.com/Articles/23471/Editable-GridView-in-ASP-NET-2-0
http://wiki.asp.net/page.aspx/1360/add-edit-update-delete-gridview/
thanx
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, March 30, 2013 10:21 AM -
User1493865241 posted
Amy Peng,
When you asked me for varchar(max) do you mean in the sql database?
I tried that in the aspx file and it's not a legal value to be put in an sqldatasource according to visual studioIs that something for net 2.0? I'm using net 4.5, It's caused problems before.
kind regards,Monday, April 1, 2013 12:42 PM -
User1493865241 posted
Hey Arslan 14,
The links are very good! however I'm not at that level so I stored them for a bit later. I'll see if I can't fix this another way. It was however A very very good tutorial and a very good anwser.
Thanks alotMonday, April 1, 2013 12:51 PM