I have a Microsoft Access database that holds information about grants available to our clients. That database is connected to a set of ColdFusion web pages where our staff can add new grants, update existing grants and delete grants. There is also the option to export a set of grants search results to an Excel spreadsheet.
Recently I noticed that the formatting on the Excel spreadsheet was thrown all off because certain records in the database contained special characters, most notably line returns. These special characters were a result of staff copying/pasting grant information into the respective form fields on the web page.
How do I strip out line returns, carriage returns etc. without stripping out necessary spacing before the data gets posted to the database? I've done some research and found that using the ColdFusion REReplace function or the Replace function may work, but I'm not sure where to add that code. Would I add it to my SQL INSERT and UPDATE statements?
Here is my INSERT statement:
<cfquery name="qAdd" datasource="#datasource#">
INSERT INTO Grants (Funder, Summary, Deadline, Awards, Website, Eligibility)