GenesisTech
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)