I am using VB.NET connected to an SQL 2000 server and I want to save some
text fields from a form to string fields in a table. The text might include
some single quotes so in my update command I am replacing single quotes with
two single quotes so that the update query works. (see piece of statement
below)

My problem is that when I look at what really gets stored in the table, all
single quotes are now doubled such as:

Text Value = Joe's
Stored in table = Joe''s

What am I missing here?


============ Segment of SQL statement =============


Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text, "'", "''")
& "',.....

instead of using two single quotes try and parameterize your query.

ie using SqlParameter
ideal way is to use SqlParameters with stored procedures but they can be
used with plain old inserts / updates

look it up

with parameters you dont need to fix the quotes before inserting them

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
"Wayne Wengert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I am using VB.NET connected to an SQL 2000 server and I want to save some
> text fields from a form to string fields in a table. The text might
include
> some single quotes so in my update command I am replacing single quotes
with
> two single quotes so that the update query works. (see piece of statement
> below)
>
> My problem is that when I look at what really gets stored in the table,
all
> single quotes are now doubled such as:
>
> Text Value = Joe's
> Stored in table = Joe''s
>
> What am I missing here?
>
>
> ============ Segment of SQL statement =============
>
>
> Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text, "'",
"''")
> & "',.....
>
>

Have you tried to execute your function without replacing your quotes? Does
it produce the results you want? Is possible that your dataadapter or
whatever you are using escapes this char for you? I know the xmldocument
object does this intrinsically.

obj.innerText = "Amos & Andrew"
- Produces -
<obj>Amos &amp; Andrew</obj>


"Wayne Wengert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am using VB.NET connected to an SQL 2000 server and I want to save some
> text fields from a form to string fields in a table. The text might
> include
> some single quotes so in my update command I am replacing single quotes
> with
> two single quotes so that the update query works. (see piece of statement
> below)
>
> My problem is that when I look at what really gets stored in the table,
> all
> single quotes are now doubled such as:
>
> Text Value = Joe's
> Stored in table = Joe''s
>
> What am I missing here?
>
>
> ============ Segment of SQL statement =============
>
>
> Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text, "'",
> "''")
> & "',.....
>
>

Thanks Hermit. I'll try that approach but I am still confused as to why the
replace approach doesn't work. I stopped the app and displayed the sql
string with the doubled instances of a single quote. If I copy/past that
into QA and run it, it works as expected but when run from my ASP app, it
stored the doubled single quotes?

Wayne

"Hermit Dave" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> instead of using two single quotes try and parameterize your query.
>
> ie using SqlParameter
> ideal way is to use SqlParameters with stored procedures but they can be
> used with plain old inserts / updates
>
> look it up
>
> with parameters you dont need to fix the quotes before inserting them
>
> --
>
> Regards,
>
> Hermit Dave
> (http://hdave.blogspot.com)
> "Wayne Wengert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I am using VB.NET connected to an SQL 2000 server and I want to save
some
> > text fields from a form to string fields in a table. The text might
> include
> > some single quotes so in my update command I am replacing single quotes
> with
> > two single quotes so that the update query works. (see piece of
statement
> > below)
> >
> > My problem is that when I look at what really gets stored in the table,
> all
> > single quotes are now doubled such as:
> >
> > Text Value = Joe's
> > Stored in table = Joe''s
> >
> > What am I missing here?
> >
> >
> > ============ Segment of SQL statement =============
> >
> >
> > Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text, "'",
> "''")
> > & "',.....
> >
> >
>
>

Jared;

Thanks for the reply. If I don't use the replace function the sql statement
blows up!

Wayne

"Jared" <@(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Have you tried to execute your function without replacing your quotes?
Does
> it produce the results you want? Is possible that your dataadapter or
> whatever you are using escapes this char for you? I know the xmldocument
> object does this intrinsically.
>
> obj.innerText = "Amos & Andrew"
> - Produces -
> <obj>Amos &amp; Andrew</obj>
>
>
> "Wayne Wengert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I am using VB.NET connected to an SQL 2000 server and I want to save some
> > text fields from a form to string fields in a table. The text might
> > include
> > some single quotes so in my update command I am replacing single quotes
> > with
> > two single quotes so that the update query works. (see piece of
statement
> > below)
> >
> > My problem is that when I look at what really gets stored in the table,
> > all
> > single quotes are now doubled such as:
> >
> > Text Value = Joe's
> > Stored in table = Joe''s
> >
> > What am I missing here?
> >
> >
> > ============ Segment of SQL statement =============
> >
> >
> > Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text, "'",
> > "''")
> > & "',.....
> >
> >
>
>

In the database is it stored with two single quotes '' or one double quote
"? Could you have mistyped the replacement string? All the documentation
I've found supports your method. Could it have something to do with your
colation?
If you paste this block into query analyzer what does it produce?

CREATE TABLE #MyTable (
Col1 varchar(25),
Col2 varchar(25))

INSERT INTO #MyTable VALUES('it''s a boy', 'it''s a girl')
SELECT * FROM #MyTable
DROP TABLE #MyTable

"Wayne Wengert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Jared;
>
> Thanks for the reply. If I don't use the replace function the sql
> statement
> blows up!
>
> Wayne
>
> "Jared" <@(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Have you tried to execute your function without replacing your quotes?
> Does
>> it produce the results you want? Is possible that your dataadapter or
>> whatever you are using escapes this char for you? I know the xmldocument
>> object does this intrinsically.
>>
>> obj.innerText = "Amos & Andrew"
>> - Produces -
>> <obj>Amos &amp; Andrew</obj>
>>
>>
>> "Wayne Wengert" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >I am using VB.NET connected to an SQL 2000 server and I want to save
>> >some
>> > text fields from a form to string fields in a table. The text might
>> > include
>> > some single quotes so in my update command I am replacing single quotes
>> > with
>> > two single quotes so that the update query works. (see piece of
> statement
>> > below)
>> >
>> > My problem is that when I look at what really gets stored in the table,
>> > all
>> > single quotes are now doubled such as:
>> >
>> > Text Value = Joe's
>> > Stored in table = Joe''s
>> >
>> > What am I missing here?
>> >
>> >
>> > ============ Segment of SQL statement =============
>> >
>> >
>> > Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text, "'",
>> > "''")
>> > & "',.....
>> >
>> >
>>
>>
>
>

In the database it is being stored as two single quotes. I've verified that
I really am typing two single quotes and when I run your sample in QA it
works as expected (it's a boy it's a girl)

I've used this replace method many times in ASP and it has always worked
fine. I am wondering if there is something special in .NET?

Wayne

"Jared" <@(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In the database is it stored with two single quotes '' or one double quote
> "? Could you have mistyped the replacement string? All the documentation
> I've found supports your method. Could it have something to do with your
> colation?
> If you paste this block into query analyzer what does it produce?
>
> CREATE TABLE #MyTable (
> Col1 varchar(25),
> Col2 varchar(25))
>
> INSERT INTO #MyTable VALUES('it''s a boy', 'it''s a girl')
> SELECT * FROM #MyTable
> DROP TABLE #MyTable
>
> "Wayne Wengert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Jared;
> >
> > Thanks for the reply. If I don't use the replace function the sql
> > statement
> > blows up!
> >
> > Wayne
> >
> > "Jared" <@(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Have you tried to execute your function without replacing your quotes?
> > Does
> >> it produce the results you want? Is possible that your dataadapter or
> >> whatever you are using escapes this char for you? I know the
xmldocument
> >> object does this intrinsically.
> >>
> >> obj.innerText = "Amos & Andrew"
> >> - Produces -
> >> <obj>Amos &amp; Andrew</obj>
> >>
> >>
> >> "Wayne Wengert" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >I am using VB.NET connected to an SQL 2000 server and I want to save
> >> >some
> >> > text fields from a form to string fields in a table. The text might
> >> > include
> >> > some single quotes so in my update command I am replacing single
quotes
> >> > with
> >> > two single quotes so that the update query works. (see piece of
> > statement
> >> > below)
> >> >
> >> > My problem is that when I look at what really gets stored in the
table,
> >> > all
> >> > single quotes are now doubled such as:
> >> >
> >> > Text Value = Joe's
> >> > Stored in table = Joe''s
> >> >
> >> > What am I missing here?
> >> >
> >> >
> >> > ============ Segment of SQL statement =============
> >> >
> >> >
> >> > Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text,
"'",
> >> > "''")
> >> > & "',.....
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Is the Use Quoted Identifiers option checked in your database property sheet
under the Options tab?

"Wayne Wengert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In the database it is being stored as two single quotes. I've verified
> that
> I really am typing two single quotes and when I run your sample in QA it
> works as expected (it's a boy it's a girl)
>
> I've used this replace method many times in ASP and it has always worked
> fine. I am wondering if there is something special in .NET?
>
> Wayne
>
> "Jared" <@(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> In the database is it stored with two single quotes '' or one double
>> quote
>> "? Could you have mistyped the replacement string? All the documentation
>> I've found supports your method. Could it have something to do with your
>> colation?
>> If you paste this block into query analyzer what does it produce?
>>
>> CREATE TABLE #MyTable (
>> Col1 varchar(25),
>> Col2 varchar(25))
>>
>> INSERT INTO #MyTable VALUES('it''s a boy', 'it''s a girl')
>> SELECT * FROM #MyTable
>> DROP TABLE #MyTable
>>
>> "Wayne Wengert" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Jared;
>> >
>> > Thanks for the reply. If I don't use the replace function the sql
>> > statement
>> > blows up!
>> >
>> > Wayne
>> >
>> > "Jared" <@(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> Have you tried to execute your function without replacing your quotes?
>> > Does
>> >> it produce the results you want? Is possible that your dataadapter or
>> >> whatever you are using escapes this char for you? I know the
> xmldocument
>> >> object does this intrinsically.
>> >>
>> >> obj.innerText = "Amos & Andrew"
>> >> - Produces -
>> >> <obj>Amos &amp; Andrew</obj>
>> >>
>> >>
>> >> "Wayne Wengert" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >I am using VB.NET connected to an SQL 2000 server and I want to save
>> >> >some
>> >> > text fields from a form to string fields in a table. The text might
>> >> > include
>> >> > some single quotes so in my update command I am replacing single
> quotes
>> >> > with
>> >> > two single quotes so that the update query works. (see piece of
>> > statement
>> >> > below)
>> >> >
>> >> > My problem is that when I look at what really gets stored in the
> table,
>> >> > all
>> >> > single quotes are now doubled such as:
>> >> >
>> >> > Text Value = Joe's
>> >> > Stored in table = Joe''s
>> >> >
>> >> > What am I missing here?
>> >> >
>> >> >
>> >> > ============ Segment of SQL statement =============
>> >> >
>> >> >
>> >> > Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text,
> "'",
>> >> > "''")
>> >> > & "',.....
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Jared;

Exactly where is this database property sheet? I'm not sure I understand
what you are referring to?

Wayne

"Jared" <@(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Is the Use Quoted Identifiers option checked in your database property
sheet
> under the Options tab?
>
> "Wayne Wengert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > In the database it is being stored as two single quotes. I've verified
> > that
> > I really am typing two single quotes and when I run your sample in QA it
> > works as expected (it's a boy it's a girl)
> >
> > I've used this replace method many times in ASP and it has always worked
> > fine. I am wondering if there is something special in .NET?
> >
> > Wayne
> >
> > "Jared" <@(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> In the database is it stored with two single quotes '' or one double
> >> quote
> >> "? Could you have mistyped the replacement string? All the
documentation
> >> I've found supports your method. Could it have something to do with
your
> >> colation?
> >> If you paste this block into query analyzer what does it produce?
> >>
> >> CREATE TABLE #MyTable (
> >> Col1 varchar(25),
> >> Col2 varchar(25))
> >>
> >> INSERT INTO #MyTable VALUES('it''s a boy', 'it''s a girl')
> >> SELECT * FROM #MyTable
> >> DROP TABLE #MyTable
> >>
> >> "Wayne Wengert" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Jared;
> >> >
> >> > Thanks for the reply. If I don't use the replace function the sql
> >> > statement
> >> > blows up!
> >> >
> >> > Wayne
> >> >
> >> > "Jared" <@(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> >> Have you tried to execute your function without replacing your
quotes?
> >> > Does
> >> >> it produce the results you want? Is possible that your dataadapter
or
> >> >> whatever you are using escapes this char for you? I know the
> > xmldocument
> >> >> object does this intrinsically.
> >> >>
> >> >> obj.innerText = "Amos & Andrew"
> >> >> - Produces -
> >> >> <obj>Amos &amp; Andrew</obj>
> >> >>
> >> >>
> >> >> "Wayne Wengert" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> >I am using VB.NET connected to an SQL 2000 server and I want to
save
> >> >> >some
> >> >> > text fields from a form to string fields in a table. The text
might
> >> >> > include
> >> >> > some single quotes so in my update command I am replacing single
> > quotes
> >> >> > with
> >> >> > two single quotes so that the update query works. (see piece of
> >> > statement
> >> >> > below)
> >> >> >
> >> >> > My problem is that when I look at what really gets stored in the
> > table,
> >> >> > all
> >> >> > single quotes are now doubled such as:
> >> >> >
> >> >> > Text Value = Joe's
> >> >> > Stored in table = Joe''s
> >> >> >
> >> >> > What am I missing here?
> >> >> >
> >> >> >
> >> >> > ============ Segment of SQL statement =============
> >> >> >
> >> >> >
> >> >> > Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text,
> > "'",
> >> >> > "''")
> >> >> > & "',.....
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

In Enterprise manager, right click on the database, choose properties, look
on the options tab, there is a check box specifying the use of quoted
identifiers. You will have to do a little more reasearch on what this
actually does, sql server books online has a pretty lengthy article on it.

I found this article as well, I didn't get a chance to read it all, but, it
looked like it had some relevance.
http://support.microsoft.com/default...NoWebContent=1

"Wayne Wengert" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Jared;
>
> Exactly where is this database property sheet? I'm not sure I understand
> what you are referring to?
>
> Wayne
>
> "Jared" <@(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Is the Use Quoted Identifiers option checked in your database property
> sheet
>> under the Options tab?
>>
>> "Wayne Wengert" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > In the database it is being stored as two single quotes. I've verified
>> > that
>> > I really am typing two single quotes and when I run your sample in QA
>> > it
>> > works as expected (it's a boy it's a girl)
>> >
>> > I've used this replace method many times in ASP and it has always
>> > worked
>> > fine. I am wondering if there is something special in .NET?
>> >
>> > Wayne
>> >
>> > "Jared" <@(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> In the database is it stored with two single quotes '' or one double
>> >> quote
>> >> "? Could you have mistyped the replacement string? All the
> documentation
>> >> I've found supports your method. Could it have something to do with
> your
>> >> colation?
>> >> If you paste this block into query analyzer what does it produce?
>> >>
>> >> CREATE TABLE #MyTable (
>> >> Col1 varchar(25),
>> >> Col2 varchar(25))
>> >>
>> >> INSERT INTO #MyTable VALUES('it''s a boy', 'it''s a girl')
>> >> SELECT * FROM #MyTable
>> >> DROP TABLE #MyTable
>> >>
>> >> "Wayne Wengert" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > Jared;
>> >> >
>> >> > Thanks for the reply. If I don't use the replace function the sql
>> >> > statement
>> >> > blows up!
>> >> >
>> >> > Wayne
>> >> >
>> >> > "Jared" <@(E-Mail Removed)> wrote in message
>> >> > news:(E-Mail Removed)...
>> >> >> Have you tried to execute your function without replacing your
> quotes?
>> >> > Does
>> >> >> it produce the results you want? Is possible that your dataadapter
> or
>> >> >> whatever you are using escapes this char for you? I know the
>> > xmldocument
>> >> >> object does this intrinsically.
>> >> >>
>> >> >> obj.innerText = "Amos & Andrew"
>> >> >> - Produces -
>> >> >> <obj>Amos &amp; Andrew</obj>
>> >> >>
>> >> >>
>> >> >> "Wayne Wengert" <(E-Mail Removed)> wrote in message
>> >> >> news:(E-Mail Removed)...
>> >> >> >I am using VB.NET connected to an SQL 2000 server and I want to
> save
>> >> >> >some
>> >> >> > text fields from a form to string fields in a table. The text
> might
>> >> >> > include
>> >> >> > some single quotes so in my update command I am replacing single
>> > quotes
>> >> >> > with
>> >> >> > two single quotes so that the update query works. (see piece of
>> >> > statement
>> >> >> > below)
>> >> >> >
>> >> >> > My problem is that when I look at what really gets stored in the
>> > table,
>> >> >> > all
>> >> >> > single quotes are now doubled such as:
>> >> >> >
>> >> >> > Text Value = Joe's
>> >> >> > Stored in table = Joe''s
>> >> >> >
>> >> >> > What am I missing here?
>> >> >> >
>> >> >> >
>> >> >> > ============ Segment of SQL statement =============
>> >> >> >
>> >> >> >
>> >> >> > Update UnitShowInfo Set ShowName = '" & Replace(txtShowName.Text,
>> > "'",
>> >> >> > "''")
>> >> >> > & "',.....
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>