Single Quotes error when inserting text in database fields

Posted on 2006-05-02
Last Modified: 2010-04-17
I am working in a C# Windows Application using SQL Server 2000. When some body type a single quote in the text fields the data base throws an error. Can any body give me sugestions on how to handle errors with songle quotes such as :


Thank you !!

Alejandro Acevedo
Question by:cyborgcom
    LVL 142

    Accepted Solution

    you have to duplicate the single quotes in the code (if you build the query dynamically).
    if you used parametrized queries, you would not have that problem.
    LVL 7

    Expert Comment

    Our you could just remove them (use replace function). I have similar issues in my business. The problem with apostrophes is that nobody bothers to enter them into search fields so you need to replace them when you hit the database anyway.

    Author Comment

    I try the following and still having problems with single quotes.

    SqlCommand cmd = new SqlCommand ();                  
                      cmd.Connection = this.conCAGDB ;

                            #region Insert Order Record

                            int OrderType;
                            int CallBack = 0;
                            if (this.chkDSLOnly .Checked)
                                  OrderType = 130;
                                  OrderType = objOrder.OrderType;

                            cmd.CommandText =                   
                                  "INSERT INTO Orders "+
                                  "(OrderNumber, "+
                                  "OrderDate, "+
                                  "CustomerID, "+
                                  "OrderType, "+
                                  "EmployeeID, "+
                                  "OrderStatus, "+                                          
                                  "CallBack, "+
                                  "Remarks, DSL) "+
                                  "VALUES (@OrderNumber, "+
                                  "@OrderDate, "+
                                  "@CustomerID, "+
                                  "@OrderType, "+
                                  "@EmployeeID, "+
                                  "@OrderStatus, "+
                                  CallBack+ ", "+      //Call Back?
                                  "@Remarks, "+
                                  System.Convert.ToInt32 (this.chkDSLSelected .Checked )+")";

                            SqlParameter prmOrderNumber = new SqlParameter();
                            prmOrderNumber.ParameterName = "@OrderNumber";
                            prmOrderNumber.Value = objOrder.OrderNumber;

                            SqlParameter prmOrderDate = new SqlParameter();
                            prmOrderDate.ParameterName = "@OrderDate";
                            prmOrderDate.Value = System.Convert .ToDateTime (objOrder.OrderDate);

                            SqlParameter prmCustomerID = new SqlParameter();
                            prmCustomerID.ParameterName = "@CustomerID";
                            prmCustomerID.Value = objOrder.CustomerID ;      

                            SqlParameter prmOrderType = new SqlParameter();
                            prmOrderType.ParameterName = "@OrderType";
                            prmOrderType.Value = OrderType ;      
                            SqlParameter prmEmployeeID = new SqlParameter();
                            prmEmployeeID.ParameterName = "@EmployeeID";
                            prmEmployeeID.Value = objOrder.EmployeeID ;                              

                            SqlParameter prmOrderStatus = new SqlParameter();
                            prmOrderStatus.ParameterName = "@OrderStatus";
                            prmOrderStatus.Value = objOrder.OrderStatus ;            

                            SqlParameter prmRemarks = new SqlParameter();
                            prmRemarks.ParameterName = "@Remarks";
                            prmRemarks.Value = this.txtRemarks .Text ;
                            cmd.Parameters .Add (prmOrderNumber);
                            cmd.Parameters .Add (prmOrderDate);
                            cmd.Parameters .Add (prmOrderType);
                            cmd.Parameters .Add (prmCustomerID);
                            cmd.Parameters .Add (prmEmployeeID);
                            cmd.Parameters .Add (prmOrderStatus);
                            cmd.Parameters .Add (prmRemarks);

                                  this.conCAGDB .Open ();                              
                                  cmd.ExecuteNonQuery ();
                                  OrderTime = System.DateTime .Now .ToShortTimeString ();                        
                            catch (Exception ex)
                                  MessageBox.Show ("Error Message: "+ex.Message,"Order Insert Error");
                                  this.conCAGDB .Close ();

    Author Comment

    I am sorry angelIII you where right ,  parametrized queries will help me to handle this problem.

    My bos wants the single quotes in the record anyway.

    Thank you so much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
    A short article about problems I had with the new location API and permissions in Marshmallow
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    794 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

    15 Experts available now in Live!

    Get 1:1 Help Now