quote in a string : query problem under VB


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: quote in a string : query problem under VB

  1. #1
    tyris Guest

    quote in a string : query problem under VB


    hi all,
    i'm a begginer in SQL so can someone help me ?

    i have this problem :
    i have a query that searches all contacts that have a name that begins with
    a
    specified letter.

    if the user looks for all the contacts that begin with the "tr" string :

    SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'tr%' ORDER BY

    ContactLName;

    here is no problem

    the problem is when the contact has a name like o'conneil for exemple ....
    because
    it gives something like :

    if the user looks for all the contacts that begin with the "o'" string:
    SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'o'%' ORDER BY

    ContactLName;

    you see the problem ? the quote in the contact name is a problem because
    under VB in the query the quote is used for a string delimitation ...
    but the contact name is entered like " o'conneil " in the database ...
    what can i do ?


    ps : sorry for my bad english


  2. #2
    Dennis Guest

    Re: quote in a string : query problem under VB


    "tyris" <kea@mailcity.com> wrote:
    >
    >hi all,
    >i'm a begginer in SQL so can someone help me ?
    >
    >i have this problem :
    >i have a query that searches all contacts that have a name that begins with
    >a
    >specified letter.
    >
    >if the user looks for all the contacts that begin with the "tr" string :
    >
    >SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'tr%' ORDER BY
    >
    >ContactLName;
    >
    >here is no problem
    >
    >the problem is when the contact has a name like o'conneil for exemple ....
    >because
    >it gives something like :
    >
    >if the user looks for all the contacts that begin with the "o'" string:


    >SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'o'%' ORDER BY
    >
    >ContactLName;
    >
    >you see the problem ? the quote in the contact name is a problem because
    >under VB in the query the quote is used for a string delimitation ...
    >but the contact name is entered like " o'conneil " in the database ...
    >what can i do ?
    >
    >
    >ps : sorry for my bad english
    >


    You need to use two single quotes within a literal, i.e. LIKE 'o''%' or LIKE
    'o''connell%'.

    Dennis

  3. #3
    Tony Guest

    Re: quote in a string : query problem under VB


    "tyris" <kea@mailcity.com> wrote:
    >
    >hi all,
    >i'm a begginer in SQL so can someone help me ?
    >
    >i have this problem :
    >i have a query that searches all contacts that have a name that begins with
    >a
    >specified letter.
    >
    >if the user looks for all the contacts that begin with the "tr" string :
    >
    >SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'tr%' ORDER BY
    >
    >ContactLName;
    >
    >here is no problem
    >
    >the problem is when the contact has a name like o'conneil for exemple ....
    >because
    >it gives something like :
    >
    >if the user looks for all the contacts that begin with the "o'" string:


    >SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'o'%' ORDER BY
    >
    >ContactLName;
    >
    >you see the problem ? the quote in the contact name is a problem because
    >under VB in the query the quote is used for a string delimitation ...
    >but the contact name is entered like " o'conneil " in the database ...
    >what can i do ?
    >
    >
    >ps : sorry for my bad english
    >








    try instead of (o'connneil) try (o"conneil). this will work.
    sql doesn't understand single quotes so you have to use double quotes. try
    that

  4. #4
    Mohamed Guest

    Re: quote in a string : query problem under VB


    Try this

    dim mySearchString as string
    dim mySqlStatement as string

    mySearchString = "o'%"
    mySqlStatement = "SELECT ContactName FROM CONTACTS WHERE ContactLName Like
    " & mySearchString & " ORDER BY ContactLName;"



    "Tony" <tony@melbay.com> wrote:
    >
    >"tyris" <kea@mailcity.com> wrote:
    >>
    >>hi all,
    >>i'm a begginer in SQL so can someone help me ?
    >>
    >>i have this problem :
    >>i have a query that searches all contacts that have a name that begins

    with
    >>a
    >>specified letter.
    >>
    >>if the user looks for all the contacts that begin with the "tr" string

    :
    >>
    >>SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'tr%' ORDER BY
    >>
    >>ContactLName;
    >>
    >>here is no problem
    >>
    >>the problem is when the contact has a name like o'conneil for exemple ....
    >>because
    >>it gives something like :
    >>
    >>if the user looks for all the contacts that begin with the "o'" string:

    >
    >>SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'o'%' ORDER BY
    >>
    >>ContactLName;
    >>
    >>you see the problem ? the quote in the contact name is a problem because
    >>under VB in the query the quote is used for a string delimitation ...
    >>but the contact name is entered like " o'conneil " in the database ...
    >>what can i do ?
    >>
    >>
    >>ps : sorry for my bad english
    >>

    >
    >
    >
    >
    >
    >
    >
    >try instead of (o'connneil) try (o"conneil). this will work.
    >sql doesn't understand single quotes so you have to use double quotes. try
    >that



  5. #5
    Dave Guest

    Re: quote in a string : query problem under VB



    "tyris" wrote:
    >
    >hi all,
    >i'm a begginer in SQL so can someone help me ?
    >
    >i have this problem :
    >i have a query that searches all contacts that have a name that begins with
    >a
    >specified letter.
    >
    >if the user looks for all the contacts that begin with the "tr" string :
    >
    >SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'tr%' ORDER BY
    >
    >ContactLName;
    >
    >here is no problem
    >
    >the problem is when the contact has a name like o'conneil for exemple ....
    >because
    >it gives something like :
    >
    >if the user looks for all the contacts that begin with the "o'" string:


    >SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'o'%' ORDER BY
    >
    >ContactLName;
    >
    >you see the problem ? the quote in the contact name is a problem because
    >under VB in the query the quote is used for a string delimitation ...
    >but the contact name is entered like " o'conneil " in the database ...
    >what can i do ?
    >
    >
    >ps : sorry for my bad english
    >



  6. #6
    dave Guest

    Re: quote in a string : query problem under VB



    "Mohamed" wrote:
    >
    >Try this
    >
    >dim mySearchString as string
    >dim mySqlStatement as string
    >
    >mySearchString = "o'%"
    >mySqlStatement = "SELECT ContactName FROM CONTACTS WHERE ContactLName Like
    >" & mySearchString & " ORDER BY ContactLName;"
    >
    >
    >
    >"Tony" wrote:
    >>
    >>"tyris" wrote:


    Dave try this use the replace function it is really easy
    >>> SELECT ContactName FROM CONTACTS WHERE ContactLName Like replace(txt?.text),

    " ' "," ' ' ") ORDER BY ContactLName
    >>>hi all,
    >>>i'm a begginer in SQL so can someone help me ?
    >>>
    >>>i have this problem :
    >>>i have a query that searches all contacts that have a name that begins

    >with
    >>>a
    >>>specified letter.
    >>>
    >>>if the user looks for all the contacts that begin with the "tr" string

    >:
    >>>
    >>>SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'tr%' ORDER

    BY
    >>>
    >>>ContactLName;
    >>>
    >>>here is no problem
    >>>
    >>>the problem is when the contact has a name like o'conneil for exemple

    ....
    >>>because
    >>>it gives something like :
    >>>
    >>>if the user looks for all the contacts that begin with the "o'" string:

    >>
    >>>SELECT ContactName FROM CONTACTS WHERE ContactLName Like 'o'%' ORDER

    BY
    >>>
    >>>ContactLName;
    >>>
    >>>you see the problem ? the quote in the contact name is a problem because
    >>>under VB in the query the quote is used for a string delimitation ...
    >>>but the contact name is entered like " o'conneil " in the database ...
    >>>what can i do ?
    >>>
    >>>
    >>>ps : sorry for my bad english
    >>>

    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>try instead of (o'connneil) try (o"conneil). this will work.
    >>sql doesn't understand single quotes so you have to use double quotes.

    try
    >>that

    >



  7. #7
    dave Guest

    Re: quote in a string : query problem under VB


    dim sql as string
    use the replace function as follows
    sql = "select state,contact,phonenumber,fax,zipcode,cellphone, email from
    tblsubcontractor where trade like '%" & "" & Replace(cmbfind.Text, "'", "''")
    & "%'"

    this the code I always use when I have that problem this is actually a peace
    of code from my current project

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