Solved

Sql Insert Query Help

Posted on 2011-03-06
6
238 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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now