sql syntax problem


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: sql syntax problem

  1. #1
    tedk Guest

    sql syntax problem


    I have run out of ways to write this sql statement and I still can't get it
    to work. I am entering text in a text box and trying to find records that
    match the search text. My sql statement looks like this:
    "select * from streets where streetname like '" & sText & "*" & " '"
    If "M" is the search text, I want to see all records that start w/ "M". However,
    it is showing a recordcount of 0. I assume this is just a syntax issue, can
    anyone please help?

    thanks, Ted

  2. #2
    Gia Guest

    Re: sql syntax problem


    "tedk" <tkirkendal@aol.com> wrote:
    >
    >I have run out of ways to write this sql statement and I still can't get

    it
    >to work. I am entering text in a text box and trying to find records that
    >match the search text. My sql statement looks like this:
    >"select * from streets where streetname like '" & sText & "*" & " '"
    >If "M" is the search text, I want to see all records that start w/ "M".

    However,
    >it is showing a recordcount of 0. I assume this is just a syntax issue,

    can
    >anyone please help?
    >
    >thanks, Ted


    hello,
    try this,
    "select * from streets where streetname like '" & sText & "'*'" & " '"

    Gia




  3. #3
    G. Allen Guest

    Re: sql syntax problem


    The problem here is the space in the last set of quotation marks. You are
    actually searching for all strings such as "Mark " and "Mary " but not "Mark"
    and "Mary" because the later two don't contain that stray space.

    You need to use:
    "select * from streets where streetname like '" & sText & "*" & "'"

    "tedk" <tkirkendal@aol.com> wrote:
    >
    >I have run out of ways to write this sql statement and I still can't get

    it
    >to work. I am entering text in a text box and trying to find records that
    >match the search text. My sql statement looks like this:
    >"select * from streets where streetname like '" & sText & "*" & " '"
    >If "M" is the search text, I want to see all records that start w/ "M".

    However,
    >it is showing a recordcount of 0. I assume this is just a syntax issue,

    can
    >anyone please help?
    >
    >thanks, Ted



  4. #4
    tedk Guest

    Re: sql syntax problem


    "Gia" <grjorge_me@yahoo.com> wrote:
    >
    >"tedk" <tkirkendal@aol.com> wrote:
    >>
    >>I have run out of ways to write this sql statement and I still can't get

    >it
    >>to work. I am entering text in a text box and trying to find records that
    >>match the search text. My sql statement looks like this:
    >>"select * from streets where streetname like '" & sText & "*" & " '"
    >>If "M" is the search text, I want to see all records that start w/ "M".

    >However,
    >>it is showing a recordcount of 0. I assume this is just a syntax issue,

    >can
    >>anyone please help?
    >>
    >>thanks, Ted

    >
    >hello,
    > try this,
    >"select * from streets where streetname like '" & sText & "'*'" & " '"
    >
    >Gia
    >
    >
    >


    I tried that and got "Data type mismatch in criteria expression"


  5. #5
    Samuel Jackson Guest

    Re: sql syntax problem


    TedK,

    In case it still doesn't work... perhaps you can try this:

    "SELECT * FROM streets WHERE streetname LIKE '" & sText & "%'"

    Samuel Jackson



    "G. Allen" <gallenspam@worldnet.att.net> wrote:
    >
    >The problem here is the space in the last set of quotation marks. You are
    >actually searching for all strings such as "Mark " and "Mary " but not "Mark"
    >and "Mary" because the later two don't contain that stray space.
    >
    >You need to use:
    >"select * from streets where streetname like '" & sText & "*" & "'"
    >
    >"tedk" <tkirkendal@aol.com> wrote:
    >>
    >>I have run out of ways to write this sql statement and I still can't get

    >it
    >>to work. I am entering text in a text box and trying to find records that
    >>match the search text. My sql statement looks like this:
    >>"select * from streets where streetname like '" & sText & "*" & " '"
    >>If "M" is the search text, I want to see all records that start w/ "M".

    >However,
    >>it is showing a recordcount of 0. I assume this is just a syntax issue,

    >can
    >>anyone please help?
    >>
    >>thanks, Ted

    >



  6. #6
    Hamad Guest

    Re: sql syntax problem


    "tedk" <tkirkendal@aol.com> wrote:
    >
    >I have run out of ways to write this sql statement and I still can't get

    it
    >to work. I am entering text in a text box and trying to find records that
    >match the search text. My sql statement looks like this:
    >"select * from streets where streetname like '" & sText & "*" & " '"
    >If "M" is the search text, I want to see all records that start w/ "M".

    However,
    >it is showing a recordcount of 0. I assume this is just a syntax issue,

    can
    >anyone please help?
    >
    >thanks, Ted



    try this


    "select * from streets where streetname like '" & Trim(sText) & "*'"



  7. #7
    Adelle Hartley Guest

    Re: sql syntax problem

    "tedk" <tkirkendal@aol.com> wrote in message
    news:3b09544f$1@news.devx.com...
    >
    > I have run out of ways to write this sql statement and I still can't get

    it
    > to work. I am entering text in a text box and trying to find records that
    > match the search text. My sql statement looks like this:
    > "select * from streets where streetname like '" & sText & "*" & " '"
    > If "M" is the search text, I want to see all records that start w/ "M".

    However,
    > it is showing a recordcount of 0. I assume this is just a syntax issue,

    can
    > anyone please help?


    Are you using Access? If so, then your SQL looks fine.
    If you are using another database, then you may need a different wildcard.
    Eg. SQL Server uses %

    Adelle.
    _____________________________________________________________
    Impress your friends. Become an SQL guru.
    http://www.sql-guru.com



  8. #8
    Antonello Guest

    Re: sql syntax problem


    "tedk" <tkirkendal@aol.com> wrote:

    >"select * from streets where streetname like '" & sText & "*" & " '"


    You must use "%" instead of "*" for use in SQL server:

    "select * from streets where streetname like '" & sText & "%" & " '"

    If you use Access, via ADO, you must use "%" as well, because ADO will translate
    the "%" into the Access wildcard syntax "*"

    Ciao

  9. #9
    Richard Guest

    Re: sql syntax problem


    "Adelle Hartley" <adelle@sql-guru.com> wrote:
    >"tedk" <tkirkendal@aol.com> wrote in message
    >news:3b09544f$1@news.devx.com...
    >>
    >> I have run out of ways to write this sql statement and I still can't get

    >it
    >> to work. I am entering text in a text box and trying to find records that
    >> match the search text. My sql statement looks like this:
    >> "select * from streets where streetname like '" & sText & "*" & " '"
    >> If "M" is the search text, I want to see all records that start w/ "M".

    >However,
    >> it is showing a recordcount of 0. I assume this is just a syntax issue,

    >can
    >> anyone please help?

    >
    >Are you using Access? If so, then your SQL looks fine.
    >If you are using another database, then you may need a different wildcard.
    >Eg. SQL Server uses %
    >
    >Adelle.
    >_____________________________________________________________
    >Impress your friends. Become an SQL guru.
    >http://www.sql-guru.com
    >
    >

    The SQL statement generated will be:
    select * from streets where streetname like 'M* '
    The space in the literal could be your problem.


  10. #10
    huibert Guest

    Re: sql syntax problem


    "Richard" <richard.Turnbull@au.unisys.com> wrote:
    >
    >"Adelle Hartley" <adelle@sql-guru.com> wrote:
    >>"tedk" <tkirkendal@aol.com> wrote in message
    >>news:3b09544f$1@news.devx.com...
    >>>
    >>> I have run out of ways to write this sql statement and I still can't

    get
    >>it
    >>> to work. I am entering text in a text box and trying to find records

    that
    >>> match the search text. My sql statement looks like this:
    >>> "select * from streets where streetname like '" & sText & "*" & " '"
    >>> If "M" is the search text, I want to see all records that start w/ "M".

    >>However,
    >>> it is showing a recordcount of 0. I assume this is just a syntax issue,

    >>can
    >>> anyone please help?

    >>
    >>Are you using Access? If so, then your SQL looks fine.
    >>If you are using another database, then you may need a different wildcard.
    >>Eg. SQL Server uses %
    >>
    >>Adelle.
    >>_____________________________________________________________
    >>Impress your friends. Become an SQL guru.
    >>http://www.sql-guru.com
    >>
    >>

    >The SQL statement generated will be:
    >select * from streets where streetname like 'M* '
    >The space in the literal could be your problem.
    >


    Like this you are looking for records containing EXACTLY three characters:
    M, * and a space.

    The correct syntax is 'M%'. This will give you all strings starting with
    an M.

    HTH
    huibert

  11. #11
    Pnsm Guest

    Re: sql syntax problem


    This is just an ideia but try replacing the "*" by a "%".

    You will end up with a string sort of like this
    "SELECT * FROM streets WHERE streetname LIKE '" & sText & "%'"

    "Richard" <richard.Turnbull@au.unisys.com> wrote:
    >
    >"Adelle Hartley" <adelle@sql-guru.com> wrote:
    >>"tedk" <tkirkendal@aol.com> wrote in message
    >>news:3b09544f$1@news.devx.com...
    >>>
    >>> I have run out of ways to write this sql statement and I still can't

    get
    >>it
    >>> to work. I am entering text in a text box and trying to find records

    that
    >>> match the search text. My sql statement looks like this:
    >>> "select * from streets where streetname like '" & sText & "*" & " '"
    >>> If "M" is the search text, I want to see all records that start w/ "M".

    >>However,
    >>> it is showing a recordcount of 0. I assume this is just a syntax issue,

    >>can
    >>> anyone please help?

    >>
    >>Are you using Access? If so, then your SQL looks fine.
    >>If you are using another database, then you may need a different wildcard.
    >>Eg. SQL Server uses %
    >>
    >>Adelle.
    >>_____________________________________________________________
    >>Impress your friends. Become an SQL guru.
    >>http://www.sql-guru.com
    >>
    >>

    >The SQL statement generated will be:
    >select * from streets where streetname like 'M* '
    >The space in the literal could be your problem.
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center