SQL constraint rule probelm

Hi there,
I'm having a hard time automatically editing some users to my SQL DB.
I established it is because the surname of the people I am adding
contains an " ' ", eg surname = O'Connel
What I am sending SQL looks like:
sQLUpdateString = "UPDATE Users SET " & updateString & _
" WHERE (UserName='" & userName & "')"
conWrite.Execute(sQLUpdateString)

My script generates updateString automatically and in the case where
it updates the users full name;
updateString = FullName='John O'Connel'
In this case userName = "JohnO"
This normally works fine, (I can update all the other users in my DB
this way), however in cases like this where the surname includes the "
' " the full name in read in as being "John O" then an error because
the "Connel" part still remains and is invalid SQL. I am told that I
should probably use a rule or a constraint to fix this, however SQL is
not really my strong suit, so any pointers on how to go about this
would be greatly appreciated.
Thanks in advance

Michael
Sun Jun 20 18:23:08 CDT 2004

In item news:eaef08f.0406201504.615df2aa@posting.google.com,
Chris says...

> Hi there,
> I'm having a hard time automatically editing some users to my SQL DB.
> I established it is because the surname of the people I am adding
> contains an " ' ", eg surname = O'Connel
> What I am sending SQL looks like:
> sQLUpdateString = "UPDATE Users SET " & updateString & _
> " WHERE (UserName='" & userName & "')"
> conWrite.Execute(sQLUpdateString)
>
> My script generates updateString automatically and in the case where
> it updates the users full name;
> updateString = FullName='John O'Connel'
> In this case userName = "JohnO"
> This normally works fine, (I can update all the other users in my DB
> this way), however in cases like this where the surname includes the "
> ' " the full name in read in as being "John O" then an error because
> the "Connel" part still remains and is invalid SQL. I am told that I
> should probably use a rule or a constraint to fix this, however SQL is
> not really my strong suit, so any pointers on how to go about this
> would be greatly appreciated.
> Thanks in advance

From SQL books online.
Delimited Identifiers topic under Accessing and Changing Relational Data.

<snip>
If the character string contains an embedded single quotation mark, insert
an additional single quotation mark in front of the embedded mark:
SELECT * FROM "My Table"
WHERE "Last Name" = 'O''Brien'
<snip>
As you can see you will need to insert an additional single quote to escape
the single quote so the O'Connel will need to be O''Connel (That is 2 single
quotes not a double quote)

-- Regards,

Michael Holzemer
No email replies please - reply in newsgroup
Learn script faster by searching here
http://www.microsoft.com/technet/community/scriptcenter/default.mspx

Viatcheslav
Mon Jun 21 03:54:13 CDT 2004

Another solution is to use command with parameters:

UPDATE Users SET some_column = ? WHERE UserName = ?

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE

"Chris" <c_mcbryde@hotmail.com> ???????/???????? ? ???????? ?????????:
news:eaef08f.0406201504.615df2aa@posting.google.com...
> Hi there,
> I'm having a hard time automatically editing some users to my SQL DB.
> I established it is because the surname of the people I am adding
> contains an " ' ", eg surname = O'Connel
> What I am sending SQL looks like:
> sQLUpdateString = "UPDATE Users SET " & updateString & _
> " WHERE (UserName='" & userName & "')"
> conWrite.Execute(sQLUpdateString)
>
> My script generates updateString automatically and in the case where
> it updates the users full name;
> updateString = FullName='John O'Connel'
> In this case userName = "JohnO"
> This normally works fine, (I can update all the other users in my DB
> this way), however in cases like this where the surname includes the "
> ' " the full name in read in as being "John O" then an error because
> the "Connel" part still remains and is invalid SQL. I am told that I
> should probably use a rule or a constraint to fix this, however SQL is
> not really my strong suit, so any pointers on how to go about this
> would be greatly appreciated.
> Thanks in advance