Sorting recordsets
-
Does anyone know how to sort a recordset using pure VB code, rather than SQL? For instance, I have a textbox/field on my form, which is attached to a Data Control, which in turn is attached to an externale file; an MS Access table. I just want to know the syntax to be able to alphabetically sort all the records in the textbox. If SQL is the only way, then I'd still appreciate the input, but with great detail, as I have no idea how to implement SQL into VB code.
I know there's a kind sole willing to help! -
I guess a pure VB way would be to implement a complete sort routine based on comparison and stuff like that between the values. I can't think of something efficient right now... but I will let you know.
By the way, how can you work with recordsets if you say that you don't know how to implement SQL..?
Regards,
Uh-Oh. -
I do not understand the problem enough to help:
1. if the textbox is connected to a datafield it will display only one entry, what do you want to sort?
2. if it is a list, combo, ... there is a .sort method and you have to specify the field you want the sorting
3. if you attach a source, instead of specifying the name of the field of the table, you should write:
SELECT FieldName_ FROM TableName ORDER BY _FieldName_ ASC;
replcae ASC with DESC if you want topdown order.
If this doesn't answer your question, please elaborate a more complete question... -
If it's an ADO recordset you can also use the Sort method if you have opened the recordset with a Static or Dynamic Cursor.
Use it like this:
rs.Sort = "[fieldname] ASC"
ASC is ascending and DESC is descending order. Just replace the ASC in the line above with DESC to sort it the opposite way. -
... Further to above message: (it won't let me edit it. says I don't have the permissions)
The nice thing with this is you don't have to requery the database again to sort it by different fields in different directions. In the majority of cases this is a faster way of doing things as you are working in local memory and not making network requests. -
Ok, let me answer all your questions one by one, then provide my own! ...
Reptile, first of all, I'm working with recordsets normally, without using any SQL. VB provides an ActiveX control (Data Control) which enables you to connect to an external DBMS (Database Management System), such as Microsoft Access. So the Data Control is what I use to connect to MS Access, then I connect various Text Boxes to this Data Control. The Data Control calls upon the fields that are in the table that's located in MS Access and attaches their value to the textbox, then you may call the actual values from the table and place them in the Text Boxes on your form. All this can be done during Design Time.
I may have gone into more detail than I should have...
Anyways, moving on to Hobbes68... See, I don't actually have only one Text Box on my form. I have about 46 or so, but in my example I just used a simplified version by saying there's only 1 Text Box. Speaking of which, what you said confused me a little. So what if there's only 1 Text Box? Why WOULDN'T you be able to sort just one text box alphabetically? You may have just one entry in the Text Box with the first entry starting with the letter 'Z', and the next entry starting with the letter 'A' and you want to sort in ascending order.
Finally, CodeWarrior76... I tried using your idea:
rs.Sort = "[fieldname] ASC"
Yet it doesn't even do anything for some reason. Not even an error message. I put it after the Update command, which updates the database. Like this:
datPersonal.Recordset.Sort = "fldLastName ASC"
That's what I tried yet it didn't do anything...
I appreciate all your help people, thanks. -
together with a .sort method there is a .sorted properties that needs to be set to TRUE.
Furthermore, after a sort method you don't have to UPDATE the database, you have to refresh the recordset ...
The fact that you specified a single textbox mislead me because it seemed that you wanted to sort that single field rather than the recordset being shown, one record at a time, through the several textbox on your form. -
hobbes68 >> hey, good call! I forgot about that. haha .. I think I would have pulled my own hair out on that for a bit.
mza >> throw that in there.
rs.sorted = True -
Darryl... regarding the problem editing a forum msg, remind me and I'll try to get it fixed... it works fine for me... hehe ;-)
-
Sorry guys but "Object doesn't support this propery or method", or so the stupid program says. Right after I update the recordset (or at any other time for that matter), I enter: datPersonal.Recordset.Sorted = True ... but the problem is that there is no method called "Sorted" for recordsets. The method exists in other controls, but not the recordset. And obviously refreshing the recordset is useless if it won't even sort it. I have already tried refreshing after the .Sort method however, yet it still doesn't work. No error message or anything.
-
Malek said:
Quote: Sorry guys but "Object doesn't support this propery or method", or so the stupid program says. Right after I update the recordset (or at any other time for that matter), I enter: datPersonal.Recordset.Sorted = True ... but the problem is that there is no method called "Sorted" for recordsets. The method exists in other controls, but not the recordset. And obviously refreshing the recordset is useless if it won't even sort it. I have already tried refreshing after the .Sort method however, yet it still doesn't work. No error message or anything.
First, to James,
See the name that this say's the quote is from ... that was already in the message screen ... and it's a reply to mza. just thought I'd let you know ... also if I'm pointing out too much stuff tell me to stop. i know what it's like to be in testing mode and have bugs thrown at you.
Secondly, mza,
Wow, I just noticed you're from London Ontario. I grew up there. I've only been in Calgary for 2 years. what part of town you from? I lived in Huron Heights nieghbourhood nearest Huron St. and Clarke Side Road. If you're willing, send me your code and DB zipped in one file to [email protected] I'll take a look and try to figure out your problem. -
This is a nightmare... my hard drive died on me... I lost 3 gigs of data. I gained 3 gigs of white hair... and I'm about to pull it all out. What does that have to do with VB? Well, my 3000 line program was on the computer and I lost it, but I DID make a backup of it a few weeks ago though, so I actually have most of it, but the most important stuff that took me so long was gone and it's going to take me a while to get back to where I was. So if I do send it to you, codewarrior, it probably won't be until I've caught up. Thanks for all your help.
Also, I live in White Oaks in London, Ontario. What a coincedence, eh?
Peace. -
Cool, keep me posted!
Talk to you later. -
You can do this: (i'm sure it will work)
private sub form_load()
data1.recordsource= "SELECT * FROM <tablename> ORDER BY <fieldname> ASC"
data1.refresh
end sub
see you... -
Joao, if you were here I'd give you a good handshake! Thanks so much, that worked.
Thanks to everyone else as well.
Post a reply
Database forum discussion
-
Required Every quarter data from sql automatically in below sql format
by jain.piyush831 (0 replies)
-
Is Your Data is Lost? Learn Online How TO Recover Your Data.
by dataempires (0 replies)
-
Executing a command line SELECT
by cyoung311 (0 replies)
-
Softaken Mac OLM to PST Migration Software
by albertcolin012 (0 replies)
-
MAX value of each column - HELP
by triparipr (0 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).