Cannot Find Row Index after using row filter

[troutbum]

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]

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" <matt.torline@bissell.com> wrote in message
news:36351922.0401212037.4e89f504@posting.google.c om...

> 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

[Matthew Torline]

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 [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Gerald Klein]

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


"Matthew Torline" <matt.torline@bissell.com> wrote in message
news:ORRjW5P4DHA.1504@TK2MSFTNGP12.phx.gbl...

> 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 [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Mike Moore [MSFT]]

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
[url]http://www.microsoft.com/protect[/url] 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.

[Jeffrey Tan[MSFT]]

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:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/[/url]
frlrfsystemwebuiwebcontrolsdatagridclassupdatecomm andtopic.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! - [url]www.microsoft.com/security[/url]
This posting is provided "as is" with no warranties and confers no rights.

[troutbum]

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" <matt.torline@bissell.com> wrote in message
>news:36351922.0401212037.4e89f504@posting.google. com...

>> 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]]

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
[url]http://www.microsoft.com/protect[/url] 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" <anonymous@discussions.microsoft.com>
> Sender: "troutbum" <anonymous@discussions.microsoft.com>
> References: <36351922.0401212037.4e89f504@posting.google.com >

<#vBX86O4DHA.2388@TK2MSFTNGP09.phx.gbl>

> 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$a401280a@phx.gbl>
> 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.datagridc ontrol
> Path: cpmsftngxa07.phx.gbl
> Xref: cpmsftngxa07.phx.gbl

microsoft.public.dotnet.framework.aspnet.datagridc ontrol:8102

> NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
> X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridc ontrol
>
> 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" <matt.torline@bissell.com> wrote in message
> >news:36351922.0401212037.4e89f504@posting.google. com...

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

> >
> >
> >.
> >

>

[Matthew Torline]

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 [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Matthew Torline]

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 [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Andre]

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 <matt.torline@bissell.com> wrote in message news:<ugStXJ25DHA.2692@TK2MSFTNGP09.phx.gbl>...

> 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 [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!