Solved

Sql Insert Query Help

Posted on 2011-03-06
6
241 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Sha1395
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
crysallus earned 500 total points
ID: 35049416
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
 

Author Comment

by:Sha1395
ID: 35049574
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
 
LVL 8

Expert Comment

by:crysallus
ID: 35049735
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
ID: 35049937
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
 

Author Comment

by:Sha1395
ID: 35050054
Hi samirbhogayta

Correct me if am wrong ,is that Trim will cut only the space not the single quotes right ?
0
 

Author Closing Comment

by:Sha1395
ID: 35050075
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question