Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

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 ,"'","''")
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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()
Avatar of Larry Brister

ASKER

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
sorry, typo:
Dim p as SqlParameter