Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get rid of the ' in a query

Posted on 2006-11-22
3
Medium Priority
?
207 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
  • 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
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.…
Suggested Courses
Course of the Month20 days, 19 hours left to enroll

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