Problem with the SQL Select Command on my VB 6 program
I'm creating this program for an old man that sorts bible verses by categories. The thing he wanted, though, was for an easy way to sort the files.
strCategory = Categories.Text
frmSearch.Hide
frmMain.adoVerses.RecordSource = "select * from tblVerses where fldCategory =" strCategory
frmMain.adoVerses.Refresh
The problem I have is with the recordsource, I gather. I don't know why it won't work (never taught how to do it in the second semester of VB). What the code is SUPPOSED to do (or in my mind) is get the category from the combo box Categories and place it in strCategory. After hiding that form, it's supposed to sort it by the category and refresh the main form. I don't know why it won't do it, though. A little help? I'd appreciate it. =)
Re: Problem with the SQL Select Command on my VB 6 program
Welcome to the forums!
I think you are looking for the ORDER statement. It allows you to sort on one or more fields. It is SQL, but the version might matter SQL Server or Access?
Re: Problem with the SQL Select Command on my VB 6 program
Re: Problem with the SQL Select Command on my VB 6 program
From MSDN:
Quote:
ORDER BY Clause
Sorts a query's resulting records on a specified field or fields in ascending or descending order.
Syntax
SELECT fieldlist
FROM table
WHERE selectcriteria
[ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][, ...]]]
A SELECT statement containing an ORDER BY clause has these parts:
Part Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases , SQL aggregate functions , selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP ), or other SELECT statement options.
table The name of the table from which records are retrieved.
selectcriteria Selection criteria. If the statement includes a WHERE clause, the Microsoft Jet database engine orders values after applying the WHERE conditions to the records.
field1, field2 The names of the fields on which to sort records.
Remarks
ORDER BY is optional. However, if you want your data displayed in sorted order, then you must use ORDER BY.
The default sort order is ascending (A to Z, 0 to 9). Both of the following examples sort employee names in last name order:
SELECT LastName, FirstName
FROM Employees
ORDER BY LastName;
SELECT LastName, FirstName
FROM Employees
ORDER BY LastName ASC;
To sort in descending order (Z to A, 9 to 0), add the DESC reserved word to the end of each field you want to sort in descending order. The following example selects salaries and sorts them in descending order:
SELECT LastName, Salary
FROM Employees
ORDER BY Salary DESC, LastName;
If you specify a field containing Memo or OLE Object data in the ORDER BY clause, an error occurs. The Microsoft Jet database engine does not sort on fields of these types.
ORDER BY is usually the last item in an SQL statement .
You can include additional fields in the ORDER BY clause. Records are sorted first by the first field listed after ORDER BY. Records that have equal values in that field are then sorted by the value in the second field listed, and so on.
1 Attachment(s)
Re: Problem with the SQL Select Command on my VB 6 program
I don't think I was clear enough in the explanation of my problem, and I'm sorry. The problem isn't exactly with the sorting, though the order by information was informative. The problem I have with the piece of code is that it doesn't do anything at all but give me an error. I apologize for not explaining in a better way. The error I get is in the picture I attached. Then it says that the refresh was unsuccessful, a problem with the ado control. =(
Re: Problem with the SQL Select Command on my VB 6 program
I think the query should be like below
..... ="Select * from <tablename> where <FileldName> = '" & <variable> & "'"
Re: Problem with the SQL Select Command on my VB 6 program
Ah, thank you. It worked perfectly. Thanks everyone for your help. =)