Cannot Find Row Index after using row filter

Discussion in 'ASP .Net Datagrid Control' started by troutbum, Jan 22, 2004.

  1. troutbum

    troutbum Guest

    I have a datagrid that works great and edits/updates just fine. So I
    decided to add rowfilter via a textbox search. This works great,
    however when I click edit the values in the row all change to values
    in another row. This is confusing to the users.. How do I take
    advantage of the rowfilter and editcommand at the same time.
    Troutbum
     
    troutbum, Jan 22, 2004
    #1
    1. Advertising

  2. troutbum

    Gerald Klein Guest

    DataTable dt = new DataTable();
    dt.Rows[0];
    dt.DefaultView[0];



    I am guessing that you are accessing the rowindex for the main table and not
    for the view.



    "troutbum" <> wrote in message
    news:...
    > I have a datagrid that works great and edits/updates just fine. So I
    > decided to add rowfilter via a textbox search. This works great,
    > however when I click edit the values in the row all change to values
    > in another row. This is confusing to the users.. How do I take
    > advantage of the rowfilter and editcommand at the same time.
    > Troutbum
     
    Gerald Klein, Jan 22, 2004
    #2
    1. Advertising

  3. I was in line with you thinking as well. But I wasn't sure how to get
    the real row index.


    dsOnTime = New DataSet
    daOnTime = New SqlDataAdapter(strSql, connWmsReporting)
    connWmsReporting.Open()
    daOnTime.Fill(dsOnTime, "OpenOrders")

    Dim dtOpenOrders As DataTable = dsOnTime.Tables(0)
    Dim dtvOpenOrders As DataView = New DataView(dtOpenOrders)
    If bolSearch Then
    dtvOpenOrders.RowFilter = "FO_ORDERNUMBER LIKE '%" &
    strSearch & "%'"
    dtvOpenOrders.RowFilter = "MV_BOLNBR LIKE '%" &
    strSearch & "%'"
    dtvOpenOrders.RowFilter = "FO_CUSTID LIKE '%" &
    strSearch & "%'"
    dtvOpenOrders.RowFilter = "FO_CUSTNAME LIKE '%" &
    strSearch & "%'"
    End If

    dgOnTime.DataSource = dtvOpenOrders
    dgOnTime.DataKeyField = "FO_ORDERNUMBER"
    dgOnTime.DataBind()




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Matthew Torline, Jan 22, 2004
    #3
  4. troutbum

    Gerald Klein Guest

    in vb I think it is going to be dt.DefaultView.Items(0) or something like
    that


    "Matthew Torline" <> wrote in message
    news:...
    > I was in line with you thinking as well. But I wasn't sure how to get
    > the real row index.
    >
    >
    > dsOnTime = New DataSet
    > daOnTime = New SqlDataAdapter(strSql, connWmsReporting)
    > connWmsReporting.Open()
    > daOnTime.Fill(dsOnTime, "OpenOrders")
    >
    > Dim dtOpenOrders As DataTable = dsOnTime.Tables(0)
    > Dim dtvOpenOrders As DataView = New DataView(dtOpenOrders)
    > If bolSearch Then
    > dtvOpenOrders.RowFilter = "FO_ORDERNUMBER LIKE '%" &
    > strSearch & "%'"
    > dtvOpenOrders.RowFilter = "MV_BOLNBR LIKE '%" &
    > strSearch & "%'"
    > dtvOpenOrders.RowFilter = "FO_CUSTID LIKE '%" &
    > strSearch & "%'"
    > dtvOpenOrders.RowFilter = "FO_CUSTNAME LIKE '%" &
    > strSearch & "%'"
    > End If
    >
    > dgOnTime.DataSource = dtvOpenOrders
    > dgOnTime.DataKeyField = "FO_ORDERNUMBER"
    > dgOnTime.DataBind()
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Gerald Klein, Jan 22, 2004
    #4
  5. Hi Matthew,

    You asked:
    When I edit a row in a DataGrid, I want to filter the rows to a smaller set
    when displaying it to the user in edit mode. How do I set
    DataGrid1.EditItemIndex correctly after I change the set of rows?

    ---
    If the DataView is sorted, then you can use DataView.Find(...). Otherwise,
    iterate through the items in the DataView to find the one you want. Below
    is a sample which uses iteration.

    To change this sample to use Find, change the sort from "au_lname" to
    "au_id". Then replace the code in the GetPosition function with: Return
    Dv.Find(ID)

    *** HTML
    <asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False">
    <Columns>
    <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update"
    CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
    <asp:BoundColumn DataField="au_id" ReadOnly="True"
    HeaderText="ID"></asp:BoundColumn>
    <asp:BoundColumn DataField="au_fname"
    HeaderText="First"></asp:BoundColumn>
    <asp:BoundColumn DataField="au_lname"
    HeaderText="Last"></asp:BoundColumn>
    </Columns>
    </asp:DataGrid>

    *** Code-behind
    Imports System.Data.SqlClient

    Const connString As String = "server='localhost'; trusted_connection=true;
    Database='pubs'"

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
    Bind(GetData(""))
    End If
    End Sub

    Private Sub Bind(ByVal Dv As DataView)
    DataGrid1.DataSource = Dv
    DataGrid1.DataBind()
    Dim i As Integer = 1
    End Sub

    Private Function GetData(ByVal Filter As String) As DataView
    Dim ConnString As String = "server='localhost'; trusted_connection=true;
    Database='pubs'"
    Dim Conn As SqlConnection = New SqlConnection(ConnString)
    Dim Qry As String = "SELECT au_id, au_fname, au_lname FROM authors"
    Dim Cmd As SqlCommand = New SqlCommand(Qry, Conn)
    Dim Adapter As New SqlDataAdapter(Cmd)
    Dim Ds As New Data.DataSet
    Adapter.Fill(Ds, "data")
    Dim Dv As DataView = Ds.Tables("data").DefaultView
    If Filter <> "" Then
    Dv.RowFilter = Filter
    End If
    Dv.Sort = "au_lname"
    Ds.Dispose()
    Adapter.Dispose()
    Cmd.Dispose()
    Conn.Dispose()
    Return Dv
    End Function

    Private Function GetPosition(ByVal Dv As DataView, ByVal ID As String) As
    Integer
    Dim i As Integer
    Dim Found As Integer
    Found = -1
    For i = 0 To Dv.Count
    If ID = Dv(i)("au_id") Then
    Found = i
    Exit For
    End If
    Next
    Return Found
    End Function

    Private Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As
    System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
    DataGrid1.EditCommand
    Dim s As String = Left(e.Item.Cells(3).Text, 1)
    Dim Dv As DataView = GetData("au_lname like '" & s & "%'")
    DataGrid1.EditItemIndex = GetPosition(Dv, e.Item.Cells(1).Text)
    Bind(Dv)
    End Sub

    Private Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As
    System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
    DataGrid1.CancelCommand
    DataGrid1.EditItemIndex = -1
    DataGrid1.SelectedIndex = -1
    Bind(GetData(""))
    End Sub

    Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As
    System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
    DataGrid1.UpdateCommand
    DataGrid1.EditItemIndex = -1
    DataGrid1.SelectedIndex = -1

    Dim conn As SqlConnection = New SqlConnection(connString)
    Dim queryString As String = "UPDATE authors SET au_fname=@First,
    au_lname=@Last WHERE au_id=@ID"
    Dim cmd As SqlCommand = New SqlCommand(queryString, conn)

    Dim parm As SqlClient.SqlParameter
    parm = New SqlClient.SqlParameter("@ID", SqlDbType.VarChar, 11)
    parm.Value = e.Item.Cells(1).Text
    cmd.Parameters.Add(parm)
    parm = New SqlClient.SqlParameter("@First", SqlDbType.VarChar, 20)
    parm.Value = CType(e.Item.Cells(2).Controls(0), TextBox).Text
    cmd.Parameters.Add(parm)
    parm = New SqlClient.SqlParameter("@Last", SqlDbType.VarChar, 40)
    parm.Value = CType(e.Item.Cells(3).Controls(0), TextBox).Text
    cmd.Parameters.Add(parm)
    conn.Open()
    cmd.ExecuteNonQuery()
    cmd.Dispose()
    conn.Dispose()

    Bind(GetData(""))
    End Sub


    Thank you, Mike
    Microsoft, ASP.NET Support Professional

    Microsoft highly recommends to all of our customers that they visit the
    http://www.microsoft.com/protect site and perform the three straightforward
    steps listed to improve your computer’s security.

    This posting is provided "AS IS", with no warranties, and confers no rights.
     
    Mike Moore [MSFT], Jan 24, 2004
    #5
  6. Hi Matthew,

    Thank you for posting in the community! My name is Jeffrey, and I will be
    assisting you on this issue.

    Based on my understanding, you use dataview to add row filter to datagrid's
    datasource, and do edit/update operation. But you did not figure out how to
    update all the change to the correct datasource row.(Can not get the
    correct datasource row index.)

    ===========================================================
    Based on my experience, you handle this in DataGrid.UpdateCommand event. In
    this event, I think you should use e.Item.DataSetIndex to get the
    datasource row index(That is the index in the dataview) and use
    DataView.RowFilter to get the datasource's record(You should choose an
    exclusive field as RowFilter).

    You can try the following Solution to see if it helps resolve your issue(I
    use the Sql Server's default table "jobs" in "pubs" database):

    private void DataGrid1_UpdateCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
    {
    DataRow dr=dataSet11.Tables[0].NewRow();

    dv.RowFilter="job_desc='"+dv[e.Item.DataSetIndex]["job_desc"]+"'";
    dv.Delete(0);

    dr["job_id"]=Convert.ToInt32(((TextBox)e.Item.Cells[1].Controls[0]).Text);
    dr["job_desc"]=((TextBox)e.Item.Cells[2].Controls[0]).Text;
    dr["min_lvl"]=((TextBox)e.Item.Cells[3].Controls[0]).Text;
    dr["max_lvl"]=((TextBox)e.Item.Cells[4].Controls[0]).Text;

    dv.RowFilter="";

    dataSet11.Tables[0].Rows.Add(dr);

    DataGrid1.EditItemIndex=-1;
    databind();
    }

    private void Page_Load(object sender, System.EventArgs e)
    {
    GetSource();
    if(!IsPostBack)
    {
    databind();
    }
    }

    private DataView dv;
    private void databind()
    {
    DataGrid1.DataSource=dv;
    DataGrid1.DataBind();
    }

    private void GetSource()
    {
    sqlDataAdapter1.Fill(dataSet11);
    dv=new DataView(dataSet11.Tables[0]);
    dv.RowFilter="job_id>=5";
    }

    private void DataGrid1_EditCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
    {
    DataGrid1.EditItemIndex=e.Item.ItemIndex;
    databind();
    }

    private void DataGrid1_CancelCommand(object source,
    System.Web.UI.WebControls.DataGridCommandEventArgs e)
    {
    DataGrid1.EditItemIndex=-1;
    databind();
    }

    In the UpdateCommand event, I use the "job_desc" field as the row filter.
    You also can use other fields, such as "job_id".

    ==============================================================
    Please apply my suggestion above and let me know if it helps resolve your
    problem.
    Actually, the MSDN DataGrid.UpdateCommand Event provide a good sample of
    doing this, for more information, please refer to:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
    frlrfsystemwebuiwebcontrolsdatagridclassupdatecommandtopic.asp

    My solution is writen in C# but the program logic is the same, I will help
    you to convert it into VB.net if you want.

    Thank you for your patience and cooperation. If you have any questions or
    concerns, please feel free to post it in the group. I am standing by to be
    of assistance.

    Best regards,
    Jeffrey Tan
    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
     
    Jeffrey Tan[MSFT], Jan 24, 2004
    #6
  7. troutbum

    troutbum Guest

    At this point I think you are correct, how ever I am not
    aware of how to find that rowindex from the main table,
    while using the dataview.

    >-----Original Message-----
    >DataTable dt = new DataTable();
    >dt.Rows[0];
    >dt.DefaultView[0];
    >
    >
    >
    >I am guessing that you are accessing the rowindex for the

    main table and not
    >for the view.
    >
    >
    >
    >"troutbum" <> wrote in message
    >news:...
    >> I have a datagrid that works great and edits/updates

    just fine. So I
    >> decided to add rowfilter via a textbox search. This

    works great,
    >> however when I click edit the values in the row all

    change to values
    >> in another row. This is confusing to the users.. How

    do I take
    >> advantage of the rowfilter and editcommand at the same

    time.
    >> Troutbum

    >
    >
    >.
    >
     
    troutbum, Jan 24, 2004
    #7
  8. Hi,

    The DataView does not have its own set of data. It only provides a filtered
    reference to the data which is actually stored in the underlying table.
    Therefore, when you modify the data in the DataView, you are actually
    modifying the data in the underlying table. Because of this, it is usually
    not necessary to find rows in the underlying table after you have already
    found them in the view.

    I think what you mean is that you send a filtered subset of data to the end
    user and they send it back with changes. Then you want to update the
    original table. To do this, the data which the end user sends back must
    have enough information in it for you to be able to find the corresponding
    row in the table. This often means including a unique key field with the
    data you send to the end user. Then, when they send it back, you can use
    that value to find the correct row in the table.

    For example, in one of the code samples posted in previous message to this
    thread, the Pubs Authors table was used and the unique filed "au_id" was
    used to find the correct row to update when the user submitted updates. In
    that example, the original DataTable and DataView were no longer available
    within the update function. So, we built a new SQL update query to update
    the data.

    Does this answer your question?

    Thank you, Mike
    Microsoft, ASP.NET Support Professional

    Microsoft highly recommends to all of our customers that they visit the
    http://www.microsoft.com/protect site and perform the three straightforward
    steps listed to improve your computer’s security.

    This posting is provided "AS IS", with no warranties, and confers no rights.


    --------------------
    > Content-Class: urn:content-classes:message
    > From: "troutbum" <>
    > Sender: "troutbum" <>
    > References: <>

    <#>
    > Subject: Re: Cannot Find Row Index after using row filter
    > Date: Sat, 24 Jan 2004 07:01:34 -0800
    > Lines: 34
    > Message-ID: <38a801c3e28a$f5430440$>
    > MIME-Version: 1.0
    > Content-Type: text/plain;
    > charset="iso-8859-1"
    > Content-Transfer-Encoding: 7bit
    > X-Newsreader: Microsoft CDO for Windows 2000
    > Thread-Index: AcPiivVADLn+QuYJTq2If1X4cioSuw==
    > X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    > Newsgroups: microsoft.public.dotnet.framework.aspnet.datagridcontrol
    > Path: cpmsftngxa07.phx.gbl
    > Xref: cpmsftngxa07.phx.gbl

    microsoft.public.dotnet.framework.aspnet.datagridcontrol:8102
    > NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
    > X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridcontrol
    >
    > At this point I think you are correct, how ever I am not
    > aware of how to find that rowindex from the main table,
    > while using the dataview.
    >
    > >-----Original Message-----
    > >DataTable dt = new DataTable();
    > >dt.Rows[0];
    > >dt.DefaultView[0];
    > >
    > >
    > >
    > >I am guessing that you are accessing the rowindex for the

    > main table and not
    > >for the view.
    > >
    > >
    > >
    > >"troutbum" <> wrote in message
    > >news:...
    > >> I have a datagrid that works great and edits/updates

    > just fine. So I
    > >> decided to add rowfilter via a textbox search. This

    > works great,
    > >> however when I click edit the values in the row all

    > change to values
    > >> in another row. This is confusing to the users.. How

    > do I take
    > >> advantage of the rowfilter and editcommand at the same

    > time.
    > >> Troutbum

    > >
    > >
    > >.
    > >

    >
     
    Mike Moore [MSFT], Jan 27, 2004
    #8
  9. Thanks to Mike I finally found the solution. I did run into one issue
    because I had allow paging on, but I used a Try Catch to trap the
    System.Web.HttpException for the followind exception: "Datagrid Paging
    Error: Invalid CurrentPageIndex value. It must be >= 0 and < the
    PageCount." Credit to Alvin at the error bank for that solution.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Matthew Torline, Jan 30, 2004
    #9
  10. Thanks to Mike I finally found the solution. I did run into one issue
    because I had allow paging on, but I used a Try Catch to trap the
    System.Web.HttpException for the followind exception: "Datagrid Paging
    Error: Invalid CurrentPageIndex value. It must be >= 0 and < the
    PageCount." Credit to Alvin at the error bank for that solution.




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Matthew Torline, Jan 30, 2004
    #10
  11. troutbum

    Andre Guest

    I ran into a similar message "Invalid CurrentPageIndex value. It must
    be >= 0 and < the PageCount." under the same circumstances.

    It turned out that I was making multiple trips to the database from
    separate dataset instances in separate subroutines: the first time to
    initially bind data to the datagrid and then every time my
    CurrentIndexChanged subroutine ran. I had declared the dataset as a
    separate private object in each subroutine, I don't think the event
    handler liked this very much.

    My (successful) solution was to declare the dataset as a protected
    object for the class, then reference the same dataset table instance
    from both subroutines. It eliminated this error, and reduced calls to
    the database.




    Matthew Torline <> wrote in message news:<>...
    > Thanks to Mike I finally found the solution. I did run into one issue
    > because I had allow paging on, but I used a Try Catch to trap the
    > System.Web.HttpException for the followind exception: "Datagrid Paging
    > Error: Invalid CurrentPageIndex value. It must be >= 0 and < the
    > PageCount." Credit to Alvin at the error bank for that solution.
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Andre, Feb 19, 2004
    #11
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark B
    Replies:
    0
    Views:
    384
    Mark B
    Jan 12, 2009
  2. D
    Replies:
    0
    Views:
    230
  3. Tomasz Chmielewski

    sorting index-15, index-9, index-110 "the human way"?

    Tomasz Chmielewski, Mar 4, 2008, in forum: Perl Misc
    Replies:
    4
    Views:
    329
    Tomasz Chmielewski
    Mar 4, 2008
  4. Stefan Mueller
    Replies:
    7
    Views:
    200
    Thomas 'PointedEars' Lahn
    Nov 28, 2005
  5. Giggle Girl

    ie6 Insert Row at specific Row Index of Table

    Giggle Girl, Feb 5, 2006, in forum: Javascript
    Replies:
    18
    Views:
    298
    Thomas 'PointedEars' Lahn
    Feb 7, 2006
Loading...

Share This Page