getting error on while inserting a single quotation values

Question

Using SQL Server 2000 and VB6

Table1

EmpName1 

Raja
Rav'i
Ramu'i
Rajes'ih

Table2

EmpName2

....

When I inserting table2.empname2 from table1.empname1, It inserted only raja, remaining rows was not inserted it showing error in '(single quotation)

"Showing error as incorrect syntax near rav"

vb6 code.

INSERT INTO table2 (EmpName) VALUES('" & EmpName & "')

How can i insert name with single quotation also.

Need query and code help.

Answer 1

You can either escape your single quotation with something like

empName = replace(empName, "'", "''")

or paramertize your query...

Answer 2

It is highly recommended to use a parametrized query (see e.g. this article), instead of building your SQL query as a string. This protects you from many forms of SQL injection, and is also often faster.

Or directly use some kind of persistence framework, if you have a lot of DB interaction.

Your query will not work because in string SQL queries, the ' must be escaped (as ''). And BTW, if you want to insert values from one table into another, it is usually not a good idea to do a SELECT, then an INSERT in a loop, like you do. Just do a

INSERT INTO table2 SELECT ... FROM table1