?
Solved

Single Quotes error when inserting text in database fields

Posted on 2006-05-02
4
Medium Priority
?
252 Views
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 :

SAM'S CLUB

Thank you !!

Alejandro Acevedo
0
Comment
Question by:cyborgcom
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16591297
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.

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
0
 
LVL 7

Expert Comment

by:Clever_Bob
ID: 16591416
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.
0
 

Author Comment

by:cyborgcom
ID: 16592008
I try the following and still having problems with single quotes.

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

                  try
                  {
                        #region Insert Order Record

                        int OrderType;
                        int CallBack = 0;
                        if (this.chkDSLOnly .Checked)
                              OrderType = 130;
                        else
                              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);

                        //objOrder.Remarks
                        try
                        {
                              this.conCAGDB .Open ();                              
                              cmd.ExecuteNonQuery ();
                              OrderTime = System.DateTime .Now .ToShortTimeString ();                        
                        }
                        catch (Exception ex)
                        {
                              MessageBox.Show ("Error Message: "+ex.Message,"Order Insert Error");
                        }
                        finally
                        {
                              this.conCAGDB .Close ();
                        }
0
 

Author Comment

by:cyborgcom
ID: 16592053
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
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…

578 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