asked on
SQL Server escape single quote when coming from select statement
I have a Stored Procedure that inserts a new record into table A by pulling the data from Table B based upon an ID.
The address field from Table B, which needs to be inserted into Table A, may contain a Single Quotation tick mark.
Currently it causes an error. How do I deal with this?
My Code looks like this:
INSERT INTO TABLE A
(address1, address2)
SELECT address1, address2
FROM TABLE B
WHERE (id = @ID)
The address field from Table B, which needs to be inserted into Table A, may contain a Single Quotation tick mark.
Currently it causes an error. How do I deal with this?
My Code looks like this:
INSERT INTO TABLE A
(address1, address2)
SELECT address1, address2
FROM TABLE B
WHERE (id = @ID)
Microsoft SQL Server 2005
Last Comment
GenesisTech
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
ste5an:
Thank you for your response. It makes sense that I should not have to do anything special and that SQL Server would handle it correctly without me having to do a replace.
My ASP Page threw the error and, since it was the 1st time ever that the address had a single quote mark, I assumed it was the stored procedure that caused it.
When I executed it directly in SQL Server there was no error and it worked fine. That led me to look at the ASP page closer and find where the problem is.
Thank you!!!
Thank you for your response. It makes sense that I should not have to do anything special and that SQL Server would handle it correctly without me having to do a replace.
My ASP Page threw the error and, since it was the 1st time ever that the address had a single quote mark, I assumed it was the stored procedure that caused it.
When I executed it directly in SQL Server there was no error and it worked fine. That led me to look at the ASP page closer and find where the problem is.
Thank you!!!
(address1, address2)
SELECT address1, replace(address2, "'", "''")
FROM TABLE B
WHERE (id = @ID)