at the moment I have the following code, for a local email system on my Intranet:
Code:
Set objComm = Server.CreateObject("ADODB.Command")

  objComm.ActiveConnection = strConnect
  objComm.CommandText="INSERT INTO mail ([sender], [recipient], [subject], [message], [locked]) VALUES ('" & user & "', '" & recipient & "', '" & subject & "', '" & message & "', '1')"

  objComm.Execute intNoOfRecords
  Set objComm = Nothing


However if a user includes a ' into their message they get an error message as the sql statement thinks it is moving on to fill the next feild.

How can I make the message variable submit regardless of any character that I put in to it (including ' )

Thankyou

I would do something like this:
Sub Whatever()
   user = StringCleaner([string to check])
   recipient = StringCleaner([string to check])
   ...etc
End Sub

Function StringCleaner(dirtyString)
  dirtyString = Replace(dirtyString, "'", "#39")
  dirtyString = Replace(dirtyString, ",", "#44")
  stringCleaner = dirtyString
End Function

What this does is replace ' and , with their ACSII value which, when displayed on an HTML page or in an HTML email, will produce the appropriate characters. This is also a good practice even if you weren't faced with this problem because it helps prevent SQL injection.

HTH

"The one language all programmers understand is profanity."