CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    4

    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. =)

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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?
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jan 2006
    Posts
    4

    Re: Problem with the SQL Select Command on my VB 6 program

    Access.

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Problem with the SQL Select Command on my VB 6 program

    From MSDN:

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jan 2006
    Posts
    4

    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. =(
    Attached Images Attached Images  

  6. #6
    Join Date
    Oct 2005
    Location
    Nepal
    Posts
    50

    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> & "'"

  7. #7
    Join Date
    Jan 2006
    Posts
    4

    Re: Problem with the SQL Select Command on my VB 6 program

    Ah, thank you. It worked perfectly. Thanks everyone for your help. =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured