Sorting

[Zaphod64831]

I'm a newbie to DB programming, and I need some help with sorting.

I need to know how to alphabetize a database by the Item_Name field. I use several different dbases on the same control for different categories but they all have the Item_Name field and I need one function to work with all of them.

Can anyone help?

[Mongo]

Hey, SW_MO, I hope NE_KS can help. ;)

If you're connecting with an SQL string, simply add
" ORDER BY Item_Name" to the end of your statement.

Otherwise, tell me which control you're using & I can
be more specific.

(Yipes, I've lost my "bold" touch... Igu. Hence, the edit.)

[Edited by Mongo on 05-12-2000 at 11:44 PM]

[Zaphod64831]

I'm using a the default database control.

[Mongo]

Hmmm, sounds like VB6. Does a default name like "Adodc1" ring a bell? Perhaps one of these two routes looks similar to how you're doing it.

Option 1: Adodc1.RecordSource = "SELECT {yada, yada} ORDER BY Item_Name"

Option2: Open Command Properties sheet. In the General tab, select the SQL Statement radio button, and click on the SQL Builder command button, which opens the VDT Query Editor. Check View | Show Panes to make sure the Design, Grid, SQL, and Results panes are all showing. Next, open VB's Data View window (if it is not already open) using the Data View Window toolbar button or the View | Data View window menu option. Open the Data Environment Connections branch, then open your project and tables branches. For each table, drag it to the design area of the Query Editor. In the Grid pane, select Ascending in the Sort Type column combo boxes. If I've got my instructions right, you should see something like this in the SQL pane:

SELECT {yada, yada} ORDER BY Item_Name

Choose the Run option from the Query menu to see the results of your query in the Results pane of the Query Designer. If the results look correct, hurray for me, else I'm out to lunch... I apologize if this isn't entirely correct, I'm trying this via my rusty noodle, sans VB6.

[Zaphod64831]

Nope, I'm using VB5 at the moment. It's just the default data control that I start up with. I tried your method, replacing Adodc1 with Data1, but it didn't work. I apologize if I'm not being clear but I don't know what else to tell you.

[Mongo]

My red-herring, sorry. Adodc is new with VB6,
doesn't exists in VB5... I jumped to a VB6 answer
with "new" and "database control."

Are you using ADO or RDO?
What was/is the original name of the control you're using?

(I'll keep trying until you say "Uncle!" *s*)

[Zaphod64831]

I'm sorry but I don't know what I'm using (ADO or RDO), as I said I'm very new to database programming. Data1 is the original control name and, if it helps, I'm using Microsoft Database (*.mdb) for my database type.

[pardede]

OK, here's the deal.

Sorting data you do on a recordset object. Every datacontrol has a recordset object. What you need to do is something like this:

Data1.Recordset.Sort = "Item_Name"
Set Data1.Recordset = Data1.Recordset.OpenRecordset

This might do the trick

[Zaphod64831]

I tried that, all I get is an error:


Runtime Error '91'

Object variable or With block variable not set

[pardede]

well, i hope you did not copy my code literally cause i used "Data1" as an example for the name of the data control. Replace "Data1" on my code with the name of the data control you are actually using and i think it will work.

[Zaphod64831]

I did copy it, and Data1 was the name of my control. I don't know why this stuff always happens to me, every time I try to do someting it just doesn't turn out right.

Is there some way I can use the SQL editor in visdata to create an SQL query and call that from VB? I think I can create the query, I just don't know how to use it.

[Mongo]

I hate to see you anguish over this any longer. Look over the article at:

http://support.microsoft.com/support/kb/articles/Q147/8/82.asp

It may more clearly explain "how-to."