What Did I Miss Here?

  • Thread starter Thread starter Wayne Wengert
  • Start date Start date
W

Wayne Wengert

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)
 
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>
 
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
 
Jared;

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

Wayne
 
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
 
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
 
Is the Use Quoted Identifiers option checked in your database property sheet
under the Options tab?
 
Jared;

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

Wayne
 
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/defaul...port/kb/articles/Q178/0/70.asp&NoWebContent=1
 
Jared;

Thanks, I'll follow up on that.

Wayne

Jared said:
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/defaul...port/kb/articles/Q178/0/70.asp&NoWebContent=1
Click to expand...
 
Back
Top