Following on from http://forum.snitz.com/forum/topic.asp?TOPIC_ID=42974
Needed to do a find and replace on several tables/columns in my db which I couldn't do from t-sql (sql server) due to the datatypes. Came up with the following code/page that allows you to enter the tablename, colomn, text to find and text to replace with.
Copy the following code into a new file called admin_find_and_replace.asp
<%
'#################################################################################
'## Copyright (C) 2000-02 Michael Anderson, Pierre Gorissen,
'## Huw Reddick and Richard Kinser
'##
'## This program is free software; you can redistribute it and/or
'## modify it under the terms of the GNU General Public License
'## as published by the Free Software Foundation; either version 2
'## of the License, or any later version.
'##
'## All copyright notices regarding Snitz Forums 2000
'## must remain intact in the scripts and in the outputted HTML
'## The "powered by" text/logo with a link back to
'## http://forum.snitz.com in the footer of the pages MUST
'## remain visible when the pages are viewed on the internet or intranet.
'##
'## This program is distributed in the hope that it will be useful,
'## but WITHOUT ANY WARRANTY; without even the implied warranty of
'## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
'## GNU General Public License for more details.
'##
'## You should have received a copy of the GNU General Public License
'## along with this program; if not, write to the Free Software
'## Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
'##
'## Support can be obtained from support forums at:
'## http://forum.snitz.com
'##
'## Correspondence and Marketing Questions can be sent to:
'## reinhold@bigfoot.com
'##
'## or
'##
'## Snitz Communications
'## C/O: Michael Anderson
'## PO Box 200
'## Harpswell, ME 04079
'#################################################################################
%>
<!--#INCLUDE FILE="config.asp"-->
<!--#INCLUDE FILE="inc_sha256.asp"-->
<!--#INCLUDE FILE="inc_header.asp" -->
<!--#INCLUDE FILE="inc_func_admin.asp" -->
<%
Server.ScriptTimeout = 99999999
if Session(strCookieURL & "Approval") <> "15916941253" then
scriptname = split(request.servervariables("SCRIPT_NAME"),"/")
Response.Redirect "admin_login.asp?target=" & scriptname(ubound(scriptname))
end if
Response.Write " <table border=""0"" width=""100%"">" & vbNewLine & _
" <tr>" & vbNewLine & _
" <td width=""33%"" align=""left"" nowrap><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & vbNewLine & _
" " & getCurrentIcon(strIconFolderOpen,"","") & " <a href=""default.asp"">All Forums</a><br />" & vbNewLine & _
" " & getCurrentIcon(strIconBar,"","") & getCurrentIcon(strIconFolderOpen,"","") & " <a href=""admin_home.asp"">Admin Section</a><br />" & vbNewLine & _
" " & getCurrentIcon(strIconBlank,"","") & getCurrentIcon(strIconBar,"","") & getCurrentIcon(strIconFolderOpenTopic,"","") & " Find and Replace<br /></font></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" </table>" & vbNewLine
if Request.Form("Method_Type") = "Do_Replace" then
Err_Msg = ""
strTable = Request.Form("txtTable")
strCol = Request.Form("txtCol")
strFind = Request.Form("txtFind")
strReplace = Request.Form("txtReplace")
if strTable = "" then Err_Msg = Err_Msg & "<li>You Must Enter the Table Name</li>"
if strCol = "" then Err_Msg = Err_Msg & "<li>You Must Enter the Column Name</li>"
if strFind = "" then Err_Msg = Err_Msg & "<li>You Must Enter the text to find</li>"
if strReplace = "" then Err_Msg = Err_Msg & "<li>You Must Enter the replacement text</li>"
if Err_Msg = "" then
strSql1 = "SELECT "& strCol &" FROM "& strTable &" WHERE "& strCol &" LIKE '%"& strFind &"%'"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql1, my_Conn, adOpenForwardOnly, adLockOptimistic
do while not rs.EOF
rs(strCol) = replace(rs(strCol),strFind,strReplace)
rs.Update
rs.MoveNext
loop
rs.Close
set rs = nothing
Response.Write " <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """>Find and Replace Complete!</font></p>" & vbNewLine & _
" <meta http-equiv=""Refresh"" content=""2; URL=admin_home.asp"">" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """>Congratulations!</font></p>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><a href=""admin_home.asp"">Back To Admin Home</font></a></p>" & vbNewLine
else
Response.Write " <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """ color=""" & strHiLiteFontColor & """>There Was A Problem With Your Details</font></p>" & vbNewLine & _
" <table align=""center"" border=""0"">" & vbNewLine & _
" <tr>" & vbNewLine & _
" <td><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHiLiteFontColor & """><ul>" & Err_Msg & "</ul></font></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" </table>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><a href=""JavaScript:history.go(-1)"">Go Back To Enter Data</a></font></p>" & vbNewLine
end if
else
Response.Write " <form action=""admin_find_and_replace.asp"" method=""post"">" & vbNewLine & _
" <input type=""hidden"" name=""Method_Type"" value=""Do_Replace"">" & vbNewLine & _
" <table border=""0"" cellspacing=""0"" cellpadding=""0"" align=""center"">" & vbNewLine & _
" <tr>" & vbNewLine & _
" <td bgcolor=""" & strPopUpBorderColor & """>" & vbNewLine & _
" <table border=""0"" cellspacing=""1"" cellpadding=""1"">" & vbNewLine & _
" <tr valign=""middle"">" & vbNewLine & _
" <td bgcolor=""" & strHeadCellColor & """ colspan=""2""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """><b>Find and Replace</b></font></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" <tr valign=""middle"">" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ align=""right""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><b>Table Name:</b> </font></td>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """><input type=""text"" name=""txtTable"" size=""30""></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" <tr valign=""middle"">" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ align=""right""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><b>Column Name:</b> </font></td>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """><input type=""text"" name=""txtCol"" size=""30""></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" <tr valign=""middle"">" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ align=""right""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><b>Find:</b> </font></td>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """><input type=""text"" name=""txtFind"" size=""30""></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" <tr valign=""middle"">" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ align=""right""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><b>Replace:</b> </font></td>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """><input type=""text"" name=""txtReplace"" size=""30""></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" <tr valign=""middle"">" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ colspan=""2"" align=""center""><input type=""submit"" value=""Submit""> <input type=""reset"" value=""Reset Old Values"" id=""reset1"" name=""reset1""></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" </table>" & vbNewLine & _
" </td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" </table>" & vbNewLine & _
" </form>" & vbNewLine
end if
WriteFooter
Response.End
%>
Topic Locked
Posted - 28 March 2003 : 19:08:05
