Script is failing because my text file has a string with a single quote as follows: O'Hearn. How can I handle this in the script? I have the following: string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','").Replace("''", " "); It is not working. I would like to insert the value of string with quote to sql table but if not possible, I can remove it. Thanks!
Assuming that the pipe character needs to be replaced with comma, and the single quote remained as a part of the value as is, the C# code should read something like this: string query = "Insert into dbo.[" + filenameonly + "] (" + columname + ") VALUES('" + line.Replace("|", ",").Replace("'", "''") + "')"; The idea is that every single quote needs to be replaced with two of them for T-SQL to be valid. Also, there is no need to add the single quotes around the pipe character in case if it needs to be replaced with just comma. The filenameonly variable is best if wrapped into brackets just in case if it has space in it or something which may render the table name to need brackets. It goes without saying that generating the SQL script like this is not safe as it becomes open to SQL injections. ***Edit*** It looks like I missed the reason why you have the pipe character replaced with **','**. This needs to happen because the line comes in as a pipe-delimited string. If this is the case then replacing the occurrences of the single quote with 2 such quotes needs to happen first. After that the replace of the pipe with **','** needs to take place. Here is the updated code: string query = "Insert into dbo.[" + filenameonly + "] (" + columname + ") VALUES('" + line.Replace("'", "''").Replace("|", "','") + "')"; If the original value of **line** is **1|Evette O'Hearn| 2038 Fairfax Rd||Toledo|OH|43613** then the value of the **query** becomes Insert into dbo.[Edit1] ([Uniseq] ,[Fullname] ,[Address1] ,[Address2] ,[City] ,[State] ,[Zip]) VALUES ('1','Evette O''Hearn',' 2038 Fairfax Rd','','Toledo','OH','43613') This is a valid insert which should work. It looks like some values might benefit from trimming but I am not sure whether this is needed or not. Hope this helps. Oleg