DataView.Sort Dilemma - A bit long

  • Thread starter Thread starter runningdog
  • Start date Start date
R

runningdog

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
 
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 said:
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
Click to expand...

-------- 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?
Click to expand...

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

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

Regards
Joyjit
 
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."
 
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.
 
The answer seems to be to use the
Table.DefaultView.Table.Rows.Add(row) method

Not the

Table.Rows.Add(row)

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

Steve
 
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."
 
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
 
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."
 
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 said:
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."
Click to expand...
 
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."
 
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.
 
Back
Top