asked on

Single Quotes error when inserting text in database fields

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

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

Clever_Bob

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.

cyborgcom

ASKER

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

cyborgcom

ASKER

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.