Solved

Sql Insert Query Help

Posted on 2011-03-06
6
243 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
Industry Leaders: 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

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!

Question has a verified solution.

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

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

724 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