DataBinding With OLEDB and asp:ListView

Question

Goshutu 0 Newbie Poster

11 Years Ago

Hi, I have a problem with my project, regarging deletion of an element in an asp:ListView, which is connected to a SQL database using ODBC. I have succeeded adding and updating a row, but deleting does not work. When I try to delete nothing happens. I think the problem is with getting the ID of the row that I want to delete. I think it tries to delete rows with ID = 0, because only the value ID does not get passed, the DeleteCommand="DELETE FROM [CITIES] WHERE [ID] = ?" gets executed. I know, because i substituted the questionmark with an actual ID from the table and it worked for that row. Please, help me, because I am struggling with this problem for a while now, and doing some research in the web did not help. I have disabled the Optimistic Concurrency in the SQLDataSource object. Here is the code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AddCity.aspx.cs" Inherits="AddCity" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Label ID="Label1" runat="server" Visible="false" CssClass="Warning" Text="Данните са променени междувременно, моля опитайте отново!"></asp:Label>
    <asp:ListView ID="ListView1" runat="server" DataKeyNames="ID" 
        DataSourceID="SqlDataSource1" InsertItemPosition="LastItem" >
        <AlternatingItemTemplate>
            <tr style="background-color:#FFF8DC;">
                <td>
                    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
                        Text="Изтрий" />
                    <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Промени" />
                </td>
                <td>
                    <asp:Label ID="NAMELabel" runat="server" Text='<%# Bind("NAME") %>' />
                </td>
                <td>
                    <asp:Label ID="REGIONLabel" runat="server" Text='<%# Bind("REGION") %>' />
                </td>
                <td>
                    <asp:Label ID="EDITCOUNTLabel" runat="server" Text='<%# Bind("EDITCOUNT") %>' />
                </td>
            </tr>
        </AlternatingItemTemplate>
        <EditItemTemplate>
            <tr style="background-color:#008A8C;color: #FFFFFF;">
                <td>
                    <asp:Button ID="UpdateButton" runat="server" CommandName="Update" 
                        Text="Обнови" />
                    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
                        Text="Отказ" />
                </td>
                <td>
                    <asp:TextBox ID="NAMETextBox" runat="server" Text='<%# Bind("NAME") %>' />
                </td>
                <td>
                    <asp:TextBox ID="REGIONTextBox" runat="server" Text='<%# Bind("REGION") %>' />
                </td>
                <td>
                    <asp:TextBox ID="EDITCOUNTTextBox" runat="server" Text='<%# Bind("EDITCOUNT") %>' />
                </td>
            </tr>
        </EditItemTemplate>
        <EmptyDataTemplate>
            <table runat="server" 

                style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;">
                <tr>
                    <td>
                        No data was returned.</td>
                </tr>
            </table>
        </EmptyDataTemplate>
        <InsertItemTemplate>
            <tr style="">
                <td>
                    <asp:Button ID="InsertButton" runat="server" CommandName="Insert" 
                        Text="Добави" />
                    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
                        Text="Отказ" />
                </td>
                <td>
                    <asp:TextBox ID="NAMETextBox" runat="server" Text='<%# Bind("NAME") %>' />
                </td>
                <td>
                    <asp:TextBox ID="REGIONTextBox" runat="server" Text='<%# Bind("REGION") %>' />
                </td>
                <td>
                    <asp:TextBox ID="EDITCOUNTTextBox" runat="server" Text='<%# Bind("EDITCOUNT") %>' />
                </td>
                <td>
                    &nbsp;</td>
            </tr>
        </InsertItemTemplate>
        <ItemTemplate>
            <tr style="background-color:#DCDCDC;color: #000000;">
                <td>
                    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
                        Text="Изтрий" />
                    <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Промени" />
                </td>
                <td>
                    <asp:Label ID="NAMELabel" runat="server" Text='<%# Eval("NAME") %>' />
                </td>
                <td>
                    <asp:Label ID="REGIONLabel" runat="server" Text='<%# Eval("REGION") %>' />
                </td>
                <td>
                    <asp:Label ID="EDITCOUNTLabel" runat="server" Text='<%# Eval("EDITCOUNT") %>' />
                </td>
            </tr>
        </ItemTemplate>
        <LayoutTemplate>
            <table runat="server">
                <tr runat="server">
                    <td runat="server">
                        <table ID="itemPlaceholderContainer" runat="server" border="1" 

                            style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;font-family: Verdana, Arial, Helvetica, sans-serif;">
                            <tr runat="server" style="background-color:#DCDCDC;color: #000000;">
                                <th runat="server">
                                </th>
                                <th runat="server">
                                    NAME</th>
                                <th runat="server">
                                    REGION</th>
                                <th runat="server">
                                    EDITCOUNT</th>
                            </tr>
                            <tr ID="itemPlaceholder" runat="server">
                            </tr>
                        </table>
                    </td>
                </tr>
                <tr runat="server">
                    <td runat="server" 

                        style="text-align: center;background-color: #CCCCCC;font-family: Verdana, Arial, Helvetica, sans-serif;color: #000000;">
                        <asp:DataPager ID="DataPager1" runat="server">
                            <Fields>
                                <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" 
                                    ShowLastPageButton="True" />
                            </Fields>
                        </asp:DataPager>
                    </td>
                </tr>
            </table>
        </LayoutTemplate>
        <SelectedItemTemplate>
            <tr style="background-color:#008A8C;font-weight: bold;color: #FFFFFF;">
                <td>
                    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
                        Text="Изтрий" />
                    <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Промени" />
                </td>
                <td>
                    <asp:Label ID="NAMELabel" runat="server" Text='<%# Eval("NAME") %>' />
                </td>
                <td>
                    <asp:Label ID="REGIONLabel" runat="server" Text='<%# Eval("REGION") %>' />
                </td>
                <td>
                    <asp:Label ID="EDITCOUNTLabel" runat="server" Visible="false" Text='<%# Eval("EDITCOUNT") %>' />
                </td>
            </tr>
        </SelectedItemTemplate>
    </asp:ListView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConflictDetection="OverwriteChanges"
        ConnectionString="<%$ ConnectionStrings:ExampleDB4ConnectionString1 %>" 
        DeleteCommand="DELETE FROM [CITIES] WHERE [ID] = ?" 
        InsertCommand="INSERT INTO [CITIES] ([NAME], [REGION], [EDITCOUNT]) VALUES (?, ?, ?)" 
        OldValuesParameterFormatString="original_{0}" 
        ProviderName="<%$ ConnectionStrings:ExampleDB4ConnectionString1.ProviderName %>" 
        SelectCommand="SELECT [NAME], [REGION], [EDITCOUNT], [ID] FROM [CITIES]" 
        UpdateCommand="UPDATE [CITIES] SET [EDITCOUNT] = ?+1, [NAME] = ?, [REGION] = ? WHERE [ID] = ?">
        <DeleteParameters>
            <asp:Parameter Name="NAME" Type="String" />
            <asp:Parameter Name="REGION" Type="String" />
            <asp:Parameter Name="ID" Type="Int32" />
            <asp:Parameter Name="EDITCOUNT" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="NAME" Type="String" />
            <asp:Parameter Name="REGION" Type="String" />
            <asp:Parameter Name="EDITCOUNT" Type="Int32" />
            <asp:Parameter Name="ID" Type="Int32" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="EDITCOUNT" Type="Int32" />
            <asp:Parameter Name="NAME" Type="String" />
            <asp:Parameter Name="REGION" Type="String" />
            <asp:Parameter Name="ID" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <br />
    </form>
</body>
</html>

asp.net

2 Contributors
3 Replies
252 Views
15 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by LastMitch

LastMitch

11 Years Ago

Hi, I have a problem with my project, regarging deletion of an element in an asp:ListView, which is connected to a SQL database using ODBC. I have succeeded adding and updating a row, but deleting does not work.

Read this and used a few lines of code from the link and make some ajustment with the query and run it again after you make the changes:

http://www.dbtutorials.com/display/listview-editing.aspx

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Goshutu 0 Newbie Poster · Answer 1 · 2013-04-14T20:49:38+00:00

In my case it is different, in ODBC the paramerters are accessed with a questionmark and reffering to them by name ( like @ID ) does not work. I am thinking of changing my datasource to OLEDB...

LastMitch · Answer 2 · 2013-04-15T01:41:06+00:00

In my case it is different, in ODBC the paramerters are accessed with a questionmark and reffering to them by name ( like @ID ) does not work. I am thinking of changing my datasource to OLEDB...

OK. Since you are OLEDB you can take a look at these (both links has a delete button function(statement which you can try)):

http://www.java2s.com/Tutorial/ASP.NET/0360__ADO.net-Database/Execcutedeletestatement.htm

http://www.c-sharpcorner.com/Forums/Thread/177354/how-to-do-insert-update-and-delete-operation-in-listview-usi.aspx