Sql Insert Query Help

hello guru

i have a query in my code like below

 cmd.CommandText = "INSERT PersonTest (EmployeeNo,NamePrefix,FirstName, MiddleName, LastName, NameSuffix,HighestEducationLevel, BirthDate, Gender ) VALUES("
                                                   +item.EmployeeNumber + ","
                                                     + "'" + item.Title + "'" + ","
                                                   + "'" + item.PreferredName + "'" + ","
                                                   + "'" + item.MiddleName + "'" + ","
                                                   + "'" + item.Surname + "'" + ","
                                                   + "'" + item.Suffix + "'" + ","
                                                   + "'" + item.HighestEducationLevel + "'" + ","
                                                    + "'" + item.BirthDate + "'" + ","
                                                    + "'" + item.Sex + "'" + ")";

Open in new window


Right now am facing a problem when the middle name comes like D'Smith

here is the result query

INSERT PersonTest (EmployeeNo,NamePrefix,FirstName, MiddleName, LastName, NameSuffix,HighestEducationLevel, BirthDate, Gender ) VALUES(328,'Mr','Julian',' D'Smith','Pinder','','','21/08/1978 12:00:00 AM','M')

how can i pass the middle name with single qoutes to DB and Date Field its not accept time,so i used char to take as a constant.

Please give me some idea about where i have to concentrate in my query
Sha1395Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crysallusCommented:
I haven't tested this, but I believe you do that by escaping the single quote with another single quote. In other words, put in 2 single quote characters where you want there to be 1 in the database.

You could probably do this by using hte string Replace method to replace all instances of a single quote, with 2 of them:

 cmd.CommandText = "INSERT PersonTest (EmployeeNo,NamePrefix,FirstName, MiddleName, LastName, NameSuffix,HighestEducationLevel, BirthDate, Gender ) VALUES("
                                                   +item.EmployeeNumber + ","
                                                     + "'" + item.Title + "'" + ","
                                                   + "'" + item.PreferredName + "'" + ","
                                                   + "'" + item.MiddleName.Replace("'", "''") + "'" + ","
                                                   + "'" + item.Surname + "'" + ","
                                                   + "'" + item.Suffix + "'" + ","
                                                   + "'" + item.HighestEducationLevel + "'" + ","
                                                    + "'" + item.BirthDate + "'" + ","
                                                    + "'" + item.Sex + "'" + ")";

Open in new window

and so on for any of other attributes that may contain a single quote character.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sha1395Author Commented:
Hey Crysallus ,

I was thinking to do the same thing what you suggest here but is that a best way do at the front end or i can write SP and handle in the back end.

Becoz not all the middle names are comes with single quotes.
0
crysallusCommented:
I'm not enough of a SQL guru to advise you on which is better. Perhaps this article may be useful though:

http://www.developerfusion.com/article/4708/to-sp-or-not-to-sp-in-sql-server/
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
Hi, At the time of insert query you have to write like this.
Before inserting record txtMiddleName.Text.Trim();. Then after you have to use the

item.Middlename
0
Sha1395Author Commented:
Hi samirbhogayta

Correct me if am wrong ,is that Trim will cut only the space not the single quotes right ?
0
Sha1395Author Commented:
I am looking for a solution from backend but anyway Crysallus always helps me with immediate response.So am happy to accept his solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.