I'm trying to write an ASP page to pull some data from my company's SQL database.

There is a field called "NameIdentifier" whose type is "nvarchar"

I want to retrieve all records where NameIdentifier <> 'DEAD'

I've exported the table into Access and
SELECT * FROM [table] WHERE NameIdentifier <> 'DEAD'
works just fine.

If I try the same query on the SQL database, I get no records returned.
Is there some way I can write this query with a WHERE clause based on this nvarchar field?

Thanks a lot...

The [YourTableNamne] syntax is proprietary to Access... Remove the [].

Try: SELECT * FROM table WHERE NameIdentifier <> 'DEAD'

That syntax will work on every RDBMS on Earth... even Access!

oops, sorry... I should have been more specific... I just had table in [] cuz it's not actually the table name.

The exact query is this:

SQL = "SELECT Count(WorkstationID) As CorporateCount FROM POSSetup WHERE MessageTable1 = 'QCorporate' AND WorkstationID NOT IN ('2000','2001') AND NameIdentifier <> 'DEAD'"


Also tried replacing 'DEAD' with this:
Convert(nvarchar(128),'DEAD', 112)
but that doesn't change anything.

the fields MessageTable1 and WorkstationID are also both nvarchar fields, so I'm wondering if maybe it's something I'm doing wrong with the <> part? Totally baffled...

Have you considered that perhaps your query is okay and actually no record satisfies your conditions?

What do you get if you try:

SELECT Count(WorkstationID) As CorporateCount
FROM POSSetup
WHERE NameIdentifier <> 'DEAD'

yeah, I thought about it...

so I tried building a test page with this query instead:

SELECT * FROM POSSetup WHERE NameIdentifier <> 'DEAD'

I then run through the recordset and print
rs("WorkstationID") and rs("NameIdentifier")

but nothing prints

If I remove the WHERE clause so that I have only "SELECT * FROM POSSetup", it prints all 10 values for WorkstationID. 8 of the values are NULL for NameIdentifier, and 2 of the values have DEAD in the NameIdentifier field

oh... the other weird thing is that, if I change my "<>" to "=" it will return the two records with "DEAD" in the NameIdentifier field...

so I must be doing something wrong with the not-equal-to stuff... It's got to be something dumb like <> should be != or something, right?

Have you tried running your query from the MySQL client ot make sure it's a MySQL problem rather than the tool you use to access it? It's probably just related to the way you quote the string value... Have you tried with "DEAD" or with backticks?

I don't actually have MySQL client, so I'm out of luck there...

backticks? I've only tried ' and "


also, if I use

"LIKE 'DEAD'"
the query works fine

if I use "NOT LIKE 'DEAD'"
the query doesn't work...

it just seems to not like my exclusions... is it somehow possible that the MYSQL server isn't allowing me to run an exclusive query?

Nope... Probably something else between your sapp and the server... Got to leave the office but I'll have a look at that at home tonite...

cool; thanks a lot for the ideas...

If I figure it out, I'll let you know

This is ridiculous...

"SELECT * FROM POSSetup WHERE (NameIdentifier <> 'DEAD')"

works

I've never put parentheses around my WHERE clause before... but now it works. really really weird...