• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 848
  • Last Modified:

LotusScript help - killing quotes

I'm hoping this is a very simple question ...

I've made an agent that connects to an SQL Server via ODBC. I am iterating through the documents in a view and inserting important data into an SQL Server table.

Now, logically, problems arise if a field contains an SQL string delimiter character, namely, the single quote --> ' <--

I made a function to replace all occurrences of ' in a string with '' (i.e. two single quotes) and thought I was really clever ...

Function KillQuotes(srcStr As String) As String
  KillQuotes = Evaluate (|@ReplaceSubString(| & srcStr & |;"'";"''")|)
End Function

... but it doesn't work ! I just get a type mismatch error. I don't really understand where the error is.

I am using Lotus Notes 4.6.2.  Please help me get the KillQuotes function working correctly!
0
davidmwilliams
Asked:
davidmwilliams
  • 6
  • 5
  • 5
  • +1
1 Solution
 
Jean Marie GeeraertsCommented:
Evaluate always returns a Variant type so you need to declare your function as a Variant.
0
 
davidmwilliamsAuthor Commented:
Ahh, interesting ... presumably the result has to then be cast to a string?

Can you give a code sample, just so I am clear?

Many thanks!
0
 
Jean Marie GeeraertsCommented:
If you check out the example from online help, you will notice that you need to reference the returned value by passing an index.

In your code this would look something like this :

Function KillQuotes(srcStr As String) As String
 dim Result as Variant
 Result = Evaluate (|@ReplaceSubString(| & srcStr & |;"'";"''")|)
 KillQuotes=Cstr(Result(0))
End Function

This way the function still returns a string, but inside the function you reference the first value returned to the Variant (=index 0) to assign to the function.

Try it and let me know if it works (I'm using a R5 client, so there might be some differences)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
davidmwilliamsAuthor Commented:
Fantastic, I'll try that out back in the office (I'm on Australian time :) and get back to you within 12 hours.
0
 
Jean Marie GeeraertsCommented:
Okay, I'm in Belgium, so I'll probably read about it tomorrow morning :-)
0
 
davidmwilliamsAuthor Commented:
Alas, this fails with an unhelpful "operation failed" in error$ :(
0
 
Jean Marie GeeraertsCommented:
Can you post your complete script here?
Including Initialize, Declarations and stuff so I can see where and how you call the function.
0
 
ArunkumarCommented:
Willi !

Do me a favor,  Try replacing the Bars for Evaluate statements with { and }  and the double quotes with Bars.

Just a try... I have played with these characters and have achieved some appreciatable results.

Good Luck!
Or
Just send us the full code.

-Arun
0
 
zvonkoCommented:
Hello David,

take this:
Function KillQuotes(srcStr As String) As String
  Dim p As Integer
  p = Instr(1,srcStr, "'")
  Do While (p > 0)
    srcStr = Left$(srcStr, p) & "'" & Mid$(srcStr, p+1)
    p = Instr(p + 2,srcStr, "'")
  Loop
  KillQuotes = srcStr
End Function

Good luck,
zvonko

0
 
ArunkumarCommented:
Zvonko !
Why to write a huge script when it could be acheived by a one liner ???
*jus curious*

;-P
Arun.
0
 
zvonkoCommented:
Because JM's one-liner is not working :)

Where is yours?

Or should I show mine <|:)

0
 
Jean Marie GeeraertsCommented:
The reason for the operation failed is simple:
When passing a quote to an @Formula, you need to put a \ in front of it, that's why the evaluate won't work.

It's better to do it with a script function like the one zvonko suggests.
0
 
zvonkoCommented:
Oh how polite we all are today :)

So here JM's corrected line to get the points:
Function KillQuotes(srcStr As String) As String
     Dim Result As Variant
     Result = Evaluate (|@ReplaceSubString("| & srcStr & |";"'";"''")|)
     KillQuotes=Cstr(Result(0))
End Function

David, points go to: jerrith! <|:-)

(not to Arun!!! :)

0
 
ArunkumarCommented:
oh come on !  Dont be ridiculous !
0
 
Jean Marie GeeraertsCommented:
I tried that zvonko with the extra quotes to go around the passed string (I overlooked that) but it didn't work, either.
Have you tested this?
0
 
ArunkumarCommented:
You know what I dont want to test it... Cause i know the oneliner will work for sure 100% !

;-)
0
 
zvonkoCommented:
Of course JM, I have learned my lesson :)

The ridiculous difference between assuming something and testing something is between it works or it does not work.

I test my assumptions to see it work <|:-)

JM, I assume your problem is at function invocation time, not function execution.
So try this extension:
Function KillQuotes(Byval srcStr As String) As String
     Dim Result As Variant
     Result = Evaluate (|@ReplaceSubString("| & srcStr & |";"'";"''")|)
     KillQuotes=Cstr(Result(0))
End Function

0
 
ArunkumarCommented:
LOL !
0
 
zvonkoCommented:
Thanks David :-)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now