none
Cannot insert data with apostrophe from textbox to sql server. RRS feed

  • Question

  • Does anyone know how can I insert a data with apostrophe from my textbox to my sql server? Because I want that the txtLastName and txtAddress could be able to add an apostrophe in my customers2 table in the sql server, im using the nvarchar(MAX) data type. Im having some errors when I run the program with an apostrophe in the txtAddress and txtLastName can anybody tell me or give an example from this code on how to solve this problem? Here’s the code that I have in my InsertNewRecord() thanks in advanced!


    Code Block

    private void InsertNewRecord()

            {

     

                SqlCommand cmdInsert = new SqlCommand();

     

                cmdInsert.Connection = cn;

                cmdInsert.CommandType = CommandType.Text;

     

                cmdInsert.CommandText = " INSERT INTO Customers2 " +

                 " (LastName, FirstName, MI, Address, TelNo, CustomerBday, CustomerStatus, Registration, CustPicture) " +

                 " VALUES ('" +

                 txtLastName.Text + "', '" + txtFirstName.Text +

                 "' , '" + txtMI.Text + "' , '" +

                 txtAddress.Text + "' , '" + txtTelNo.Text +

                 "' , '" + dtpBirthdate.Value.Date.ToString() +

                 "' , '" + cboStatus.Text + "' , '" +

                 dtpRegistration.Value.Date.ToString() + "', @CustPicture)";

     

                FileStream fs;

     

                if (picImage.Image == null)

                {

                    string anony = Application.StartupPath + @"\Anonymous.jpg";

     

                    fs = new FileStream(anony, FileMode.Open, FileAccess.Read);

                }

                else

                {

                    fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);

                }

     

                Byte[] bData = new Byte[fs.Length];

                fs.Read(bData, 0, (int)bData.Length);

                fs.Close();

     

     

                SqlParameter prm = new SqlParameter("@CustPicture", SqlDbType.VarBinary, bData.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, bData);

               

                cmdInsert.Parameters.Add(prm);

     

                cmdInsert.ExecuteNonQuery();

     

            }

    Friday, December 28, 2007 11:31 AM

Answers

  • I cannot endorse either of the solutions offered. First, never concatenate fields into an SQL statement as you are doing. Best practices says to build your INSERT statement to accept parameters. This means you need to build a Parameter object for each parameter. These parameters handle date and string framing issues. (Your code will fail with a LastName of "O'Mally". ) It also stops SQL Injection attacks.

     

     

    Saturday, December 29, 2007 4:48 AM
    Moderator

All replies

  • You will try to replace singe apostrophe  with double apostrophe ..

    like

    txtAddress.Text.Replace("'","''")
    Friday, December 28, 2007 12:11 PM
  •  

    use:

     

    Code Block

    miTextBox.Text = "\'":

     

     

    Friday, December 28, 2007 3:10 PM
  • I cannot endorse either of the solutions offered. First, never concatenate fields into an SQL statement as you are doing. Best practices says to build your INSERT statement to accept parameters. This means you need to build a Parameter object for each parameter. These parameters handle date and string framing issues. (Your code will fail with a LastName of "O'Mally". ) It also stops SQL Injection attacks.

     

     

    Saturday, December 29, 2007 4:48 AM
    Moderator
  • Thanks William, that was pretty perfect.
    Saturday, December 29, 2007 3:38 PM