Problem with INSERT INTO Sql with data that contains an apostrophe

[FireFett]

I have a project where all the tables are created and maintained dynamically in code using ADO.&nbsp;&nbsp;The problem comes in when a field contains a ' in it when performing an INSERT INTO statement. <br><br>Here is a psuedo sample of my code to give you an idea of what I am attempting.<br><br><br>Dim sSQL as String<br>Dim sTableName as String<br>Dim DBconn as ADODB.Connection<br>Dim rsInsert as ADODB.Recordset<br><br>Set DBConn = SomeString<br>Set rsInsert = New ADODB.Recordset<br><br>sTableName = &quot;SomeName&quot;<br><br>sSQL = &quot;INSERT INTO &quot; & sTableName & &quot;(Field1, Field2) Values ('&quot; & Text1.Text & &quot;', '&quot; & text2.Text & &quot;');&quot;<br><br>rsInsert.Open sSQL, dbConn, adOpenKeyset<br><br>I have tried to use a function that searched Text1 and Text 2 for the ' and replace them with the access method &quot;&quot;&quot; but vb returned a syntax error so I tried the function with &quot;chr(39)&quot; and still no luck.&nbsp;&nbsp;<br><br>Please help!!!&nbsp;&nbsp;<br>

 

[edderic]

With VB6.0 : Replace Function<br><br>('&quot; & Replace(Text1.Text,&quot; ' &quot;, &quot; ' ' &quot & &quot;'&quot;& Replace(text2.Text, &quot; ' &quot;, &quot; ' ' &quot & &quot;'&quot<br>Eric<br><br><br> <p>Eric De Decker<br><a href=mailto:vbg.be@vbgroup.nl>vbg.be@vbgroup.nl</a><br><a href=

http://communities.msn.be/VisualBasicForum>Dutch

Visual Basic Forum</a><br>

 

[MikeLacey]

to say that another way, the SQL command:<br><br>Insert into test_table values ('O''Mally');<br><br>will insert the string &quot;O'Mally&quot; into your database.<br><br>Mike<br> <p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[chiph]

FireFett -<br><br>This is a classic problem when using SQL inside another language.&nbsp;&nbsp;What you have to do is double-up the number of quotes in any string parameters passed to SQL.&nbsp;&nbsp;You can use the VB6 Replace function, or write your own parameter parsing routine, depending on what your requirements are.<br><br>Chip H.<br>