Binaryworld - Dealing with Single Quote (') problem in SQL Server while inserting da ... [ VB.net -> ADO.net ]

I dont know how many times I had to face single quote problem during my day to day programming task. When you pass any character or varchar datatype to sql server you have to be very careful if your data contains single quotes. You have to replace (') with ('') so sql server can understand it properly. but this is not the best way everytime coz when you insert data then u have to convery it with ('') and when you read it back then again you have to replace ('') with (') And finally I found the most efficient way to deal with this type of problem is use Command/Parameters to execute your query or stored procedure.

e.g the following statement might fail if someone enter codename with single quote in it.

Click here to copy the following block

cmd.CommandText = "select count(*) from codemaster where code ='" txtCode.text "'"
ret=cmd.ExecuteScaler()

but if you use the following statement then it will execute statement without any error

Click here to copy the following block

cmd.CommandText = "select count(*) from codemaster where code=@codename"
cmd.Parameters.Add("@codename", txtCodeName.Text)
ret=cmd.ExecuteScaler()