Updating with Filtered GridView

Discussion in 'ASP .Net' started by YisMan, Apr 27, 2008.

  1. YisMan

    YisMan Guest

    Hi everybody,
    I'm just finding my way around in ASP.NET and have a proplem with a
    databound gridview.
    I have a simple SqlDataSource returning all rows from a table name
    "Accounts". The dataSource has auto-created CRUD (=Create Read Update Delete)
    statements. the CRUD Operations work perfectly till i add the following lines
    of code to the page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
    Handles Me.Load
    Dim strFilter As String = "1=1"
    If txtBank.Text <> "" Then strFilter &= " and banknum=" & txtBank.Text
    SqlDataSource1.SelectCommand = "SELECT * FROM [Accounts] WHERE " &
    strFilter
    GridView1.DataBind()
    End Sub

    what happens now is that the Delete statement still works but the Update
    does not. No error occurs or anything like that. the record simply does not
    get updated. the "txtBank" is a field which can be optionally used by the
    user to filter by bank number. the problem, though, exists even if the
    textbox is empty.
    if i comment out the last 2 lines of the selectcommand and databind then the
    updates are back in order but the filter becomes worthless.
    the reason im using the selectcommand instead of the commom control
    parameter is because of 2 main reasons:
    a) in the end application there will be many filters and this cannot be
    accomplished with the declaritive option.
    b) the filters must be optional.

    I would appreciate any help here. if there is another way to filter or
    alternatively, have the gridview accept updates after being filtered please
    let me know about it.

    For reference, im pasting my page markup. very simple. Almost everything was
    made by the designer.


    <asp:Label ID="Label5" runat="server" Text="Bank Number:"></asp:Label>
    <asp:TextBox ID="txtBank" runat="server"
    AutoPostBack="True"></asp:TextBox>

    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
    AllowSorting="True" AutoGenerateColumns="False"
    DataKeyNames="AccountID"
    DataSourceID="SqlDataSource1">
    <Columns>
    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True"
    ShowSelectButton="True" />
    <asp:BoundField DataField="AccountID" HeaderText="AccountID"
    InsertVisible="False" ReadOnly="True"
    SortExpression="AccountID" />
    <asp:BoundField DataField="PersonID" HeaderText="PersonID"
    SortExpression="PersonID" />
    <asp:BoundField DataField="BankNum" HeaderText="BankNum"
    SortExpression="BankNum" />
    <asp:BoundField DataField="BranchNum" HeaderText="BranchNum"
    SortExpression="BranchNum" />
    <asp:BoundField DataField="AccountNum" HeaderText="AccountNum"
    SortExpression="AccountNum" />
    <asp:BoundField DataField="HolderName" HeaderText="HolderName"
    SortExpression="HolderName" />
    </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:cnsMainBase %>"
    DeleteCommand="DELETE FROM [Accounts] WHERE [AccountID] = @AccountID"
    InsertCommand="INSERT INTO [Accounts] ([AddedOn], [AddedBy],
    [PersonID], [BankNum], [BranchNum], [AccountNum], [HolderName]) VALUES
    (@AddedOn, @AddedBy, @PersonID, @BankNum, @BranchNum, @AccountNum,
    @HolderName)"
    SelectCommand="SELECT * FROM [Accounts]"
    UpdateCommand="UPDATE [Accounts] SET [AddedOn] = @AddedOn, [AddedBy]
    = @AddedBy, [PersonID] = @PersonID, [BankNum] = @BankNum, [BranchNum] =
    @BranchNum, [AccountNum] = @AccountNum, [HolderName] = @HolderName WHERE
    [AccountID] = @AccountID">
    <DeleteParameters>
    <asp:parameter Name="AccountID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
    <asp:parameter Name="AddedOn" Type="DateTime" />
    <asp:parameter Name="AddedBy" Type="String" />
    <asp:parameter Name="PersonID" Type="Int32" />
    <asp:parameter Name="BankNum" Type="Int32" />
    <asp:parameter Name="BranchNum" Type="Int32" />
    <asp:parameter Name="AccountNum" Type="Int64" />
    <asp:parameter Name="HolderName" Type="String" />
    <asp:parameter Name="AccountID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
    <asp:parameter Name="AddedOn" Type="DateTime" />
    <asp:parameter Name="AddedBy" Type="String" />
    <asp:parameter Name="PersonID" Type="Int32" />
    <asp:parameter Name="BankNum" Type="Int32" />
    <asp:parameter Name="BranchNum" Type="Int32" />
    <asp:parameter Name="AccountNum" Type="Int64" />
    <asp:parameter Name="HolderName" Type="String" />
    </InsertParameters>
    </asp:SqlDataSource>



    Thankfully,
    YisMan
     
    YisMan, Apr 27, 2008
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.