Sql In

Discussion in 'C#' started by cancer10, Oct 29, 2006.

0
  1. #1
    Does anyone know the exact query for IN in the following senario...

    I want to search for 2 columns for 1 mobile number (mobile, alternate mobile)

    I am using the following query


    strSQL = "select * from tblcontest where bar_mob_no IN('" & sSQL &"') or bar_alt_mob in('" & sSQL &"')"
    Code (markup):
    But its hshowing the following error
    
Microsoft JET Database Engine- Error '80040e14'

Syntax error (missing operator) in query expression 'bar_mob_no IN(''44444444'') or bar_alt_mob in(''44444444'')'.
    Code (markup):
     
    cancer10, Oct 29, 2006 IP
  2. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #2
    maybe this could work
    strSQL = "select * from tblcontest where bar_mob_no='" & sSQL &"' or bar_alt_mob='" & sSQL &"' "

    if the fields are integers, you'd beter use it this way
    strSQL = "select * from tblcontest where bar_mob_no=" & sSQL &" or bar_alt_mob=" & sSQL &" "

    also you may wanna try using the LIKE in the SQL statement
     
    ludwig, Oct 29, 2006 IP
  3. Garve

    Garve Peon

    Messages:
    62
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    A mobile phone number should be stored as text to maintain any zeros at the start and any spacing.

    Getting this to work requires that the numbers are always stored the same way - eg with spaces or without spaces - otherwise it'll be difficult.

    Assuming sSQL is the mobile number, I think this is the code you need.

    strSQL = "select * from tblcontest where bar_mob_no = '" & sSQL &"' or bar_alt_mob = '" & sSQL &"' "
     
    Garve, Oct 30, 2006 IP
  4. honeydesign

    honeydesign Peon

    Messages:
    62
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    why your sSQL is '44444444'
    the sSQL should be 44444444
    or chang IN(' to IN( and ') to )
    by cut the ' out from your statement

    both all are another way ok.
     
    honeydesign, Nov 1, 2006 IP
  5. Garve

    Garve Peon

    Messages:
    62
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Honeydesign - a telephone number is not numerical data, because it may have a zero at the front and spaces in it. It is a text string.

    eg
    05555 555555
     
    Garve, Nov 2, 2006 IP
  6. honeydesign

    honeydesign Peon

    Messages:
    62
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Grave you right ,the Tel No. shoule be text not number as you point.

    but in the error is say
    *******************
    Microsoft JET Database Engine- Error '80040e14'

    Syntax error (missing operator) in query expression 'bar_mob_no IN(''44444444'') or bar_alt_mob in(''44444444'')'.
    ***********************
    you will see the 2 ' [chr(39)] in the SQL statment , it not 1 " [chr(34)]
    in the front and the end of 44444444

    so the sSQL variable is content '44444444' (len() is 10) not 44444444 (len() is 8)

    in the best way should cut the chr(39) out from the sSQL variable.

    The last, in the ASP code should have A SPACE in the end of "&" too.

    Nice to see u
     
    honeydesign, Nov 2, 2006 IP