[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Get rid of the ' in a query

Posted on 2006-11-22
3
Medium Priority
?
205 Views
Last Modified: 2012-08-13
Hi im having a problem with importing data from access into SQL;

It does not like Mc'Smith or in other words '

using the following:

SqlCeConnection connection = new SqlCeConnection(ConnectionString);
                    connection.Open();

                    foreach (DataRow row in ds.Tables[0].Rows)
                    {

                        SqlCeCommand command = connection.CreateCommand();

                        //SqlCeCommand sqlCom = new SqlCeCommand();
                        string strtitle = row["Title"].ToString();
                        string strinitials = row["Initials"].ToString();
                        string strlastname = row["Surname"].ToString();
                        string straddress = row["Address 1"].ToString();
                        string straddress1 = row["Address 2"].ToString();
                        string strtown = row["Address 3"].ToString();
                        //string strcounty = row["county"].ToString();
                        string strpostcode = row["postcode"].ToString();
                        string strcountry = row["country"].ToString();
                        string stremail = row["Email"].ToString();
                        string strtel = row["phone number"].ToString();
                        //string strhdyhau = row["hdyhau"].ToString();
                        //string stripadd = row["ipadd"].ToString();
                        string strcate = "Explorers";
                        //string strdaterecieved = row["daterecieved"].ToString();
                        string strsubscribe = "No";
                        string strdelmethod = "By Post";
                       
                        string strimportfrom = "Source";
                        string strclientenabled = "Yes";
                        string strbrochureSubscibe = "Yes";



                        SqlCeParameter param = new SqlCeParameter();
                        param.ParameterName = "@MyParam";
                        param.Value = (DateTime)row["Reservation Date"];
                        command.Parameters.Add(param);

                        command.CommandText = @"INSERT INTO Eaddress (title, name, lastname, address, address1, town, postcode, country, emailad, tel, subscribeemail, cate, importeddate, EnquireDate, importedfrom, delmethod, clientEnabled, subscribebrochure) VALUES ('" + strtitle + "', '" + strinitials + "', '" + strlastname + "', '" + straddress + "', '" + straddress1 + "', '" + strtown + "', '" + strpostcode + "', '" + strcountry + "', '" + stremail + "', '" + strtel + "', '" + strsubscribe + "', '" + strcate + "', GETDATE(), @MyParam, '" + strimportfrom + "', '" + strdelmethod + "', '" + strclientenabled + "', '" + strbrochureSubscibe + "')";
                        command.ExecuteNonQuery();



                    }

can someone show me how to either ignore the ' , take it out or insert it (its the Surname Field i have the problem)

thanks
0
Comment
Question by:ziwez0
[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
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 17998819
for all of the fields that are string data, do like this:
 string strtitle = row["Title"].ToString().Replace("'", "''");

now, you seem to be copying the rows from 1 data table to another database, are these 2 different databases? doing a INSERT INTO ... SELECT ... FROM  should be faster ?!
if that is not possible, you still might look up a parametrized query, that will take the burden from the quotes from you.


0
 
LVL 2

Author Comment

by:ziwez0
ID: 17998910

yeah would be got some other stuff going on to, im getting in length error how do i say keep the first 10 digits then trim everything after

something like..

string strLastname = row["Surname"].ToString().TrimEnd(10);
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17998994
trimend is to trim away characters at the end of the string, and the parameter is to indicate which character...

you want to use this:
string strLastname = row["Surname"].ToString().Substring(0,10);
0

Featured Post

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!

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

656 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