asp and a sql insert statement problem 1

[mitchb]

Hi. I'm having a problem with some asp/sql/crystal. I am using an asp front end to enter data into a sql db from which I extract the data with Crystal. My problem lies in when a user enters data into a comment field, and the data contains an apostrophe it crashes the insert statement. My asp code consists of

	conn.execute "insert into product_detail (quote_num, line_num, product_num" _
	& ", description, qty_oh, um, qty_quoted, cost, price, line_comment) values (" _
	& session("quotenum") & ", " _
	& session("linenum") & ", " _
	& "'" &  pn & "', " _
	& "'" &  desc & "', " _
	& qty_oh & ", " _
	& "'" &  um & "', " _
	& qty_quoted & ", " _
	& cost & ", " _
	& price & ", " _
	& "'" &  comment & "'" _
	& ")"

I can get rid of the error by using escape(comment), but then the data stored in sql contains all the escape characters. Then when I retrieve the data with Crystal the report shows all the escape characters.
Any suggestions on how to insert the data into sql without the data itself effecting the asp code?

 

[JamesLean]

Use the Replace function:

strSQL = "INSERT table1 (col1) " &_
  "VALUES ('" & Replace(Session("StringVar"), "'", "''") & "')"

--James

 

[mitchb]

Thanks James! That worked perfectly!