Solved

ASP.NET error when calling SqlDataSource.Delete

Posted on 2007-11-29
14
1,369 Views
Last Modified: 2008-03-17
I have a gridview which has a ButtonField which calls the DeleteCommand of the GridView. In the code, several parameters are set up and the call is made (SqlDataSource.Delete), however, I get the following error:

"You have specified that your delete command compares all values on SqlDataSource 'SqlDataSource3', but the dictionary passed in for values is empty.  Pass in a valid dictionary for delete or change your mode to OverwriteChanges."}

Any ideas as to what is wrong and how I can resolve this?

Thanks!
0
Comment
Question by:wppiexperts
  • 5
  • 4
  • 3
  • +1
14 Comments
 

Author Comment

by:wppiexperts
ID: 20377518
I think I fixed that issue, by changing ConflictDetection=CompareAllValues to OverwriteChanges, however now the delete command will process but it does not delete any rows, even though all the parameters are assigned the correct values.....now I'm stuck...
0
 
LVL 7

Expert Comment

by:Infinite_Recursion
ID: 20377645
please post the code.
0
 

Author Comment

by:wppiexperts
ID: 20377693
       If e.CommandName = "DeleteFromCart" Then
            Session("original_cart_id") = cartID
            Session("original_cart_userID") = 144
            Session("original_cart_productID") = cart_prodID
            Session("original_PONum") = Nothing
            Session("original_cart_quantity") = qty
            Try
                SqlDataSource3.Delete()

        <DeleteParameters>
            <asp:Parameter Name="original_cart_id" Type="Int32" />
            <asp:Parameter Name="original_cart_userID" Type="Double" />
            <asp:Parameter Name="original_cart_productID" Type="String" />
            <asp:Parameter Name="original_cart_quantity" Type="Int32" />
            <asp:Parameter Name="original_PONum" Type="String" />
        </DeleteParameters>

DeleteCommand="DELETE FROM cart WHERE cart_id = @original_cart_id AND (cart_userID = @original_cart_userID OR (cart_userID IS NULL and @original_cart_userID IS NULL)) AND (cart_productID = @original_cart_productID OR (cart_productID IS NULL AND @original_cart_productID IS NULL)) AND (cart_quantity = @original_cart_quantity OR (cart_quantity IS NULL AND @original_cart_quantity IS NULL)) AND (PONum = @original_PONum OR (PONum IS NULL AND @original_PONum IS NULL))"
                Label3.Text = CalculateTotal(Session("userid"), 1)
            Catch ex As Exception
                Label3.Text = Err.Description
            End Try

        End If
0
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.

 
LVL 7

Expert Comment

by:Infinite_Recursion
ID: 20377733
OK since you have already changed ConflictDetection=CompareAllValues to OverwriteChanges, I am guessing you only need to have the cart_id in your deleteCommand since that id is an identifier for the record. try rebuilding the command.
0
 

Author Comment

by:wppiexperts
ID: 20377814
no luck. I changed the deletecommand to:
DeleteCommand="DELETE FROM cart WHERE cart_id = @original_cart_id

the delete command just doesn't get fired off.
Is there a way to see the deletecommand that is being actually used against the database (with the variables inserted)?
0
 
LVL 7

Expert Comment

by:Infinite_Recursion
ID: 20377841
yeah, you can hook to the events of the sqldatasource events like Deleting, and Deleted.
0
 

Author Comment

by:wppiexperts
ID: 20378152
I tried:
Dim strSQL As String = "DELETE FROM cart WHERE cart_id = " & cartID
SqlDataSource3.DeleteCommand = strSQL

no luck, its still not deleting the record
0
 
LVL 7

Expert Comment

by:Infinite_Recursion
ID: 20378210
place a break point on that line and check that the value of cartID is actually the correct ID of the record that you intend to delete by comparing to the table in the database.
0
 
LVL 10

Expert Comment

by:digitalZo
ID: 20380048
What's the source code for GridView? Where have you called the Command Name?
0
 

Author Comment

by:wppiexperts
ID: 20382228
the code for the gridview is:
    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource3" AutoGenerateColumns="False" DataKeyNames="cart_id,cart_userid" FooterStyle-BackColor="#707070" FooterStyle-ForeColor="#FFFFFF" ShowFooter="True" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
        <Columns>
            <asp:BoundField DataField="cart_userID" HeaderText="cart_userID" SortExpression="cart_userID" Visible="False" />
            <asp:TemplateField HeaderText="cart_id" InsertVisible="False" SortExpression="cart_id"
                Visible="False">
                <ItemTemplate>
                    <asp:Label ID="cart_id" runat="server" Text='<%# Bind("cart_id") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Stock #" SortExpression="cart_productID">
                <ItemTemplate>
                    <asp:Label ID="cart_productID" runat="server" Text='<%# Bind("cart_productID") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Quantity" SortExpression="cart_quantity">
                <EditItemTemplate>
                    <asp:TextBox ID="cart_quantity" runat="server" Text='<%# Bind("cart_quantity") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="cart_quantity" runat="server" Text='<%# Bind("cart_quantity") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Unit Total" SortExpression="subtotal">
                <EditItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("subtotal", "${0:C}") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# FormatCurrency(Get_Amount(Eval("Y1_PRICE"),Eval("cart_quantity"))) %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="Label2" Text='<%# FormatCurrency(Get_Total()) %>' Runat="Server"/>
                </FooterTemplate>
                <FooterStyle BorderWidth="0px" Font-Bold="True" HorizontalAlign="Right" />
            </asp:TemplateField>
            <asp:CommandField ButtonType="Button" ShowEditButton="True" />
            <asp:ButtonField ButtonType="Button" Text="Remove"  CommandName="DeleteFromCart"/>
        </Columns>
        <FooterStyle BackColor="Tan" ForeColor="White" />
        <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
        <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
        <HeaderStyle BackColor="Tan" Font-Bold="True" />
        <AlternatingRowStyle BackColor="PaleGoldenrod" />
    </asp:GridView>
The ButtonField calls the "DeleteFromCart" routine which essentially has the following lines:
Dim cartID As String = CType(row.FindControl("cart_id"), Label).Text
Dim strSQL As String = "DELETE FROM cart WHERE cart_id = " & cartID
SqlDataSource3.DeleteCommand = strSQL

when I display the sql command that is issued, it is finding the correct value for the cartID and the command itself seems to execute fine (I don't receive any type of error saying there was a syntax error).
0
 
LVL 10

Expert Comment

by:digitalZo
ID: 20387027
You aren't referencing the [Delete] CommandName on the grid. How will it come to know that you want to delete from the grid? Add the command name on the rowdeleting event of the grid so that it will fire when you call for deleting of the row.

PS: I've added the code in CAPS just to differentiate between your code and the modified part.


  <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource3" AutoGenerateColumns="False" DataKeyNames="cart_id,cart_userid" FooterStyle-BackColor="#707070" FooterStyle-ForeColor="#FFFFFF" ShowFooter="True" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" ONROWDELETING="DeleteFromCart">

Open in new window

0
 
LVL 10

Accepted Solution

by:
digitalZo earned 125 total points
ID: 20387054
No wait! Sorry, I made a  mistake. If you have added the Command Name on the button field, that's correct. But you need to reference the Row_Deleting event handler to the grid.

Something like this:

You may want to go through this: http://www.gridviewguy.com/ArticleDetails.aspx?articleID=138

Hope this helps.
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource3" AutoGenerateColumns="False" DataKeyNames="cart_id,cart_userid" FooterStyle-BackColor="#707070" FooterStyle-ForeColor="#FFFFFF" ShowFooter="True" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" OnRowDeleting="GridView1_RowDeleting>
 
 
 
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
 
{
 
int cartID = (int) GridView1.DataKeys[e.RowIndex].Value;
 
[delete query] 
 
}

Open in new window

0
 
LVL 1

Expert Comment

by:Computer101
ID: 21146872
Forced accept.

Computer101
EE Admin
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question