asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ziwez0

ASKER

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);

Guy Hengel [angelIII / a3]

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);