Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 12th, 2007, 07:39 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Custom script to remove special characters

Hi everyone,

So I have a problem. I have a MS SQL database and in it are fields (TEXT) that contain certain characters I'd like to either replace or delete. I orginally tried the following in SQL Query Analyzer:

Code:
UPDATE Products_Descriptions
SET ProductDescription = REPLACE(LTRIM(RTRIM(ProductDescription)), '©', '©')
But that didn't work because MS SQL can't use the replace command on a TEXT field. In the above example I'm trying to replace the copyright symbol with the HTML equivalent. Anyhoo... I Googled my problem and found this forum and another thread with someone that wanted to do something similar. He ran into my same problem using the REPLACE function on a TEXT table in MS SQL. Someone post the code below for him (he wanted to replace one URL with another URL).

Code:
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


So... my question is... can some help me with the above code? I'm wise enough to figure out how to replace the URLs above with my characters I want replaced as well as changing his tables to my tables (yea... like that's smart) :)

I'm wondering if I can past the above into a blank ASP page, change the connection settings, and it will automatically run against my database as soon as I load the page?

If the above is true, can someone show me how to change the above code to connect to my database? Lets assume the server IP is 1.2.3.4, database is database1, login is login1, and password in password1.

Can I use something like I'm already using for one of my applications?

Code:
<%

    Dim db_database, db_Username, db_password, db_server
    db_database = "database1"
    db_username = "login1"
    db_password = "password1"
    db_server   = "1.2.3.4"
    Config_ConnectionString = "Provider=SQLOLEDB; Data Source=" & db_server & "; Initial Catalog=" & db_database & "; User ID="
 & db_username & "; Password=" & db_password & "; Network Library=dbmssocn; OLE DB Services=-1;"

Config_DatabaseServer = "SQL"

%>
Any help would REALLY be appreciated. I'm a little lost when it comes to this stuff but even if you can't spell everything out for me if you could at least point me in the right direction I'd be happy :)
Reply With Quote
  #2 (permalink)  
Old January 12th, 2007, 07:49 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Code:
<%@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

%>
Reply With Quote
  #3 (permalink)  
Old January 12th, 2007, 08:05 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
code to remove ms word special characters vikaspa Pro PHP 0 November 27th, 2008 10:20 PM
storing special special characters in nvarchar... ACE2084 SQL Server 2000 2 February 9th, 2005 11:45 AM
special characters lian_a Classic ASP Basics 3 June 23rd, 2004 05:16 AM
special characters in url lian_a Classic ASP Basics 2 May 4th, 2004 03:05 AM



All times are GMT -4. The time now is 03:45 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.