Input Error

[semomaniz]

The problem that i am having is when ever i input text in format like O'Neal as lastname then my code breaks and gives me following error

Incorrect syntax near 'Neal'. Unclosed quotation mark before the character string ''.

If i remove ( ' ) from the Input the code works. How do i solve this? Please advice.

 
string strSql = " Update Drivers SET CarrierID =@carrierid, LastName =@lastname, FirstName =@firstname, Address =@address, City =@city," +
            "StateID =@stateid, ZipCode =@zipcode, CellAreaCode =@cellareacode, CellPrefix =@cellprefix, CellPhone =@cellphone, "+
            "HomeAReaCode =@homeareacode, HomePrefix =@homeprefix, HomePhone =@homephone, FaxAreaCode =@faxareacode, FaxPrefix =@faxprefix," +
            "Fax =@fax, TruckNumber =@trucknumber where driverid = " + DriverID;
 
        SqlConnection conn = new SqlConnection();
        String constr = ConfigurationManager.ConnectionStrings[("USBSRVR")].ConnectionString;
        conn = new SqlConnection(constr);
        SqlCommand mycommand = new SqlCommand(strSql, conn);
 
        //adding sqlparameters
        #region parameters
 
        SqlParameter carrier = new SqlParameter("@carrierid", SqlDbType.Int);
        mycommand.Parameters.Add(carrier);
        mycommand.Parameters["@carrierid"].Value = CarrierID;
 
        SqlParameter Last = new SqlParameter("@lastname", SqlDbType.NVarChar);
        mycommand.Parameters.Add(Last);
        mycommand.Parameters["@lastname"].Value = LastName;
 
        SqlParameter First = new SqlParameter("@firstname", SqlDbType.NVarChar);
        mycommand.Parameters.Add(First);
        mycommand.Parameters["@firstname"].Value = FirstName;
 
        SqlParameter addres = new SqlParameter("@address", SqlDbType.NVarChar);
        mycommand.Parameters.Add(addres);
        mycommand.Parameters["@address"].Value = Address;
 
        SqlParameter drivercity = new SqlParameter("@city", SqlDbType.NVarChar);
        mycommand.Parameters.Add(drivercity);
        mycommand.Parameters["@city"].Value = City;
 
        SqlParameter driverstate = new SqlParameter("@StateID", SqlDbType.Int);
        mycommand.Parameters.Add(driverstate);
        mycommand.Parameters["@StateID"].Value = StateID;
 
        SqlParameter zip = new SqlParameter("@zipcode", SqlDbType.Char);
        mycommand.Parameters.Add(zip);
        mycommand.Parameters["@zipcode"].Value = ZipCode;
 
        SqlParameter cad = new SqlParameter("@cellareacode", SqlDbType.NVarChar);
        mycommand.Parameters.Add(cad);
        mycommand.Parameters["@cellareacode"].Value = CellAreaCode;
 
        SqlParameter cellpref = new SqlParameter("@cellprefix", SqlDbType.NVarChar);
        mycommand.Parameters.Add(cellpref);
        mycommand.Parameters["@cellprefix"].Value = CellPrefix;
 
        SqlParameter cellphon = new SqlParameter("@cellphone", SqlDbType.NVarChar);
        mycommand.Parameters.Add(cellphon);
        mycommand.Parameters["@cellphone"].Value = CellNumber;
 
        SqlParameter hac = new SqlParameter("@homeareacode", SqlDbType.NVarChar);
        mycommand.Parameters.Add(hac);
        mycommand.Parameters["@homeareacode"].Value = HomeAreaCode;
 
        SqlParameter hp = new SqlParameter("@homeprefix", SqlDbType.NVarChar);
        mycommand.Parameters.Add(hp);
        mycommand.Parameters["@homeprefix"].Value = HomePrefix;
 
        SqlParameter homeph = new SqlParameter("@homephone", SqlDbType.NVarChar);
        mycommand.Parameters.Add(homeph);
        mycommand.Parameters["@homephone"].Value = HomePhone;
 
        SqlParameter fac = new SqlParameter("@faxareacode", SqlDbType.NVarChar);
        mycommand.Parameters.Add(fac);
        mycommand.Parameters["@faxareacode"].Value = FaxAreaCode;
 
        SqlParameter faxpref = new SqlParameter("@faxprefix", SqlDbType.NVarChar);
        mycommand.Parameters.Add(faxpref);
        mycommand.Parameters["@faxprefix"].Value = FaxPrefix;
 
        SqlParameter faxnum = new SqlParameter("@fax", SqlDbType.NVarChar);
        mycommand.Parameters.Add(faxnum);
        mycommand.Parameters["@fax"].Value = FaxNumber;
 
        SqlParameter trucknum = new SqlParameter("@trucknumber", SqlDbType.NVarChar);
        mycommand.Parameters.Add(trucknum);
        mycommand.Parameters["@trucknumber"].Value = TruckNumber;
 
        #endregion
 
        //execution of the sql statement.
        try
        {
            conn.Open();
            mycommand.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
 
 
 

[Plater]

You can do a number of things.
Using the SQLParameteres should ahve solved that issues for you.

Otherwise you need to escape the single quote character ' by making it be two SINGLE quote characters '' (Note: Not the same as a double quote " )

[semomaniz]

I also thought that using SQLParameters would fix the problem but still i get the same error. Is there some thing that i am missing?

[Plater]

Hmm, I've only used them with StoredProcedure s, so maybe that's the part that is different.

[semomaniz]

so i am guessing using a Sting.Replace(" '","''") is my easiest option to get rid of this error.

[Plater]

That's what its sounding like.
Remember you have to do it on the value before it goes into your parameters though