ADO SQL Server: Apostrophe's in Text

alsol

Hi,

I came across a problem when inserting new records with Apostrophe's to a SQL Server database from NAV using ADO. Below is the code we are using:

OpenConnection();
AdoSqlString := 'INSERT INTO Logistics ' +
' (No, ShipName, ShipName2, ShipContact, ShipAddress, ShipAddress2, ShipCity, ShipPostCode, ' +
' ShipCounty, ShipCountry) ' +
'VALUES ' +
' (''%1'', ''%2'', ''%3'', ''%4'', ''%5'', ''%6'', ''%7'', ''%8'', ''%9'', ''%10'')';
AdoSqlString := STRSUBSTNO(AdoSqlString,HandlingUnit."No.",HandlingUnit."Ship-to Name",HandlingUnit."Ship-to Name 2",
HandlingUnit."Ship-to Contact",HandlingUnit."Ship-to Address",HandlingUnit."Ship-to Address 2",
HandlingUnit."Ship-to City",HandlingUnit."Ship-to Post Code",HandlingUnit."Ship-to County",
HandlingUnit."Ship-to Country Code");
ExecuteStatement(FALSE);
CloseConnection();

That works perfect until there are apostrophe's in some of the variables. Any ideas how I can solve this?

kriki

Try to create a stored procedure that accepts parameters and that does the insert (after all it is also safer against SQL injection attacks).
See here: http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx for more info.