Get rid of the ' in a query

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
LVL 2
ziwez0Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
ziwez0Author Commented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.