kreplech Posted December 3, 2007 Share Posted December 3, 2007 Hello all,Below is a little script I wrote to remove URL encoding from every field in a table. I had to write it because many of the database fields are NTEXT barring me from using the SQL REPLACE function. So I'll need to use the following VBS script as a scheduled windows task on the server (URL encoded data is imported nightly)... easy enough. Well, for some reason the update isn't committing - despite the fact that the script runs without any errors. I'd like to use rs.update rather than writing an UPDATE statement since my table has no primary key (GASP!). Anyway, if you can spot the problem, please fill me in.Thanks in advance for any help,M_____________________________________________Set conn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")conn.open "Driver={SQL Native Client};Server=server;Database=db;Uid=user;Pwd=pass;"rs.cursorlocation = 3rs.Open "select * from thisTable;", conn, 2, 4While Not rs.EOF For Each Field In rs.Fields If (Field.Type = 202 Or Field.Type = 203 Or Field.Type = 201) And Not (IsNull(Field.Value)) And Field.Value <> "" Then updateVal = Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Field.Value, "%20", " "), "%2F", "/"), "%2C", ","), "%3A", ":"), "%25", "%"), "%5C%27", "'"), "%85", "..."), "%93", "'"), "%94", "'"), "%3F", "?"), "%E2%80%99", ""), "%5C%22", "'"), "%28", "("), "%29", ")"), "%26", "&"), "%3B", ";"), "%96", "–"), "%97", "—"), "%92", "’"), "%0d", ""), "%0a", ""), "%23", "#"), "%22", "'"), "%27", "'"), "%7E", "~"), "%24", "$"), "%95", "•"), "%09", " "), "%5C%22", "'")) If updateVal <> Field.Value Then msgbox(Field.Value) ' Correctly displays the unReplaced field rs(Field.Name).Value = updateVal rs.update msgbox(Field.Value) ' Correctly display the Replaced field, but update never happened... End If End If Next rs.MoveNextWendrs.Closeconn.Closemsgbox("Done!") Link to comment Share on other sites More sharing options...
kreplech Posted December 4, 2007 Author Share Posted December 4, 2007 found the fix... and i'm feeling very impressed with myself.I dropped the rs.cursorlocation = 3 altogetherand changed the open recordset statement to:rs.Open "select * from thisTable;", conn, 2, 2 ' adOpenDynamic which seems kinda obvious now.Thanks!M Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.