Syntax error - can you help?

<i><b>Originally posted by : steu (steu.s.mann@aexp.com)</b></i><br />I would appreciate any help you send to resolve this error message.... <br /><br />There is a notes field on a user form that is then sent to preview.asp. In that field when the user types in a ' or " character the following error is displayed:<br /><br />*****************<br />Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br />[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'. <br />/formstoday/preview.asp, line 182 <br /><br />***************<br /><br />Line 182 is thus:<br />objConn.execute sql<br /><br />If they do no use the ' or " cgaracter the form works fine and the data is sent from preview.asp to the database. The column in the database for this filed is a property of 'text'. When it was a property of 'varchar' I didn't get this erorr - but I need a field that will hold a large amount of data - hence the property of 'text'.<br /><br />Any ideas on how to resolve this?<br /><br />Thanks in advance!<br />

<i><b>Originally posted by : jeff</b></i><br /><br />Try this:<br /><br />Dim myStr ' the value from the form field<br /><br />myString = Replace(myString,"'","''")<br /><br />This will replace the single quote with two single quotes. The problem lies in the SQL code. Strings in SQL code are contained by single quotes. If you have a single quote in the string itself, then the SQL parser sees it as a syntax error. To solve this replace all single quotes with two single quotes. ie:<br /><br />SQL = "Insert into Table (Field) Values('My mom's cookies');"<br /><br />This will break at the single quote in "mom's" - To fix it replace the single quote with a double quote:<br /><br />SQL = "Insert into Table (Field) Values('My mom''s cookies');"<br /><br />This won't break. The parser interprets two single quotes as a one single quote in the string. The same is true for double quotes.<br /><br />Hope this helps.<br /><br />Jeff<br /><br /><br />------------<br />steu at 5/9/2000 4:19:48 PM<br /><br />I would appreciate any help you send to resolve this error message.... <br /><br />There is a notes field on a user form that is then sent to preview.asp. In that field when the user types in a ' or " character the following error is displayed:<br /><br />*****************<br />Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br />[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'. <br />/formstoday/preview.asp, line 182 <br /><br />***************<br /><br />Line 182 is thus:<br />objConn.execute sql<br /><br />If they do no use the ' or " cgaracter the form works fine and the data is sent from preview.asp to the database. The column in the database for this filed is a property of 'text'. When it was a property of 'varchar' I didn't get this erorr - but I need a field that will hold a large amount of data - hence the property of 'text'.<br /><br />Any ideas on how to resolve this?<br /><br />Thanks in advance!<br />