Jump to content

(.vbs) Cannot commit rs.update


kreplech

Recommended Posts

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...