Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: Need another pair of eyes sql 'Order by'

    my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
    If I run this in SQL Manager it works fine:
    Code:
    SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
    if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.

    the error I get is:
    Microsoft OLE DB Provider for SQL Server error '80040e21'
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    /Newindex/trip_types.asp, line 53
    line 53 is the 'desc = oRS...' bizarrely
    Code:
    oRS.Open strSQL, oConn, 2, 3
    	oRS.moveFirst
          Do while not oRS.eof
    		 country = oRS("country")
    	53--->    desc = oRS("description")
    		 url_link = oRS("url_link")
    		 startDate = oRS("start_date")
    		 endDate = oRS("end_date")
    		 trip = oRS("trip_type")
    		 difficulty = oRS("difficulty")
    not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.

    any comments gratefully received thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the SQL Statement hard-coded in the asp page? Or can people change the value of 'Trekking'?

    Outside of that, you could try to reference the fields by number (starting at 0, naturally), and see if you are running into the fact that "Description" is a keyword in T-SQL.

  3. #3
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    people can change TREKKING and that bit works, but I have simplified the sql string in this thread so people concentrate on what's going wrong.
    the examples above work fine on the web page and SQL manager but the web version fails if I add 'ORDER BY start_date'
    The fact that it all works fine makes me think there's no problem with spelling.
    What's T-SQL and is that relevent to me using MS SQL Server and asp -vvbscript?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    T-SQL = Transact SQL. Microsoft/Sybase's version of the Structured Query Language standard. All queries passed to SQL Server are in T-SQL by definition.

    I was wondering if an extra quote may have strayed into the word 'Trekking', which would more likely throw a syntax problem, though.

    Have you tried using a different Cursor Type in the line:
    Code:
    oRS.Open strSQL, oConn, 2, 3
    Maybe the dynamic cursor is the one having trouble with a sorted recordset. Not being a VB programmer of any note, this is more of an educated guess.

  5. #5
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    test your spelling theroy
    try - order by 4

    also could be that ASP is terminating the string prior to order by.
    some sort of ''' problem
    Test this by removing the where clause

    print / msgbox whatever ASP uses to select the SQL prior to running.

    Just some thoughts

  6. #6
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    thanks I'll have a go and get back 2u

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    whoo hoo!

    I removed the cursors and it works. thanks MCrowley

    innitially they were 2,3 (whatever that means)
    then I changed them to 0,1 and it worked then I removed them thinking that if I don't know what I'm doing I should go with the defaults... or change jobs.

    Thanks for your time folks

Posting Permissions

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