July 2, 2008 at 11:30 am
I have created an ActiveX script task that reads records from an Informix database for a specific sales order into an ADO recordset and then inserts the data into SQL Server 2000 tables. This works great, until a record in the Informix database containing any apostrophes rears its head. I know that I can get around apostrophes in the data by doubling up on the apostrophes (e.g. O'Malley becomes O''Malley), but the apostrophe could appear anywhere in the string and without parsing every charater in the string to inser another apostrophe (which would make the script run like a dead camel), I can't think of anything obvious.
Here's a simple version of my script:
' Define connection and recordset variables
Dim cnSTX, rsXMC
' Create connection to Informix
Set cnSTX = CreateObject ("ADODB.Connection")
Set rsXMC = CreateObject ("ADODB.Recordset")
' Open DSN
cnSTX.Open "baxti"
' Read records for specific sales order (defined in global variable) into recordset
rsXMC.Open "SELECT socom_ordref, 0 AS line, socom_comseq, 100 AS level, socom_text FROM socomm WHERE socom_comtype = 'H' AND socom_ordline = 0 AND socom_ordref = '" & DTSGlobalVariables("gvSalesOrder").Value & "'", cnSTX
' Define variables for data
Dim cnDW, varOrder, varLine, varSeq, varLevel, varText
' Create destination data source
Set cnDW = CreateObject ("ADODB.Connection")
cnDW.Open = "Provider=sqloledb;Data Source=(local);Initial Catalog=STXdocmnger;Integrated Security=SSPI"
' Assign data to variables
Set varOrder = rsXMC("socom_ordref")
Set varLine = rsXMC("line")
Set varSeq=rsXMC("socom_comseq")
Set varLevel=rsXMC("level")
Set varText=rsXMC("socom_text")
' Insert data until there are no more records in the recordset to process
Do Until rsXMC.EOF
cnDW.Execute "INSERT INTO xACKlines VALUES ('" & varOrder & "','" & varLine & "','" & varSeq & "','" & varLevel & "','" & varText & "', NULL, NULL, NULL, NULL, NULL)"
rsXMC.MoveNext
Loop
Main = DTSTaskExecResult_Success
So, if the data assigned to (say) varText contains an apostrophe (e.g. O'Malley), the script breaks down.
Is there any clever and quick way of getting around this?
July 2, 2008 at 11:41 am
Can you do a ...
replace(varText, '''', '''''')
July 3, 2008 at 3:15 am
Hi Ken,
Thank you for your reply......I need all the help I can get at the moment because ActiveX, ADO and vbscript are all new to me.........can you tell????? I'm learning though.....
OK, I get a syntax error when trying to use that in my script, so to rule out my own incompetence, I wrote a very simple script to test the syntax. This is what I wrote:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim VarText
varText="My name is Darren O'Malley."
MsgBox Replace(varText, '''', '''''')
Main = DTSTaskExecResult_Success
End Function
When I try to parse this, it fails with a compilation error on the line with the Replace function. This works fine though:
MsgBox Replace(varText, ".", "!")
and replaces the full stop with an exclamation mark, so I know the function and basic structure is OK, so it must be the find and replace with parameters that need tinkering with.
I'll search the Internet some more for this, but if you know why this is happening, that would be great.
Oh, and I've ordered a load of teach yourself ActiveX, ADO and vbscript books!
Cheers!
July 3, 2008 at 4:10 am
Sorry! Scrap my last post!
Replace(varText,"'", "''") works!!!!!
I cocked up the use of quote marks and apostrophes!
The full syntax needs to be (spaces added for clarification):
Replace(vartext, " ' ", " ' ' ")
or
Replace(varText, double-quote apostrophe double-quote, double-quote apostrophe apostrophe double-quote)
Duh!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply