vbscript loading data into SQL Server 2005 from Excel

Question

Post reply

vbscript loading data into SQL Server 2005 from Excel

MisplacedChildhood

Ten Centuries

Points: 1345
More actions
February 29, 2012 at 3:29 pm

#143220

I received this email from a developer... I'm not that familiar with VB and told him the 2 single quotes works with SQL. It still fails for him after adding the 2nd quote.
-----------------
A puzzle.
The attached vbscript works until it tries to upload a description variable that has an Apostrophe (') in it.
I've attempted to add an additional apostrophe because it appeared from online research that I needed to double up on the apostrophe to make it work. See attached code.
I'm still getting the error message:
(see attachment .jpg)
There's a description on the offending record field: "Policyholders' Surplus".
Do you have any suggestions that might help me get this to work?
script is attached as well as .txt
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

I can't tell exactly what is wrong from reading that code, but why not try REPLACE instead of your own function?

REPLACE(myString,"'","''")

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

MisplacedChildhood Ten Centuries Points: 1345 More actions · Answer 2

mister.magoo (2/29/2012)
I can't tell exactly what is wrong from reading that code, but why not try REPLACE instead of your own function?
REPLACE(myString,"'","''")

The developer did try the replace statement, but got the identical error message. So apparently his code was working correctly. However, there must be some other issue here that I’m missing.

MisplacedChildhood Ten Centuries Points: 1345 More actions · Answer 3

Thanks MM. Issue resolved. He used REPLACE which solved that column error, but then he was getting the same error on ANOTHER column. It took a while to figure it out as he assumed it was the same column.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Thanks for the feedback..

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);