VBScript SQL Limitations?

[jkrohn]

I hate this language more and more everyday

That being said, I have two queries that work just fine in MS SQL query analyzer. Then I plug the query into VBScript, it returns nothing. No error is thrown, I am just greeted with an emplty record set.

If I have it print the SQL right before it executes it and plug that into MS Query Analyzer manually I get the desired results. Not in the program though.

SQL statement.

Select Count(*) From PDA_SMS_Users where Not exists (Select * from Users Where PDA_SMS_Users.username = Users.username AND ((([Access] - ([Access] % 4)) % (4*2))= 4 OR (([Access] - ([Access] % 8)) % (8*2)) = 8))

Relevent code:

sSQL2 = "Select Count(*) From PDA_SMS_Users where Not exists (Select * from Users Where PDA_SMS_Users.username = Users.username AND ((([Access] - ([Access] % 4)) % (4*2))= 4 OR (([Access] - ([Access] % 8)) % (8*2)) = 8))"
Set oRS = oConn.execute(sSQL2)

iRecordCount = oRS(0)

iRecordCount comes up at 0 in the VBscript program and the correct number in MS Query analyzer

Jkrohn

[vass0922]

ah yes I hate that problem as well

Check into the ado connection properties, I can't remember which one it is

adoRecordset.CursorLocation = adUseServer
adoRecordset.CursorType = adOpenStatic

Check the value of those two constants on this site
http://asp.programmershelp.co.uk/adoconstants.php

[Whir]

Nevermind, listen to Vass.

I never got cursorlocation right. I hate SQL.

[jkrohn]

God I am getting pissed.

Unfortunately that didn't work Vass.

I converted it all to oRS.open and such, but it still will not return correctly.

Even a simple statement like Select Count(*) From table results in zero :-/

What angers me more is I have the exact same page (and code copied this page from that one) only with a modified SQL statement (one less condition) and it all works perfectly.

I can't even get that page to load ANY data no matter the query.

Jkrohn

[Whir]

Could it be a simple field name misspelling?

[jkrohn]

Bah, gimme a minute on this one

Jkrohn

[jkrohn]

Ok, I wish it was a field misspelling :-/ Remember, these queries work EXACTLY as they are suppossed to in MS Query analyzer.

Have a look for yourself.
One that works

"Select PDA_SMS_Users.Username, PDA_SMS_Users.pnumber, PDA_SMS_Users.provider, PDA_SMS_Users.LME" & sMetal & "C, PDA_SMS_Users.LME" & sMetal & "WT From PDA_SMS_Users,Users WHERE PDA_SMS_Users.username = Users.username  AND PDA_SMS_Users.LME" & sMetal & " LIKE " & "'%" & sTime & "%' AND SubscriptionEndDate > GETDATE() AND (([Access] - ([Access] % 4)) % (4*2)) = 4"

One that doesn't:

"Select PDA_SMS_Users.Username, PDA_SMS_Users.pnumber, PDA_SMS_Users.provider, PDA_SMS_Users.LME" & sMetal & "C, PDA_SMS_Users.LME" & sMetal & "WT From PDA_SMS_Users,Users WHERE PDA_SMS_Users.username = Users.username  AND PDA_SMS_Users.LME" & sMetal & " LIKE " & "'%" & sTime & "%' AND SubscriptionEndDate > GETDATE() AND Users.Access = 2"

And for the second query.
One that does work:

"Select Count(*) From PDA_SMS_Users,Users WHERE PDA_SMS_Users.username = Users.username  AND SubscriptionEndDate > GETDATE() AND PDA_SMS_Users.LME" & sMetal & " LIKE " & "'%" & sTime & "%' AND (([Access] - ([Access] % 4)) % (4*2)) = 4 "

"Select Count(*) From PDA_SMS_Users,Users where PDA_SMS_Users.Username = Users.username  AND SubscriptionEndDate > GETDATE() AND PDA_SMS_Users.LME" & sMetal & " LIKE " & "'%" & sTime & "%' AND  Users.Access = 2"

The only differences between the two come at the end. If I plug the two that don't work into the page that does, they still don't work. Reverting to the originals causes the page to behave as it should though. Remember that these do EXACTLY what I want in MS Query Analyzer.

Thank god it is lunch, I am going to kill someone. I need a break.
Jkrohn

[jkrohn]

Well it is now working. NO idea what the hell happened.

Thats three hours down the drain.
/me sighs

Jkrohn