locked
Gridview can't delete RRS feed

  • 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

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/

    Thanks

    Saturday, 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
    • 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 studio

    Is 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 alot

    Monday, April 1, 2013 12:51 PM