Professional Web Applications Themes

What's wrong with my SQL statement in ASP?! - ASP Database

I'm working on an ASP Web application, and am having syntax issues in a WHERE statement I'm trying to write that uses the CInt Function on a field. Basically, I want to select records using criteria of Race, Gender and Crime Code. But the Crime Code field in the table is text, and I cannot change it. I want to use a range of crime codes, so need to convert it to an integer on-the-fly. Here's what I have in my code so far: varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* " varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT ...

  1. #1

    Default What's wrong with my SQL statement in ASP?!

    I'm working on an ASP Web application, and am having syntax issues in
    a WHERE statement I'm trying to write that uses the CInt Function on a
    field.

    Basically, I want to select records using criteria of Race, Gender and
    Crime Code. But the Crime Code field in the table is text, and I
    cannot change it. I want to use a range of crime codes, so need to
    convert it to an integer on-the-fly. Here's what I have in my code so
    far:

    varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

    varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
    ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
    PrisonRelease.PID = Defendant.PID_Code "

    varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
    varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

    varSQL = varSQL & "AND
    (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
    Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
    (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
    Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
    "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
    Between 1800 And 1899) "

    When I try to execute this code on my Web page, I get an error. But it
    works fine in Access, with some minor syntax changes. What am I
    missing?!

    Thanks,
    Rachel Weeden
    Rachel Guest

  2. #2

    Default Re: What's wrong with my SQL statement in ASP?!


    "Rachel Weeden" <com> wrote in message
    news:google.com...
     

    WHAT is the error?

    And what is the result of:

    RESPONSE.WRITE VARSQL
    RESPONSE.END

    Ray at work


    Ray Guest

  3. #3

    Default Re: What's wrong with my SQL statement in ASP?!

    On 23 Aug 2004 06:29:17 -0700, com (Rachel
    Weeden) wrote:
     

    I can't see your display from here, so you'll need to post the actual
    error you get so we can diagnose it. You'll also want to post the
    output of:

    Response.Write varSQL

    In fact, if you look at that output you'll likely see where your error
    is.

    Jeff
    Jeff Guest

  4. #4

    Default Re: What's wrong with my SQL statement in ASP?!



    The error I get in IE is:
    Error Type:
    Microsoft VBScript compilation (0x800A0401)
    Expected end of statement

    The line/column it gives is right at the "Not Like" part of the SQL
    statement.

    The response.write I have to view the code does not work, as it is a
    "Page Not Found" error, so no part of the page is working.

    Any ideas?

    Thanks!
    Rachel

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rachel Guest

  5. #5

    Default Re: What's wrong with my SQL statement in ASP?!

    That's a VB Script error, not an ADO error. So, it's probably just in your
    concatenation of a string - a string that just happens to be a SQL query.
    But that part is irrelevant.

    Did you notice that the error gives you the line number as well? Did you
    look at that line in your ASP code? I'm going to guess that this is where
    the error lies:

    (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And

    You have " characters in the middle of a string. You cannot do that. And
    for this particular application, you should be using ' instead of " anyway,
    since you're delimiting text in SQL query.

    (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like '[0-9][0-9][0-9]' And
    instead

    But, for future reference, if you need to have " in a string, such as:
    Joe said, "Hi!"
    to do that in VBScript, you'd do:

    Response.Write "Joe said, ""Hi!"""

    Doubling " in the middle of a string will give you a " character.

    Ray at work




    "Rachel Weeden" <gov> wrote in message
    news:%23%phx.gbl... 


    Ray Guest

Similar Threads

  1. Help! What's wrong with my QUERY statement??
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: August 13th, 09:05 AM
  2. What is wrong with this insert statement?
    By chill84 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 13th, 02:53 PM
  3. mssql: update statement wrong or truncated
    By Jochen Daum in forum PHP Development
    Replies: 1
    Last Post: November 16th, 11:01 PM
  4. Replies: 4
    Last Post: September 16th, 04:54 PM
  5. field seperator in select / wrong statement ?
    By Kai Baumgart in forum IBM DB2
    Replies: 1
    Last Post: August 26th, 02:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139