ORDER BY ---- GRRRR

[newmediaguy]

Hi guys

I am having trouble with getting an order by clause working.... this may be more ASP related but thought I ask you first.

It works in the query analyzer but not when called in the ASP page.

set replies = server.CreateObject("ADODB.Recordset")
replies.ActiveConnection = databaseString
replies.Source = "SELECT  * FROM QuoteReplies WHERE ReferenceNumber = '"&Session("ReferenceNumber")&"' AND Category = '"&cat("Category")&"' AND Manufacturer = '"&man("Manufacturer")&"' ORDER BY ID ASC"

ID is an identity field but that shouldnt make a difference....i wouldnt have thought.

Any thoughts appreciated

Glen
Conception | Execution

 

[nigelrivett]

replies.Source = "SELECT * FROM QuoteReplies WHERE ReferenceNumber = '"&Session("ReferenceNumber")&"' AND Category = '"&cat("Category")&"' AND Manufacturer = '"&man("Manufacturer")&"' ORDER BY ID ASC"

If it works inn query analyser then it is likely to be asp related.
What happens? Is there an error?

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBill]

I see that some of your quotes appear as the double quote " vice two single quotes ''. Is that the way you have it in the script? I believe it should be single quotes and never double quotes.

You have: I believe it should be:
"SELECT ''SELECT
'"&Session '''&Session

I could be wrong (I don't use ASP), but try changing all the double quotes to two single quotes.

-SQLBill

 

[newmediaguy]

Thanks Bill & Nigel

There is no error per se' just that the ordering is wrong in the page.

The Reason for the "" is for string concantenation eg.

sql = " select * from user where id= '" & Variable & "'"

ASP strips the quotes out when it is called so you would end up with

sql = select * from user where id= 'VariableVALUE'

god i hate working with syntax strings!!!




Glen
Conception | Execution

 

[SQLBill]

How is the ordering wrong?

ORDER BY ID ASC

Let's say you have IDs 1 5 7 4 8 10, the above ORDER BY would return:

1
4
5
7
8
10

That is by ID ASCending.

-SQLBill

 

[newmediaguy]

the ordering will come out like:

5
3
1
2
4
8
6

I can post some screen shots if you would like?

Thanks

Glen

Glen
Conception | Execution

 

[SQLBill]

No, I believe you. Is the script you provided the WHOLE script? I'm having trouble figuring out how it could be ORDERing. That order isn't numeric nor alphabetic in either ascending or descending values.

-SQLBill

 

[newmediaguy]

Hi Bill,

Thanks for getting back to me, i will post first thing in the morning the whole asp, ive checked to see if it's in a loop etc, am pulling my hair out cause it does work in query analyser and the SQL window when looking at the tables, this is the first time i have ever had it not order either DESC or ASC on an identity column..........my thoughts are that it is the ASP as the weak link. That said will restart both SQL server and IIS and flush the cache out just to be sure.

Will not cry now but wait and see what the rising sun and 'gulp' reboot the working server brings.......

Just crazy web sh... stuff.

will let you know in the morning (GMT = 0 for me)

Thanks


Glen
Conception | Execution