Excel into Access using ASP with DWMX

[Jim]

Hi
Anyone know an ASP script for transferring data from an Excel sheet to an
Access table, some of the fields of which contain names which have an
apostrophe (') in them.
I have tried all sorts of things but am getting tripped up by the apostrophe
which is used as a field container in the SQL :

"INSERT INTO tblResults Values(0,'" & rsExcel("FeisID") & "' , '" &
rsExcel("CompNo") & "' , '" & rsExcel("Entry") & "' , '" & rsExcel("Name") &
"' , '" & rsExcel("School") & "' , '" & rsExcel("Position") & "' , '0' , '"
& Done & "')"

The first field is the ID field of tblResults and need to be incremented
automatically. If I leave the first value out, I get an error along the
lines of "Number of query values and destination fields are not the same"
Also, if the name field is like Harry O'Hare, the thing fails...
Any ideas anyone? Many thanks

Jim

[webshorts]

Hi Jim, You'll need to do a replace on the fields to get rid of the single
quotes. The best way is to first build yourself a function, like so: <%
function cleanString(myStr) cleanString = Replace(myStr, ''', '''') end
function %> Then, wrap that around each of your values: INSERT INTO
tblResults VALUES (0, '' &amp; cleanString(rsExcel('FeisID') &amp; '', ...
That will allow you to insert apostrophes into the database by escaping them
with double apostrophes. Hope that helps.