0

I am trying to insert a record into my SQL database that has a column value as follows: 

6" PVC vent 140' S, 4' W of NW crnr of bldg

I have tried putting single quotations around at the front and end of this value and I have tried putting double quotations at both the front and end of this value. Both results in an "Incorrect syntax" error. Both ends the value at 140' and starts the S, as another value. I really need to figure this out because I am seeing multiple values like this in the database unfortunately.

Here is my complete INSERT statement, the value above is the SMPL_PT_DESC column:

INSERT INTO CC_MSD_LIMS_STAGING (SMN, FACILITY_ID,SCHEDULE_DATE, IND_NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE, ZIP, SMPL_PT_DESC, SMPL_PT_NUM, SPLIT, TRUCK_ID, PROJECT_TYPE, SAMPLER, IND_ID, BOTTLE_TYPE, PRESERVATIVE, SAMPLE_TYPE, POLLUTANT, UNITS, SIU_IND, SURCHARGE_IND, SPECIAL_INSTRUCTIONS, AUTOSAMPLER_READY, SHIFT_L, SHIFT_START_TIME, PRIMARY_CONTACT_FIRSTNAME, PRIMARY_CONTACT_LASTNAME, PRIMARY_CONTACT_PHONE, ALTERNATE_CONTACT_FIRSTNAME, ALTERNATE_CONTACT_LASTNAME, ALTERNATE_CONTACT_PHONE, MONITORING_ID) 
VALUES ('1793175', '1037247400', '11/13/2017 12:00:00 AM', 'STONETRENDS LLC', '18092 Chesterfield Airport', '', 'Chesterfield', 'MO', '63005', '6" PVC vent 140' S, 4' W of NW crnr of bldg', '001', 'N', '493', 'IM', 'M', '2464', 'P0', 'None', 'T04', 'T208000', 'mg/L', '', 'Y', '', 'Y', '1', '0700AM', 'Patrick ', 'Martin', '6365371607', 'Stefan ', 'Landgraf', '6365371607', '-1')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Obie_One
  • 81
  • 10
  • 1
    You need to escape the single quote by replacing it by two single quotes: `... vent 140''S, 4''W of ...` Or even better use a parametrized query – Joe Nov 19 '17 at 19:49
  • Use backslash and single quote to escape double quotes and single quote respectively. `6\" PVC vent 140'' S, 4'' W of NW crnr of bldg` – Akash KC Nov 19 '17 at 19:49
  • Use a parameterized query with strongly-typed parameters. Not only will that avoid the need to escape quotes, it will handle dates and decimals properly and provide better security. – Dan Guzman Nov 19 '17 at 20:14
  • Are you just typing this command in a SQL client? – Matt Gibson Nov 19 '17 at 21:02
  • I got the same result. I used the code below to deal with the escaping: ~if (col.ColumnName.Equals("SMPL_PT_DESC")) { string smpl_pt = row[col].ToString().Replace("'", "''"); smpl_pt = smpl_pt.Replace('"', '\"'); cmdText.Append("" + row[col] + ""); cmdText.Append("', '"); }~ – Obie_One Nov 19 '17 at 21:52
  • please excuse my "code". I couldn't get my mini-Markdown correct. The code above produces the same value as what I originally posted. I need for the INSERT sql statement to be able to properly read this. Because as of now it stops at the **S,** – Obie_One Nov 19 '17 at 21:56
  • I wish I could change how the users input these descriptions but this is what I am dealing with. – Obie_One Nov 19 '17 at 21:57
  • @Obie_One So you're programming this using SqlClient's SqlCommand? In that case [*use a parameterised query*](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2). There's no need to roll your own escaping. – Matt Gibson Nov 19 '17 at 22:54
  • I'm generating the INSERT command in a C# application and trying to use a SQLCommand object to execute the command. – Obie_One Nov 19 '17 at 23:26

1 Answers1

0

You should write \" or "" ( if use @ ) instead of " for .net compiler and '' ( double ' ) instead of ' for sql server.

string sql = "INSERT INTO CC_MSD_LIMS_STAGING (SMN, FACILITY_ID,SCHEDULE_DATE, IND_NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE, ZIP, SMPL_PT_DESC, SMPL_PT_NUM, SPLIT, TRUCK_ID, PROJECT_TYPE, SAMPLER, IND_ID, BOTTLE_TYPE, PRESERVATIVE, SAMPLE_TYPE, POLLUTANT, UNITS, SIU_IND, SURCHARGE_IND, SPECIAL_INSTRUCTIONS, AUTOSAMPLER_READY, SHIFT_L, SHIFT_START_TIME, PRIMARY_CONTACT_FIRSTNAME, PRIMARY_CONTACT_LASTNAME, PRIMARY_CONTACT_PHONE, ALTERNATE_CONTACT_FIRSTNAME, ALTERNATE_CONTACT_LASTNAME, ALTERNATE_CONTACT_PHONE, MONITORING_ID) VALUES ('1793175', '1037247400', '11/13/2017 12:00:00 AM', 'STONETRENDS LLC', '18092 Chesterfield Airport', '', 'Chesterfield', 'MO', '63005', '6\" PVC vent 140'' S, 4'' W of NW crnr of bldg', '001', 'N', '493', 'IM', 'M', '2464', 'P0', 'None', 'T04', 'T208000', 'mg/L', '', 'Y', '', 'Y', '1', '0700AM', 'Patrick ', 'Martin', '6365371607', 'Stefan ', 'Landgraf', '6365371607', '-1')";

or with @

string sql = @"INSERT INTO CC_MSD_LIMS_STAGING (SMN, FACILITY_ID,SCHEDULE_DATE, IND_NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE, ZIP, SMPL_PT_DESC, SMPL_PT_NUM, SPLIT, TRUCK_ID, PROJECT_TYPE, SAMPLER, IND_ID, BOTTLE_TYPE, PRESERVATIVE, SAMPLE_TYPE, POLLUTANT, UNITS, SIU_IND, SURCHARGE_IND, SPECIAL_INSTRUCTIONS, AUTOSAMPLER_READY, SHIFT_L, SHIFT_START_TIME, PRIMARY_CONTACT_FIRSTNAME, PRIMARY_CONTACT_LASTNAME, PRIMARY_CONTACT_PHONE, ALTERNATE_CONTACT_FIRSTNAME, ALTERNATE_CONTACT_LASTNAME, ALTERNATE_CONTACT_PHONE, MONITORING_ID) 
VALUES ('1793175', '1037247400', '11/13/2017 12:00:00 AM', 'STONETRENDS LLC', '18092 Chesterfield Airport', '', 'Chesterfield', 'MO', '63005', '6"" PVC vent 140'' S, 4'' W of NW crnr of bldg', '001', 'N', '493', 'IM', 'M', '2464', 'P0', 'None', 'T04', 'T208000', 'mg/L', '', 'Y', '', 'Y', '1', '0700AM', 'Patrick ', 'Martin', '6365371607', 'Stefan ', 'Landgraf', '6365371607', '-1')";
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44