Replace statement in update ASP.NEt

I need to include a replace statement in the following code.
        Dim oCom2 As SqlCommand = New SqlCommand
        oCom2.Connection = objConn
        objConn.Open()
        Dim sSQL As String
        sSQL = "Update dbo.tblAdTxt "
        sSQL = sSQL & "Set adtxt = '" & ManagedText.Html & "'"
        sSQL = sSQL & ", txtTitle = '" & txtTitle.Text & "'"
        sSQL = sSQL & ", AdType = '" & ListAdType.SelectedValue & "'"
        sSQL = sSQL & ", wrdCnt = '" & Request.Params("txtWordCnt") & "'"

        sSQL = sSQL & " where txtAdID=" & Request.QueryString("txtAdID")
        oCom2.CommandText = sSQL
        oCom2.ExecuteNonQuery()
        oCom2.Dispose()

I need for the
sSQL = sSQL & "Set adtxt = '" & ManagedText.Html & "'"

to allow for an apostrophe in it.
Replace(ManagedText.Html ,"'","''")
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
quick-fix:

Dim oCom2 As SqlCommand = New SqlCommand
        oCom2.Connection = objConn
        objConn.Open()
        Dim sSQL As String
        sSQL = "Update dbo.tblAdTxt "
        sSQL = sSQL & "Set adtxt = '" & Replace(ManagedText.Html ,"'","''")  & "'"
        sSQL = sSQL & ", txtTitle = '" & txtTitle.Text & "'"
        sSQL = sSQL & ", AdType = '" & ListAdType.SelectedValue & "'"
        sSQL = sSQL & ", wrdCnt = '" & Request.Params("txtWordCnt") & "'"

        sSQL = sSQL & " where txtAdID=" & Request.QueryString("txtAdID")
        oCom2.CommandText = sSQL
        oCom2.ExecuteNonQuery()
        oCom2.Dispose()



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
better, to protect against sql injection:

Dim oCom2 As SqlCommand = New SqlCommand
oCom2.Connection = objConn
objConn.Open()
Dim sSQL As String
sSQL = "Update dbo.tblAdTxt "
sSQL = sSQL & "Set adtxt = @adtxt "
sSQL = sSQL & ", txtTitle = '@title "
sSQL = sSQL & ", AdType = @adtype "
sSQL = sSQL & ", wrdCnt = @cnt "
sSQL = sSQL & " where txtAdID=  @id "
oCom2.CommandText = sSQL

Dim p as SqlParamter
p = new SqlParameter("@adtxt", ManagedText.Html )
oCom2.Parameters.Add(p)
p = new SqlParameter("@title ",  txtTitle.Text )
oCom2.Parameters.Add(p)
p = new SqlParameter("@adtype ",  ListAdType.SelectedValue )
oCom2.Parameters.Add(p)
p = new SqlParameter("@cnt ", Request.Params("txtWordCnt")  )
oCom2.Parameters.Add(p)
p = new SqlParameter("@id ",  Request.QueryString("txtAdID"))
oCom2.Parameters.Add(p)

oCom2.ExecuteNonQuery()

        oCom2.Dispose()
0
 
Larry Bristersr. DeveloperAuthor Commented:
angelIII,
  Your first answer works..of course.  But your second...I'm getting a "SqlParamter is not defined" on the part below.  And it's not just the typo...
Using VS2005

Dim p as SqlParamter

SHould I consider the question answered and repost the second one?

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, typo:
Dim p as SqlParameter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.