Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    34

    Unanswered: someone school me on ...

    sql strings

    x = 1 ' integer
    y = "1" ' string


    which one(s) are correct?

    sql = "SELECT * "
    sql = sql & "FROM SomeTable "
    sql = sql & "WHERE SomeField = ' " & x & " '

    or

    sql = "SELECT * "
    sql = sql & "FROM SomeTable "
    sql = sql & "WHERE SomeField = " & x & "

    or

    sql = "SELECT * "
    sql = sql & "FROM SomeTable "
    sql = sql & "WHERE SomeField = ' " & y & " '

    or

    sql = "SELECT * "
    sql = sql & "FROM SomeTable "
    sql = sql & "WHERE SomeField = " & y & "



    i am so confused


    i am working with ASP

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know anything about ASP, but - generally speaking - numbers should be left alone (with no quotes) while strings should be enclosed in quotes.

    In Oracle, you'd do something like this:

    SELECT * FROM some_table
    WHERE number_column = 1
    AND char_column = '1'

    Sometimes it might work OK if you leave it this way:
    ... AND char_column = 1
    because DB engine would perform implicit conversion from a number to a character. But this is not recommended as you might end with a VALUE ERROR (for example, you have numbers stored into the character column for years, and then, suddenly, someone enters 'A' into that column which is perfectly OK regarding the database, but your code will fail if there are no enclosing single quotes on the variable).

    Therefore, your 2nd and 3rd answer seem to be correct from my point of view (which doesn't have to be correct).

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Thumbs up Strings vs ints

    Remember, in classic ASP, all variables are considered "variant", so when it comes to SQL, it does not matter if X = 1 or Y = "1"

    However, this *is* important in the ASP code (but not SQL code)
    Code:
    In ASP, 
    If X = 1 then...  results in TRUE
    If X = "1" then...  results in FALSE
    and
    If Y = 1 then...  results in FALSE
    If Y = "1" then...  results in TRUE
    However, When you are sending a select statement to SQL SERVER, it *all* gets sent as a string.

    Asssume the following table, PERSON, in Sql Server.
    AGE is INT
    NAME is char(50)

    In ASP, you can do:
    Code:
    strSQL = "Select * from PERSON, Where AGE=" & X
    or
    strSQL = "Select * from PERSON, Where AGE=" & Y
    Notice that there are *no* quotes around X or Y. Quotes imply that it is a string. AGE is an INT, so it expects an int. In both cases, SQL will intepret this as
    Select * from PERSON, Where AGE = 1


    This is also allowed:
    Code:
    strSQL = "Select * from PERSON, Where NAME=" & "'" & X & "'"
    or
    strSQL = "Select * from PERSON, Where NAME=" & "'" & Y & "'"
    Notice that I *do* have single quotes around X and Y. Becuase NAME is a char() and expects a string. In both cases, SQL will intepret this as
    Select * from PERSON, Where NAME= '1'

    Please also note that in SQL Server, strings must be in single quotes.


    Last Example:
    Code:
    Both of these will give you a SQL Error:
    strSQL = "Select * from PERSON, Where AGE=" & "'" & X & "'"
    strSQL = "Select * from PERSON, Where NAME=" & X
    In example 1, AGE is expecting an INT, so by sending it the string '1', it causes an error.

    In example 2, NAME is expecting a CHAR, so by send it the numeric 1, it causes an error.


    ~Le

Posting Permissions

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