dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] SQL logic error in text with apostrophe

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2011
    Posts
    139

    Resolved [RESOLVED] SQL logic error in text with apostrophe

    Hi guys,
    I have a similar problem as this thread http://www.vbforums.com/showthread.p...ter-in-textbox but a little bit different...
    When I have a text with apostrophe (') (eg: Text1.text = "na'eblis"), I get an "SQL logic error near "eblis": syntax error"
    The SQL can't deal with apostrophe in the textbox...
    Is there a way to check if there is an apostrophe in the text string and change it?
    Something like this:
    Normal code:
    Code:
    cmd.CommandText = "INSERT INTO Info (Name) VALUES ('" & Name.Text & "')"
    If there is an apostrophe in the text string:
    Code:
    Some code to split the text1.text string in to 3 (before apostrophe(str1), the apostrophe(str2) and after(str3))
    Then we combine the 3 and add the proper syntax to the apostrophe.
    But I can't think of the proper syntax for it...
    Can someone help?
    Thanks

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,954

    Re: SQL logic error in text with apostrophe

    The direct answer to your question is yes, yes there is .... the real answer though is: don't do it, use parameters instead. This is why.
    Code:
    cmd.CommandText = "INSERT INTO Info (Name) VALUES (@NewName)"
    cmd.Parameters.AddWithValue("@NewName", Name.Text)
    Now you don't care about apostrophes or other funky characters that break the SQL.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,765

    Re: SQL logic error in text with apostrophe

    Even better, you also don't have to worry about SQL injection exploits.
    My usual boring signature: Nothing

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,359

    Re: SQL logic error in text with apostrophe

    For further explanation of various reasons why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2011
    Posts
    139

    Re: SQL logic error in text with apostrophe

    Thanks guys
    I read all the links, and I see the error in my ways...
    I will change my code and write back (on Tue - I hope)

    Thanks for the help

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    May 2011
    Posts
    139

    Re: SQL logic error in text with apostrophe

    Quote Originally Posted by techgnome View Post
    The direct answer to your question is yes, yes there is .... the real answer though is: don't do it, use parameters instead. This is why.
    Code:
    cmd.CommandText = "INSERT INTO Info (Name) VALUES (@NewName)"
    cmd.Parameters.AddWithValue("@NewName", Name.Text)
    Now you don't care about apostrophes or other funky characters that break the SQL.

    -tg
    This worked perfectly
    Thanks for the help

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width