DataView.Sort Dilemma - A bit long

I have a dataset contining sorted data in mutilple tables from which I am
selecting rows to action.
I use the Find method of the DefaultView from a specific Table to get the
position of a row in the table.
I then set the position of the BindingContext to action the row.

eg.
Table.DatafaultView.Sort = "ColumnName"
pos=Table.DatafaultView.Find(key)
BindingContext(DataSet,Table).Position = pos

This works fine to switch between existing rows.
When I add a row to the DataSet (without updating the DataAdpter, because I
don't want to save the changes at this point) the above method returns
incorrect rows.

eg. If the existing data looks like this

pos Key
------------
0 123
1 234
2 345

and I then add the record 111

pos=Table.DatafaultView.Find(111) will return 0 as it should.
BindingContext(DataSet,Table).Position = 0 will bind to 123

pos=Table.DatafaultView.Find(123) will return 1
BindingContext(DataSet,Table).Position = 1 will bind to 234

So it appears that the data in the DataSet looks like

pos Key
------------
0 123
1 234
2 345
3 111

Is there something I need to do, after an Table.Rows.Add(row), to the
DefaultView or DataSet to have it rebuild its sort index?

TIA Steve

William
Tue Aug 17 22:55:54 CDT 2004

See comments inline:

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
"runningdog" <runningdog@reply.to.newsgroup> wrote in message
news:4122a788$0$77153$c30e37c6@lon-reader.news.telstra.net...
> I have a dataset contining sorted data in mutilple tables from which I am
> selecting rows to action.
> I use the Find method of the DefaultView from a specific Table to get the
> position of a row in the table.
> I then set the position of the BindingContext to action the row.
>
> eg.
> Table.DatafaultView.Sort = "ColumnName"
> pos=Table.DatafaultView.Find(key)
> BindingContext(DataSet,Table).Position = pos
>
> This works fine to switch between existing rows.
> When I add a row to the DataSet (without updating the DataAdpter, because
I
> don't want to save the changes at this point) the above method returns
> incorrect rows.
>
> eg. If the existing data looks like this
>
> pos Key
> ------------
> 0 123
> 1 234
> 2 345
>
> and I then add the record 111

-------- What value corresponds to 111? from your data below it looks like
3 should correspond to it (not sure if it's an Autoincrement field or how it
gets assigned - basically, you say that 111 should return 0 but I'm losing
you here - what, if it were working correctly woudl the value be.
>
> pos=Table.DatafaultView.Find(111) will return 0 as it should.
> BindingContext(DataSet,Table).Position = 0 will bind to 123
>
> pos=Table.DatafaultView.Find(123) will return 1
> BindingContext(DataSet,Table).Position = 1 will bind to 234
>
> So it appears that the data in the DataSet looks like
>
> pos Key
> ------------
> 0 123
> 1 234
> 2 345
> 3 111
>
> Is there something I need to do, after an Table.Rows.Add(row), to the
> DefaultView or DataSet to have it rebuild its sort index?

See my question above, I guess I got a little confused. Anyway, if you need
it sorted on the Key column only, what's happening if you specify sort at
the end after you've added the Row? Also, by any chance do you have your
rowstate filter set? I dont' see it so I'm guessing you don't, but if it
was specified to Added for instance then it would cause the position to
change. Regarding the view, I'm fairly sure (I don't have my IDE in front
of me right now) that if the column is set as the sort and you insert a row
into the table, that it will move it according to the sort. I know I did
this with a gird and turned off sorting in the grid, prograqmatically
specifying the sort on the view and as soon as Add was called, everything
moved where I wanted it. I'm probalby not of much help b/c I don't
understand the position thing (ie 111 returns 0 but so does 123 if I read it
correclty). As far as filtering in general though, you could bind to a view
as well, and then just set the rowfilter to '111' for instance which should
show only that record provided it meets the criteria
>
> TIA Steve
>
>
>
>

Joyjit
Wed Aug 18 01:55:18 CDT 2004

Hi,

Tyr calling the AcceptCahnges method of the Dataset after adding the new
rows.

Regards
Joyjit

"runningdog" <runningdog@reply.to.newsgroup> wrote in message
news:4122a788$0$77153$c30e37c6@lon-reader.news.telstra.net...
> I have a dataset contining sorted data in mutilple tables from which I am
> selecting rows to action.
> I use the Find method of the DefaultView from a specific Table to get the
> position of a row in the table.
> I then set the position of the BindingContext to action the row.
>
> eg.
> Table.DatafaultView.Sort = "ColumnName"
> pos=Table.DatafaultView.Find(key)
> BindingContext(DataSet,Table).Position = pos
>
> This works fine to switch between existing rows.
> When I add a row to the DataSet (without updating the DataAdpter, because
I
> don't want to save the changes at this point) the above method returns
> incorrect rows.
>
> eg. If the existing data looks like this
>
> pos Key
> ------------
> 0 123
> 1 234
> 2 345
>
> and I then add the record 111
>
> pos=Table.DatafaultView.Find(111) will return 0 as it should.
> BindingContext(DataSet,Table).Position = 0 will bind to 123
>
> pos=Table.DatafaultView.Find(123) will return 1
> BindingContext(DataSet,Table).Position = 1 will bind to 234
>
> So it appears that the data in the DataSet looks like
>
> pos Key
> ------------
> 0 123
> 1 234
> 2 345
> 3 111
>
> Is there something I need to do, after an Table.Rows.Add(row), to the
> DefaultView or DataSet to have it rebuild its sort index?
>
> TIA Steve
>
>
>
>

v-kevy
Wed Aug 18 04:02:33 CDT 2004

Hi Steve,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that the positioning doesn't work properly
after you add a row to the DataSet. If there is any misunderstanding,
please feel free to let me know.

I have written a test program according to your description. However, this
one works fine on my machine whether there is row added or not. Could you
please try this code on your machine? It gets data from Employees table in
Northwind database.

private void Form1_Load(object sender, System.EventArgs e)
{
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Employees",
this.sqlConnection1);
this.ds = new DataSet();
sda.Fill(ds, "Employees");
ds.Tables["Employees"].DefaultView.Sort = "LastName";

this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = "Employees";
}

private void button1_Click(object sender, System.EventArgs e)
{
int pos = this.ds.Tables[0].DefaultView.Find(this.textBox1.Text);
BindingContext[ds,"Employees"].Position = pos;
}

If that still doens't work, could you please show me the code that you are
binding to data source?

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

fanor
Thu Aug 19 20:05:07 CDT 2004

Hi,

I got the same problem, the way a solve the problem was to update de
data set, clear the data table, fill the table and find the row. I know
this is very inefficient but is the only way it worked. There must be
another way, but what the other guys said didn't work for me. By the way
, I'm using c# and an ACCESS database.



runningdog wrote:
> I have a dataset contining sorted data in mutilple tables from which I am
> selecting rows to action.
> I use the Find method of the DefaultView from a specific Table to get the
> position of a row in the table.
> I then set the position of the BindingContext to action the row.
>
> eg.
> Table.DatafaultView.Sort = "ColumnName"
> pos=Table.DatafaultView.Find(key)
> BindingContext(DataSet,Table).Position = pos
>
> This works fine to switch between existing rows.
> When I add a row to the DataSet (without updating the DataAdpter, because I
> don't want to save the changes at this point) the above method returns
> incorrect rows.
>
> eg. If the existing data looks like this
>
> pos Key
> ------------
> 0 123
> 1 234
> 2 345
>
> and I then add the record 111
>
> pos=Table.DatafaultView.Find(111) will return 0 as it should.
> BindingContext(DataSet,Table).Position = 0 will bind to 123
>
> pos=Table.DatafaultView.Find(123) will return 1
> BindingContext(DataSet,Table).Position = 1 will bind to 234
>
> So it appears that the data in the DataSet looks like
>
> pos Key
> ------------
> 0 123
> 1 234
> 2 345
> 3 111
>
> Is there something I need to do, after an Table.Rows.Add(row), to the
> DefaultView or DataSet to have it rebuild its sort index?
>
> TIA Steve
>
>
>
>

runningdog
Sun Aug 22 23:07:51 CDT 2004

The answer seems to be to use the
Table.DefaultView.Table.Rows.Add(row) method

Not the

Table.Rows.Add(row)

Steve

"fanor" <xreed2000@yahoo.com> wrote in message
news:#9PM8GlhEHA.704@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I got the same problem, the way a solve the problem was to update de
> data set, clear the data table, fill the table and find the row. I know
> this is very inefficient but is the only way it worked. There must be
> another way, but what the other guys said didn't work for me. By the way
> , I'm using c# and an ACCESS database.
>
>
>
> runningdog wrote:
> > I have a dataset contining sorted data in mutilple tables from which I
am
> > selecting rows to action.
> > I use the Find method of the DefaultView from a specific Table to get
the
> > position of a row in the table.
> > I then set the position of the BindingContext to action the row.
> >
> > eg.
> > Table.DatafaultView.Sort = "ColumnName"
> > pos=Table.DatafaultView.Find(key)
> > BindingContext(DataSet,Table).Position = pos
> >
> > This works fine to switch between existing rows.
> > When I add a row to the DataSet (without updating the DataAdpter,
because I
> > don't want to save the changes at this point) the above method returns
> > incorrect rows.
> >
> > eg. If the existing data looks like this
> >
> > pos Key
> > ------------
> > 0 123
> > 1 234
> > 2 345
> >
> > and I then add the record 111
> >
> > pos=Table.DatafaultView.Find(111) will return 0 as it should.
> > BindingContext(DataSet,Table).Position = 0 will bind to 123
> >
> > pos=Table.DatafaultView.Find(123) will return 1
> > BindingContext(DataSet,Table).Position = 1 will bind to 234
> >
> > So it appears that the data in the DataSet looks like
> >
> > pos Key
> > ------------
> > 0 123
> > 1 234
> > 2 345
> > 3 111
> >
> > Is there something I need to do, after an Table.Rows.Add(row), to the
> > DefaultView or DataSet to have it rebuild its sort index?
> >
> > TIA Steve
> >
> >
> >
> >
>

runningdog
Sun Aug 22 23:30:52 CDT 2004

No it isn't. The problem seems to be with using the DefaultView rather than
creating a custom DataView.

Steve

"runningdog" <runningdog@reply.to.newsgroup> wrote in message
news:41296de7$0$38032$c30e37c6@lon-reader.news.telstra.net...
> The answer seems to be to use the
> Table.DefaultView.Table.Rows.Add(row) method
>
> Not the
>
> Table.Rows.Add(row)
>
> Steve
>
> "fanor" <xreed2000@yahoo.com> wrote in message
> news:#9PM8GlhEHA.704@TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I got the same problem, the way a solve the problem was to update de
> > data set, clear the data table, fill the table and find the row. I know
> > this is very inefficient but is the only way it worked. There must be
> > another way, but what the other guys said didn't work for me. By the way
> > , I'm using c# and an ACCESS database.
> >
> >
> >
> > runningdog wrote:
> > > I have a dataset contining sorted data in mutilple tables from which I
> am
> > > selecting rows to action.
> > > I use the Find method of the DefaultView from a specific Table to get
> the
> > > position of a row in the table.
> > > I then set the position of the BindingContext to action the row.
> > >
> > > eg.
> > > Table.DatafaultView.Sort = "ColumnName"
> > > pos=Table.DatafaultView.Find(key)
> > > BindingContext(DataSet,Table).Position = pos
> > >
> > > This works fine to switch between existing rows.
> > > When I add a row to the DataSet (without updating the DataAdpter,
> because I
> > > don't want to save the changes at this point) the above method returns
> > > incorrect rows.
> > >
> > > eg. If the existing data looks like this
> > >
> > > pos Key
> > > ------------
> > > 0 123
> > > 1 234
> > > 2 345
> > >
> > > and I then add the record 111
> > >
> > > pos=Table.DatafaultView.Find(111) will return 0 as it should.
> > > BindingContext(DataSet,Table).Position = 0 will bind to 123
> > >
> > > pos=Table.DatafaultView.Find(123) will return 1
> > > BindingContext(DataSet,Table).Position = 1 will bind to 234
> > >
> > > So it appears that the data in the DataSet looks like
> > >
> > > pos Key
> > > ------------
> > > 0 123
> > > 1 234
> > > 2 345
> > > 3 111
> > >
> > > Is there something I need to do, after an Table.Rows.Add(row), to the
> > > DefaultView or DataSet to have it rebuild its sort index?
> > >
> > > TIA Steve
> > >
> > >
> > >
> > >
> >
>
>

v-kevy
Mon Aug 23 04:45:42 CDT 2004

Hi Steve,

Table.DefaultView.Table equal so Table. Did you use the same code as mine
to bind the DataGrid to DataTable?

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

runningdog
Mon Aug 23 18:24:17 CDT 2004

Kevin,

My service provider seems to have dropped some messages from this thread.
This is the first responce I've seen from you on this thread.

To clarify my problem.

I am not specifically concerned with DataGrid to DataTable binding.
The control that I am working on does not contain a DataGrid but consists of
a number of TextBoxes bound to a sorted DataSet.
I am using the DefaultView.Find to locate the required row and it all works
find until I add new rows to the dataset and then Find returns the wrong
row.

However I have found that if I creat a new DataView and use it's Find method
the same code now works even when I add rows.
The app that the code is in is rather complex so it is bit hard to isolate
but I am putting together a test that I'll post.

Thanks Steve

"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:l4eYJYPiEHA.2228@cpmsftngxa10.phx.gbl...
> Hi Steve,
>
> Table.DefaultView.Table equal so Table. Did you use the same code as mine
> to bind the DataGrid to DataTable?
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

v-kevy
Tue Aug 24 03:23:48 CDT 2004

Hi Steve,

The following is my first reply to the issue. I have tried to change the
DefaultView on my machine, however, this worked perfect.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that the positioning doesn't work properly
after you add a row to the DataSet. If there is any misunderstanding,
please feel free to let me know.

I have written a test program according to your description. However, this
one works fine on my machine whether there is row added or not. Could you
please try this code on your machine? It gets data from Employees table in
Northwind database.

private void Form1_Load(object sender, System.EventArgs e)
{
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Employees",
this.sqlConnection1);
this.ds = new DataSet();
sda.Fill(ds, "Employees");
ds.Tables["Employees"].DefaultView.Sort = "LastName";

this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = "Employees";
}

private void button1_Click(object sender, System.EventArgs e)
{
int pos = this.ds.Tables[0].DefaultView.Find(this.textBox1.Text);
BindingContext[ds,"Employees"].Position = pos;
}

If that still doens't work, could you please show me the code that you are
binding to data source?

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

runningdog
Tue Aug 24 20:12:36 CDT 2004

Kevin,

The problem here is not with a DataGrid.
I have a form with a number of TextBoxes bound to a DataSet and an find
method that selects the row to bind to.
This works fine until I programatically add a row to the dataset.
I have got around this problem by binding the Textboxes to a DataView then
adding the rows using the DataViews NewRow method.

The following example works

Me.tbCompanyName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.DV, "CompanyName"))
...
Private Sub btAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btAdd.Click
Dim row As DataRow
row = DV.Table.NewRow()
'DV is a dataview
row.Item("CustomerId") = CustomerId.Text
row.Item("CompanyName") = tbCompanyName.Text
DV.Table.Rows.Add(row)
Me.BindingContext(DV).EndCurrentEdit()
End Sub

But the following does not

Me.tbCompanyName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.DS, "Customers.CompanyName"))
...
Private Sub btAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btAdd.Click
Dim row As DataRow
row = DS.tables("Customers").NewRow()
row.Item("CustomerId") = CustomerId.Text
row.Item("CompanyName") = tbCompanyName.Text
DS.tables("Customers").Rows.Add(row)
Me.BindingContext(DS, "Customers").EndCurrentEdit()
End Sub

It will add the row and a Find will return the expected position but

Me.BindingContext(DS, "Customers").Position =
DS.Tables("Customers").DefaultView.Find(key)

binds to the wrong row. It binds original row occupying the postion returned
by the find.
I am out of trouble with by using DataViews but would be interested to know
what I am doing wrong.

Thanks Steve

"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:j1dM1ObiEHA.2228@cpmsftngxa10.phx.gbl...
> Hi Steve,
>
> The following is my first reply to the issue. I have tried to change the
> DefaultView on my machine, however, this worked perfect.
>
> First of all, I would like to confirm my understanding of your issue. From
> your description, I understand that the positioning doesn't work properly
> after you add a row to the DataSet. If there is any misunderstanding,
> please feel free to let me know.
>
> I have written a test program according to your description. However, this
> one works fine on my machine whether there is row added or not. Could you
> please try this code on your machine? It gets data from Employees table in
> Northwind database.
>
> private void Form1_Load(object sender, System.EventArgs e)
> {
> SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Employees",
> this.sqlConnection1);
> this.ds = new DataSet();
> sda.Fill(ds, "Employees");
> ds.Tables["Employees"].DefaultView.Sort = "LastName";
>
> this.dataGrid1.DataSource = ds;
> this.dataGrid1.DataMember = "Employees";
> }
>
> private void button1_Click(object sender, System.EventArgs e)
> {
> int pos = this.ds.Tables[0].DefaultView.Find(this.textBox1.Text);
> BindingContext[ds,"Employees"].Position = pos;
> }
>
> If that still doens't work, could you please show me the code that you are
> binding to data source?
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

v-kevy
Thu Aug 26 08:50:53 CDT 2004

Hi Steve,

The latter code doesn't work because it has selected wrong object as
DataSource. When you are using a DataSet as data source, your program will
pick up Customers.CompanyName directly as values. So if your table is
sorted, it is highly recommended to use DataView as data source.

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

runningdog
Mon Aug 30 06:54:10 CDT 2004

Thanks Kevin,

This confirms my conclusion that a DefaultView is not inter-changeable with
a DataView and changing my code to use a dataview has fixed my immediate
problem. This does raise a potential problem filtering a DataGrid bound to a
Relationship but maybe I'll cross that bridge if I get to it.

Thanks for your help

Regards Steve.

"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:VAcQ8O3iEHA.2936@cpmsftngxa10.phx.gbl...
> Hi Steve,
>
> The latter code doesn't work because it has selected wrong object as
> DataSource. When you are using a DataSet as data source, your program will
> pick up Customers.CompanyName directly as values. So if your table is
> sorted, it is highly recommended to use DataView as data source.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>