Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql Insert Query Help

Posted on 2011-03-06
6
Medium Priority
?
247 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
  • 3
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
crysallus earned 2000 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
Technology Partners: 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 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