ASP SQL 'IS NULL'

[AndyApp]

I've a SQL statement that works fine in Access 97, SQL Server 2000 and Query Analyzer. However when I put it onto my ASP page it is just simply as if it's being ignored. I think it has to do with the last part:

WHERE (UsersSkills.SkillID IS NULL)

It doesn't seem to do anything with the IS NULL part. I've tried '= NULL' I've tried IS 'NULL', = 'NULL', = "", IS "" but nothing seems to work.

It's really frustrating when something works everywhere else but not in the place it needs to! Anyone any ideas?

"Life is like a Ferrari, it goes to fast.
But that's ok, because you can't afford it anyway" - Jim Davis (Garfield)

 

[Stoemp]

What is the field type of the field 'SkillID'?

 

[AndyApp]

It's an int(eger).

"Life is like a Ferrari, it goes to fast.
But that's ok, because you can't afford it anyway" - Jim Davis (Garfield)

 

[AndyApp]

does asp support LEFT OUTER JOIN?

"Life is like a Ferrari, it goes to fast.
But that's ok, because you can't afford it anyway" - Jim Davis (Garfield)

 

[MrGreed]

AndyApp,

ASP, is the scripting lanuage for the server and really has nothing to do with SQL. SQL is processed by your database and if there is something wrong with your query it is most likly not executing correctly or you are throwing some kind of exception.

What are you using with ASP to run your SQL statements, "ADO"? If so make sure you have a valid connection open and a recordset. Check that your SQL string being passed to the database is formed correctly. If you can post your SQL string here and the code that executes it from your ASP page. Maybe someone can then tell you what looks "a bit off" with the code.

MrGreed

http://www.pastame.com

"did you just say Minkey?, yes that's what I said."

 

[AndyApp]

The function is called from a button:

FUNCTION btnAddSkill_onclick()
DIM AllSKillsRS
DIM strSQL
DIM UserSel

UserSel = (UserSelect.getValue(UserSelect.selectedIndex)) 'This will give a number currently 87

strSQL = "SELECT SkillsList.SkillName, SkillsList.SkillDesc, UsersSkills.SkillID, UsersSkills.UserID FROM SkillsList LEFT OUTER JOIN UsersSkills ON SkillsList.ID = UsersSkills.SkillID WHERE (UsersSkills.UserID = '" & UserSel & "') OR (UsersSkills.UserID IS NULL) OR (UsersSkills.SkillID IS NULL)"

RecAllSkills.close()
RecAllSkills.setSQLText(strSQL)
RecAllSkills.open()

END FUNCTION

RecAlllSkills is a standard Visual InterDev recordset. This is the output from strSQL if I do a response.write:

SELECT SkillsList.SkillName, SkillsList.SkillDesc, UsersSkills.SkillID, UsersSkills.UserID FROM SkillsList LEFT OUTER JOIN UsersSkills ON SkillsList.ID = UsersSkills.SkillID WHERE (UsersSkills.UserID = '87') OR (UsersSkills.UserID IS NULL) OR (UsersSkills.SkillID IS NULL)

If I take either strSQL, output or raw, and place them in Access, QueryAnalyzer or SQL Server Enterprise Manager and run them they all work as they are supposed to.

If I make a similar query and just use an INNER JOIN I get the same result in ASP, SQL, ACCESS, QRYANALYZER. This result is the same as what I am getting running the LEFT OUTER JOIN in ASP. Hope that makes sense!

"Life is like a Ferrari, it goes to fast.
But that's ok, because you can't afford it anyway" - Jim Davis (Garfield)