Single/Double Quote Syntax Trouble

[philwes83170]

I have a script that writes values to a SQL Database. Unfortunately some of the data contains single quotes in the name (ie. Mom and Pop's Food). I can't seem to get the right combination of quotes to to get this to work. Here is the code that works great for a simple name (ie. Northern Food). Just need to figure out how to make it work if the value contains a single quote. Thanks in advance for any help you can offer.

iReturn = slapi_DBExecuteSQL("UPDATE ACCOUNT SET ACCOUNT = '" & strAccount & "' WHERE ACCOUNTID = '" & strAccountID & "'")

The variable strAccount is the one that is giving me trouble.

 

[DotNetGnat]

iReturn = slapi_DBExecuteSQL("UPDATE ACCOUNT SET ACCOUNT = '" & Replace(strAccount,"'","''") & "' WHERE ACCOUNTID = '" & strAccountID & "'")

-DNG

 

[philwes83170]

Thanks, but I'm getting a syntax error still. The script thinks Replace is a sub-routine or function.

How about something like this....

ACCOUNT = 'Replace(" & strAccount & ","'","''")' WHERE

I know this isn't it specifically, I'm still getting an error with this.

 

[DotNetGnat]

oops i am sorry...

do this...
before the sql query...

strAccount=Replace(strAccount,"'","''")

then you can have the query same as previous...

iReturn = slapi_DBExecuteSQL("UPDATE ACCOUNT SET ACCOUNT = '" & strAccount & "' WHERE ACCOUNTID = '" & strAccountID & "'")

-DNG

 

[philwes83170]

We're still missing something. strAccount is a variable in the VB Script. The SQL Statement is being built on the fly. The SQL Statement actually looks like this when it runs:

UPDATE ACCOUNT SET ACCOUNT = 'Northern Food' WHERE ACCOUNTID = 'AB45GH6'

The problem I have is that variable sometimes contains and single quote and looks like this.

UPDATE ACCOUNT SET ACCOUNT = 'Joe's Food Shop' WHERE ACCOUNTID = 'AB45GH6'

That extra quote of course crashes the statement. I know I'm on the right track with the REPLACE command just can't get the syntax right in the statement.

 

[DotNetGnat]

you need to apply the replace() function to your variable before passing to your sql query.

-DNG

 

[philwes83170]

How about looking at this from another direction...
How can I make this statement work?

SELECT * FROM Account
Where Account = 'Test Account's'

Tried putting it in brackets and enclosing it in double quotes and it thinks its a field name and not a value.

 

[zhavic]

Every character ' inside a string needs to be replaceted with two characters '', as DotNetGnat shows you

SELECT * FROM Account
Where Account = 'Test Account''s'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[JamesLean]

The original query is correct. Try this:

sSQL = "UPDATE ACCOUNT SET ACCOUNT = '" & Replace(strAccount, "'", "''") & "' WHERE ACCOUNTID = '" & strAccountID & "'"

iReturn = slapi_DBExecuteSQL(sSQL)

--James

 

[philwes83170]

Still getting an error in the vb script that says REPLACE is an undefined Sub or Function. What is the VB Script equivalent to Replace?

 

[JamesLean]

Replace is a VBScript function:

http://www.devguru.com/Technologies/vbscript/quickref/replace.html

--James

 

[zhavic]

Or this

http://msdn.microsoft.com/library/d.../html/frlrfsystemstringclassreplacetopic2.asp

so

sSQL = "UPDATE ACCOUNT SET ACCOUNT = '" & strAccount.Replace("'", "''") & "' WHERE ACCOUNTID = '" & strAccountID & "'"


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[philwes83170]

Thanks for all your help. I suspect I'm having problems with the software that is using the scripts and not the scripts themselves. I've contacted the software vendor for further asssitance, thanks.