Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: How to query a database on an nvarchar field?

    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...

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: How to query a database on an nvarchar field?

    Originally posted by Schmakt
    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!

  3. #3
    Join Date
    Nov 2003
    Posts
    7
    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...

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by Schmakt
    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'

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    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

  6. #6
    Join Date
    Nov 2003
    Posts
    7
    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?

  7. #7
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by Schmakt
    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
    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?

  8. #8
    Join Date
    Nov 2003
    Posts
    7
    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?

  9. #9
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by Schmakt
    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...

  10. #10
    Join Date
    Nov 2003
    Posts
    7
    cool; thanks a lot for the ideas...

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

  11. #11
    Join Date
    Nov 2003
    Posts
    7
    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...

Posting Permissions

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