[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

LotusScript help - killing quotes

Posted on 2002-07-30
19
Medium Priority
?
825 Views
Last Modified: 2013-12-18
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
Comment
Question by:davidmwilliams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +1
19 Comments
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7187380
Evaluate always returns a Variant type so you need to declare your function as a Variant.
0
 
LVL 4

Author Comment

by:davidmwilliams
ID: 7187702
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
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7187710
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Author Comment

by:davidmwilliams
ID: 7187719
Fantastic, I'll try that out back in the office (I'm on Australian time :) and get back to you within 12 hours.
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7187723
Okay, I'm in Belgium, so I'll probably read about it tomorrow morning :-)
0
 
LVL 4

Author Comment

by:davidmwilliams
ID: 7189315
Alas, this fails with an unhelpful "operation failed" in error$ :(
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7190068
Can you post your complete script here?
Including Initialize, Declarations and stuff so I can see where and how you call the function.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7193070
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7193481
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
 
LVL 9

Expert Comment

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

;-P
Arun.
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7194612
Because JM's one-liner is not working :)

Where is yours?

Or should I show mine <|:)

0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7195717
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7195889
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7195909
oh come on !  Dont be ridiculous !
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7195915
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7196027
You know what I dont want to test it... Cause i know the oneliner will work for sure 100% !

;-)
0
 
LVL 10

Accepted Solution

by:
zvonko earned 400 total points
ID: 7196115
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7196695
LOL !
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7327130
Thanks David :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question