?
Solved

LotusScript help - killing quotes

Posted on 2002-07-30
19
Medium Priority
?
810 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
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!

 
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

765 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