ASP Script to replace certain characters from a database with another

Ok... so I originally wanted to do this using MS SQL's Query analizer but it appears the REPLACE function doesn't work in TEXT fields. So... next idea I found was to create a simple ASP script to run against the DB. This is what I found:

********************************************

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Application("conn")

SQL = "select * from table1;"

Set oRS = oConn.Execute(SQL)
Do while not oRS.EOF
SQL2 = "UPDATE table1 SET URLs = '" & Replace(oRS("URLs"),"http://www.bad.com", "http://www.good.com") & "';"
oConn.Execute(SQL2)
oRS.Movenext
Loop
oRS.Close
Set oRS = Nothing
oConn.Close
Set oRS = Nothing

******************************************

Now... the person who gave that example above wanted to replace one URL in their database with another URL. So I have 2 questions.

1.) Will that script above replace just the characters I want replaced or will it effect the entire column of the table I'm editing? For example, if the database has "This is Mike from Wazoo" and I run a script to replace Mike with Tim, would the script change to "This is Tim from Wazoo"? I just want to make sure it won't replace the entire field. I want only the text I say to be changed... everything else stays the same.

2.) Can someone modify the above code to work with my database (I don't know enough ASP to mess around with it)? My table I need to edit is Products_Descriptions and the column I need to modify is ProductDescription. Lets say my server IP for the DB connection is 1.2.3.4 and my datbase is: database1, login is: login1, and pass is: pass1. For replacing characters I can figure that one out

 

Would the following code work? It's my best personal attempt!

Also... would I just upload that to my server and run it from a web browser?

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabase "
%>

<%
    SQL = "select * from Products_Descriptions;"

    Set oRS = cnn.Execute(SQL)
    Do while not oRS.EOF
    SQL2 = "UPDATE Products_Descriptions SET ProductDescription = '" & Replace(oRS("ProductDescription"),"©", "&copy;") & "';"
    cnn.Execute(SQL2)
    oRS.Movenext
    Loop
    oRS.Close
    Set oRS = Nothing
    cnn.Close
    Set oRS = Nothing
    
%>

Code (markup):

 

Decided to run it and see what happened... the following error posted to the page:

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

/SQL_update.asp, line 14

 

There's major flaw in your code. You are executing update on the whole database for each row in the database. Anyway, does this article help http://www.sqlteam.com/item.asp?ItemID=15528 ?

 

I thought I was only running against the Products_Descriptions table and the ProductDescription column.

So you're saying using SQL querey analizer I can modify the text field within my table? I guess running a script works around the REPLACE issue in a TEXT field?

I looked over everythiing but I'm still a bit confused. I understand how 60% of the commands work in the file:

http://www.sqlteam.com/downloads/15528SearchReplace.sql

but I don't understand about 40% of the commands and I really have no clue on where to replace his information with mine. Think you could help me out?

My table is Products_Descriptions
My Column in that table is ProductDescription
The text I want to replace is ©
I want to replace that text with ©

If you can help me further I'd really appreciate it.

 

Ok try this code... this is what's going on:
1) Select the product ID and description from rows that has a @ symbol.
2) Loop thru the result and update each description replacing @ with ©.

Replace "ProductID" in the code below with whatever you named your primary key column. Put the script on your server and call it from the browser. You should backup your data or test this on a small table. Remove the script from the server after it worked. Let me know how it goes.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabase "
%>

<%
    SQL = "select ProductID, ProductDescription from Products_Descriptions WHERE PATINDEX('%@%', ProductDescription) > 0;"

    Set oRS = cnn.Execute(SQL)
    Do while not oRS.EOF
       SQL2 = "UPDATE Products_Descriptions SET ProductDescription = '" & Replace(oRS("ProductDescription"),"©", "&copy;") & "' WHERE ProductID=" & oRS("ProductID") & ";"
       cnn.Execute(SQL2)
    oRS.Movenext
    Loop
    oRS.Close
    Set oRS = Nothing
    cnn.Close
    Set oRS = Nothing
    
%>

PHP:

 

Hi... thanks for trying to help. My primary key column is ProductID so I left what you had and simply entered my database information. The following error posted to the screen when I tried to tun it:

Microsoft OLE DB Provider for SQL Server error '80040e10'

No value given for one or more required parameters.

/sql_update.asp, line 14

 

You need to print out the UPDATE statements to see what the problem is.