Solved

Replace statement in update ASP.NEt

Posted on 2007-03-27
4
191 Views
Last Modified: 2010-03-19
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 ,"'","''")
0
Comment
Question by:lrbrister
  • 3
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18802609
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18802638
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
 

Author Comment

by:lrbrister
ID: 18803995
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18806064
sorry, typo:
Dim p as SqlParameter
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now