Solved

Replace statement in update ASP.NEt

Posted on 2007-03-27
4
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 143

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 143

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 143

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

695 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