I'm trying to use DMX's built-in Check User Name SB to prevent duplicates of a certain field being inserted into my db. I've modified it to be used to check for a web album's name instead of the default username check.

My problem now is that if you enter any text into that field including a single quote (e.g. album's name), I end up with an error:

<pre id=code><font face=courier size=2 id=code>Syntax error (missing operator) in query expression 'Name='album's name''.
</font id=code></pre id=code>

I've seen the following line of code insterted via DMX's Insert Record SB:

<pre id=code><font face=courier size=2 id=code> MM_formVal = "'" & Replace(MM_formVal,"'","''" & "'"</font id=code></pre id=code>

and was wondering whether this could be adapted into the Check User Name SB?

Any suggestions would be much appreciated.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I'm trying to use DMX's built-in Check User Name SB to prevent duplicates of a certain field being inserted into my db. I've modified it to be used to check for a web album's name instead of the default username check.

My problem now is that if you enter any text into that field including a single quote (e.g. album's name), I end up with an error:

<pre id=code><font face=courier size=2 id=code>Syntax error (missing operator) in query expression 'Name='album's name''.
</font id=code></pre id=code>

I've seen the following line of code insterted via DMX's Insert Record SB:

<pre id=code><font face=courier size=2 id=code> MM_formVal = "'" & Replace(MM_formVal,"'","''" & "'"</font id=code></pre id=code>

and was wondering whether this could be adapted into the Check User Name SB?

Any suggestions would be much appreciated.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

OK - I've solved this one on my own.

To fix the single quote bug in the DMX Check User Name SB, you need to add a new function in to it and tweak one line of code. I've listed the changes below:

1) Open your page with the Check User Name SB in it in code view.
2) Locate the comment
<pre id=code><font face=courier size=2 id=code>' *** Redirect if username exists </font id=code></pre id=code>
3) Just below this add the following function
<pre id=code><font face=courier size=2 id=code>Function sqlEncode(sqlValue)
sqlEncode = replace(sqlValue, "'", "''"
End function </font id=code></pre id=code>
4) Now locate this line of code
<pre id=code><font face=courier size=2 id=code>MM_dupKeySQL="SELECT Username FROM Logins WHERE Username='" & MM_dupKeyUsernameValue & "'" </font id=code></pre id=code>
and replace <pre id=code><font face=courier size=2 id=code>MM_dupKeyUsernameValue </font id=code></pre id=code> with <pre id=code><font face=courier size=2 id=code>sqlEncode(MM_dupKeyUsernameValue) </font id=code></pre id=code>
5) Save the file and you're done.

I've actually made the same fix to the Check New Element extension by Felice Di Stefano found on the Felixone.it site and sent him the fix as well.

Hopefully he will include this into his next version of the extension so that it may be used with any field value check - including single quotes.