Single Quote error.

[mtl77]

Hi All,

I am having problems with the following insert statement when the values that are inserted contain single quotes. I have been trying to use the verbatim string ( @ in c#) but i have not been able to fiqure it out. Any ideas or suggestions?

"INSERT INTO Details Values('" + compId + "','" + compName + "','" + todaysDate + "','" + homeDesk + "','" + operSys + "','" + twoComp + "','" + accNetDesk + "','" + accNetLap + "','" + accNetRsn + "','" + intCon + "','" + ispName + "','" + deskAntiVir + "','" + validAntLic + "','" + upgdSoft + "','" + updgDef + "','" + antivirName + "','" + fireWall + "','" + fireWallKnow + "','" + smpt + "','" + pop + "','" + pptp + "','" + ftp + "','" + http + "','" + phyFire + "','" + phyFireName + "','" + accNetOther + "','" + achoDesc + "','" + accClientHD + "','" + achdDesc + "','" + accClientHL + "','" + achlDesc + "')";

thanks,
kf

 

[JamesLean]

You need to double up all the single quotes in any values that you concatenate into the SQL string eg, in VB you can use the Replace() function.

So this string:

here's a single quote

Becomes:

here''s a single quote

SQL Server interprets that as an embedded quote within the string value and will insert the correct string.

--James

 

[clarkin]

since its so complicated you are probably better off doing an addnew and an update on a recordset.

something like (in ASP):

..
xrs.open "SELECT 'nothing' FROM tableName", connObj, 1, 2

xrs.addnew 'add new row to table

'set values
xrs("tableFieldName") = escapeSQL(compID) 
'and so on

xrs.update
xrs.close

escapeSQL replaces ' with '' as JamesLean mentioned

good luck

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].