Question on the replace function for Inserting into SQL

  • Thread starter Thread starter Nancy.
  • Start date Start date
N

Nancy.

Hello:
I have a dataset field:
Description='STRUCTURES. SWALE INLET TYPE D-3(36" DIA.)
(<10' DEEP)'

I want to save several fields including the description
into a SQL table.

The replace("'","''") should pass the string as :
'STRUCTURES. SWALE INLET TYPE D-3(36" DIA.)(<10'' DEEP)'

But, instead keeps writing the two singles after writing
a comma which confuses the statesment:
'STRUCTURES. SWALE INLET TYPE D-3(36" DIA.)(<10',''
DEEP)'

This is my Insert code:
m_StrQuery = "INSERT INTO TABLE_A " & _
"(awardid, pwnumber,
itemnumber,description,unitid,contractquantity,contractuni
tprice,typeflag,ntuser,datein) VALUES " & _
" ('" & awardid & "','" & _
pwnumber & "','" & _
itemnumber & "','" & _
description.Replace("'", "''") &
_
"','" & unitid & " '," & _
contractqty & "," & _
contractprice & ",'" & _
typeflag & "','" & _
m_strWinNTUser & "','" & _
dtToday & "')"

Could you please let me know how should I do it?
Thanks!
Nancy.
 
Hi Nancy,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to insert a record which
contains single quotes in a text field. If there is any misunderstanding,
please feel free to let me know.

I'm not quite sure about you metioned in your last post:

"But, instead keeps writing the two singles after writing
a comma which confuses the statesment:
'STRUCTURES. SWALE INLET TYPE D-3(36" DIA.)(<10','' DEEP)'"

Do you mean the generated string added a comma or when you add a comma to
the string, the SQL statement cannot be generated correctly?

To avoid dealing with the reserved characters in the SQL statement, we can
use parameters. Here is an example:

Dim m_StrQuery as String= "INSERT INTO table22 (a) VALUES (@a)"
Dim com as New SqlCommand(m_StrQuery, this.sqlConnection1)
Dim param as SqlParameter = com.Parameters.Add("@a", SqlDbType.VarChar,
100)
param.Value = "STRUCTURES. SWALE INLET TYPE D-3(36\" DIA.)(<10' DEEP)"

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Nancy. said:
I have a dataset field:
Description='STRUCTURES. SWALE INLET TYPE D-3(36" DIA.)
(<10' DEEP)'

I want to save several fields including the description
into a SQL table.
Click to expand...

I would suggest that instead of trying to do the SQL escaping yourself,
you should place the SQL with placeholder parameters in a command, and
then set the values of the parameters directly.

This is a much safer way of passing parameters, and also means you
don't need to deal with data type conversions yourself.
 
Nancy:

I'm with Kevin and Jon 100% on this. Using Dynamic SQL is going to be very
error prone b/c invariably you are going to forget to escape the stuff at
one point. At best it's going to cause errors in your app that you are
going to have to fix after an annoyed user discovers it. At worst, a hacker
(if your app is exposed to the outside world) or a disgruntled employee
might send you an email with the contents of your database proving that
their injection attack worked. In addition, parameterized queries are more
efficient. The first reason alone is reason enough. The second reason is
the most critical though. These days there are more than enough script
kiddies out there trying to mess up your DB and ruin your career..and if
your information is important, the extent of the damage could end up hurting
a lot more people than just you. Moreover, using Params makes your code's
intentionds very clear so it will be easier for other developers to
understand. In a nutshell, there's all upside to using params and no
downside.

HTH,

Bill
 
Hi Nancy,

I'm so glad that my suggestion works. Thank you for sharing your experience
with everyone here. If you have any questions, please feel free to post
them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top