DataSet Find Problem

[EdSimmons]

I'm attempting to do a Find in the DefaultView of a DataSet for an

EmployeeID, the Find returns the correct index position based on a sort of

EmployeeID. As soon as I change my DataAdapter's initial query to use

"ORDER BY LastName", the index position is off because it still returns the

index based on the DataSet's EmployeeID Sort. If I change the DataSet's

Sort to LastName, I can only Find by LastName, which obviously defeats the

purpose.

 

Any help would be appreciated.

[Nerseus]

Do you need Find because you're databinding individual controls (using BindingContext and Position)? If you just need to get access to the row to look at some values, you can use the DataTable's Select method (which allows a sort, filter, etc. and returns a DataRow array).

 

-nerseus

[EdSimmons]

Yes, we are using databinding, BindingContext, and Position. The only way we've seen so far to simulate the old fashioned find is to loop through the entire recordset checking each value, which is obviously quite annoying.

 

I'm surprised, or maybe not, that Microsoft seems to have made a lot of the basic tasks done in a database application so difficult.

[Nerseus]

I agree in that the Find seems to be stricken with some problems like the one you mention. It's too bad the BindingContext doesn't support some better way to get to a row than an ordinal position.

 

Luckily for me (unlucky for you maybe), I haven't had to do this kind of binding - I generally use grids when showing more than one row. Or when showing only one row at a time but I need paging, I've had alternate solutions (like also using a grid to select the desired row - which updates the binding context).

 

If you do find an answer, please post. I seem to recall hearing this type of question asked before. You might also try the MS forums, especially the one for Data, to see if anything shows up there.

 

-nerseus

[jfackler]

Might sound convoluted, but it only takes a few lines of code to do a .select followed by a .find based on the employeeID from the row returned....i.e. leave your find based on the EmployeeID and just find the EmployeeID based on whatever you want to search for. For example given a combobox that you might select an employees name from:

Dim arSelectResult As DataRow() 'an array of datarows
Dim EmpID As String 'or integer or whatever your EmpID type is

arSelectResult = dataset1.Tables("Employees").Select("Name = " & cmbEmployeeName.SelectedText) 'returns an array of rows with that employees 
'name selected, for example, in a combobox populated 
'from your dataset in the format :
'cmbEmployeeName.DataSource = dataset1.Tables("Employees")
'cmbEmployeeName.DisplayMember = "Name"


EmpID = CStr(arSelectResult(0).Item("EmployeeID")) 'assumes the employee name is unique and only returns one row

 

yields the EmployeeID which you can then use in your .Find

 

Hope it helps,

Jon

[EdSimmons]

We already have the ID of the record, the issue is that the .Find yields a wrong position when an ORDER BY is used in the SQL procedure.

[jfackler]

The dataview find method returns the index of the row that was found, based on the primary key values. If you want to find a row based on some other column value, you must use the rowfilter property of the dataview.

 

dv.RowFilter = "EmployeeID = " & EmployeeID
            
' Simple bind Employee Name to a TextBox control
Text1.DataBindings.Add("Text", dv, "EmployeeName")

 

That help?

 

Jon

[EdSimmons]

No. As soon as you say "The dataview find method returns the index of the row that was found" I'm saying that in certain situations you are wrong. When the SQL query is loaded with "ORDER BY" the index returned isn't correct.

 

If you have access to a Northwind database I've attached a form that proves the issue. You need to delete a record out of the employees table so that the ID's are not 100% in order and then just change the connection string.

form1.vb

[jfackler]

Ed,

Those situations are when the PrimaryKey is not the Column you are ordering by.

Try the rest of the solution I gave you....i.e. dv.rowfilter.

It returns a dv filtered with just the value for the column you provide.

Binding to the columns of the returned dv then should give you the solution you are looking for.

 

Jon

[EdSimmons]

I don't think I'm getting across what we have to do, but I think Nerseus understood it. I want the ability to use back and next through the database, which means I want the data sorted by the full name of the person. I also want to be able to find based on the id, which is off when it's sorted by the full name.

 

In the example you provided my dataset is filtered to one ID, which means advancing to the next name in the list is impossible unless we know the id and refilter. Correct?

[jfackler]

Correct and,

Sorry for the confusion. I thought you just wanted to find a specific EmployeeId and show that employees info in bound textboxes.

However, you mentioned you didn't want to have to loop through all the rows of data. Is there some reason you want to do that?

I'm going to work with the file you provided and create a way to, via an input field in a text box, let you move to any employee by name or id. I'll post the code back here.

 

Right now, have to run. I'll get back soon.

 

 

Jon

[jfackler]

Ed,

This, I think does what you want.

I've added a button to "Find by Employee Name" and changed the Find #11 button to "Find by Employee ID".

Either way, click the appropriate button and scroll through by either Full Name or Employee Id.

I also added another textbox to watch the EmployeeId while you scroll.

 

Let me know what you think,

Jon

form1.vb

[EdSimmons]

The problem with this method is that it doesn't allow me to maintain the next/back buttons based on the FullName load since you reload the dataset each time. So it enables the find, but destroys the next/back positioning ability.

 

In addition to that I'm forced to reload all, in this case 2000 but growing, records everytime the user does anything.

 

In the old method you could load the recordset once in any ordering you wanted, move next and previous at will, and search any field you wanted regardless of the sort order. And if you wanted to change the sort you didn't need to reload the recordset each time.

[jfackler]

In addition to that I'm forced to reload all, in this case 2000 but growing, records everytime the user does anything.

In .Net, if you're going to manipulate the data a dataset is the ideal tool. The advantage is the disconnected architecture. Does take some re thinking of solutions but the saving in resource over head is worth the trouble in the long run. If you simply want to read the data you might check into a datareader....very fast but forward only.

 

In the old method you could load the recordset once in any ordering you wanted, move next and previous at will, and search any field you wanted regardless of the sort order. And if you wanted to change the sort you didn't need to reload the recordset each time.

The "old method" maintained a connection to the data and so utilized more of the aforementioned resources. The recordset was never disconnected from the data and so new views were simple, like you mentioned. I think the aspect of .NET that makes it unique from VB of old is the disconnected architecture (although there are many other unique qualities to .NET not the least of which is the OOP factor). If you are going to manipulate your data, I humbly suggest not doing the job the same way you did with VB6. The names are similar but the solutions are, in some cases, radically different.

 

Jon

[EdSimmons]

I'd agree on taking a different route, unfortunately a lot of clients are used to the MS Access feel of doing things. If .NET is drastically changing things so that you can't give the user a similar feel for basic applications I'm pretty disappointed.

[jfackler]

Oh, (this will probably prompt some further comments) I think you can still give the clients a similar feel. They don't/won't know that you are being more efficient if your interface doesn't change much. On the other hand, maybe an interface change is warranted to improve how they interact with their data. They may not know how much better (I use that word cautiously) they can do. We should provide them with the most efficient sollution that meets their individual needs. I suppose that sounds like Microsoft mentality.....impose "improvement" whether anyone recognizes/wants it or not....We're kind of moving off topic though, Mods: If this thread continues, we should probably move it to random thoughts.

Jon

[Nerseus]

Assuming the manual looping works, how slow is it? Assuming you have 2000 rows and you have to loop through each one to find the right name, is it so long that the users will notice? Maybe it's not as "clean" as a simple Find method, but if it works at a good enough pace why not use it?

 

Try something like:

Dim dr As DataRowView 
Dim i As Integer = 0
For Each dr In dv
   If (dr["EmpName" = "Dan") 
       Exit For
   End If
   i = i + 1
Next

 

My VB.NET isn't that good, but I hope you get the idea. I don't know if that will work with the sorting or not...

 

-Nerseus

[EdSimmons]

Nerseus

 

This is the exact solution I had my employee use for now. With 2000 records it's not bad, but I hated having him use a solution that I didn't feel was very clean. If you run into other people with this issue it's a viable solution so far for us.

[jfackler]

Ed,

I was frustrated with the problem you presented, so I pursued a different solution.

The problem would seem to be one of binding, not sorting.

Check the attached.

I changed the way the source to which the data was bound and

created a dataview from the dataset to achieve what I think is a good solution to your question.

 

As you probably found before, you'll need to change the connection string.

 

 

Let me know what you think.

 

Jon

form1.vb