Re: handling apostrophe

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 13:29:57 +0200

Just use Replace(varname, "'", "''") in your client app. I.e., replace each single quote with two
single quotes before submitting it to SQL server. Or better yet, use command and parameter objects
in ADO.NET which will do this for you. And, even better yet, use stored procedures with command and
parameter objects. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"darkstar_E2" <darwin_e@comtechsolutions.com> wrote in message
news:%23SW24XhpEHA.1576@TK2MSFTNGP12.phx.gbl...
> yes i believe so. but the real problem behind this scenario is not that i am
> coding in vb.net and i am using a routine that will get data from the
> database by feeding it with a sql statement as parameter.
> Let say: GetDataFromStatement("Select * from tblARInvoice")
> The problem here is if i will feed the parameter with a string variable that
> will determine tha filter.
> Let say I have a variable in Vb ( _CustomerID).. and i will use this to
> filter my data.
> i will use this :
>     GetDataFromStatement("Select * from tblARInvoice where strCustomer = ' "
> & _CustomerID & "'"  )
> Now, what if the _CustomerID variable contains boy's game..
> How can i feed it in the routine if i am only a user of that routine..
> I would really appreciate an answer...
>
>
>
>
>
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
> message news:OOJPH8gpEHA.3300@TK2MSFTNGP12.phx.gbl...
> > > But i do not want to use this:
> > >     set @str = 'boy''s game'
> >
> > Why don't you want to use the proper way of doing it? Prefixing a single
> quote with another single
> > quote is the defined and documented way to get a single quote into the
> column/variable. It is even
> > defined in the ANSI/ISO SQL standard.
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "darkstar_E2" <darwin_e@comtechsolutions.com> wrote in message
> > news:%23RngP4gpEHA.3300@TK2MSFTNGP12.phx.gbl...
> > > Dear All,
> > > I have some trouble handling the apostrophe( ' ) contained in a string.
> Let
> > > say i have a string (boy's game). How can i pass this to a variable @str
> .
> > > I can't pass this using this:
> > >     set @str = 'boy's game' -> this will produce error.
> > > But i do not want to use this:
> > >     set @str = 'boy''s game'
> > > Please reply to this problem....
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: General Question: Single Quotes in Text Fields
    ... StoredPrcedure in the SQL server, when single quote involved. ... Good Luck ... to morph apostrophes into some other character or just delete them ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL query fails
    ... > that is acceptable to Oracle, SQL Server, and Access. ... > this requires using the single quote as the literal string delimiter as well ... > thise case determines it to be the single quote. ...
    (microsoft.public.access.queries)
  • Re: bound forms
    ... If you're using a pass-through query to SQL Server, ... As to editing between a control and the table to which it's bound, ... > string between a control and the table it is bound to, the single quote ... >>> Is there any straightforward way of getting between a bound textbox and ...
    (microsoft.public.access.forms)
  • Re: How to store the string with single quote in Microsoft SQL Server CE 2.0 using C# CF programme
    ... Dont know about SQL Server CE 2.0 - but did you try the old fashion SQL way? ... > functions in mySQL api so that we can save the single quote into MySql ... > database using C programme. ... How to save the single quote string ...
    (microsoft.public.dotnet.languages.csharp)