Remove a specific character from a string to make INSERT accept string with quotes

Question

Not sure this question has been asked before though. If it were, my apologies.

I need to remove an apostrophe character from a string.

The reason for this has to be done because it's triggering an error in the SQL query for client's name which I fetch from the local database.

The error indicates near the 'apostrophe s'.

When I remove that data field from the local database error ain't appear.

I came up with a solution for removing the apostrophe character from the client name.

I tried to use Remove() function but it only works for integers. (string to int conversion error).

My code is as follows:

while (rdr.Read())
            {
                 int promised_date = (int)(rdr.GetValue(0));

string strClientName = (rdr.GetValue(1).ToString());

                string strClientReference = (rdr.GetValue(2).ToString());
                string strJobCategory = (rdr.GetValue(3).ToString());
                string datCommisioned = (rdr.GetValue(4).ToString());
                string datPromisedDelivery = (rdr.GetValue(5).ToString());


                  if (this.OpenConnection() == true)
                    {
                        string querynew = "INSERT INTO jobs_table (nJobNumber,strClientName,strClientReference,strJobCategory,datCommisioned,datPromisedDelivery) VALUES ("+promised_date+",'"+strClientName+"','"+strClientReference+"','"+strJobCategory+"','"+datCommisioned+"','"+datPromisedDelivery+"' )";//yeyyy why only few?

                        MySqlCommand cmd = new MySqlCommand(querynew, connection);
                        cmd.ExecuteNonQuery(); 

                      this.CloseConnection();
                    }
              }

Does anyone have an idea how to remove the apostrophe from strClientName when reading the data?

No No No. Rather than changing the data to make it "safe", use SQL Parameters to pass the data. That has not been the correct way to compose SQL for a long time — Ňɏssa Pøngjǣrdenlarp, Apr 27 '18 at 00:08
What's the client called, [Bobby Tables](https://xkcd.com/327/)? — Maarten Bodewes, Apr 27 '18 at 00:11
if its the same database you can use an INSERT into SELECT call, do it all at once and not inside a loop at all...you have an openconnection/closeconnection in a reader loop which is odd — Ctznkane525, Apr 27 '18 at 00:20

score 4 · Accepted Answer · edited Apr 27 '18 at 00:26

You should use parameters. You can read more about here: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html

while (rdr.Read())
{
    int promised_date = (int)(rdr.GetValue(0));
    string strClientName = (rdr.GetValue(1).ToString());
    string strClientReference = (rdr.GetValue(2).ToString());
    string strJobCategory = (rdr.GetValue(3).ToString());
    string datCommisioned = (rdr.GetValue(4).ToString());
    string datPromisedDelivery = (rdr.GetValue(5).ToString());

    if (this.OpenConnection() == true)//closing parenthesis
    {
        // query using parameter names 
        string querynew = "INSERT INTO jobs_table "
                          + "(nJobNumber,strClientName,strClientReference,strJobCategory,datCommisioned,datPromisedDelivery)" 
                          + "VALUES (@PromisedDate, @ClientName, @ClientReference, @JobCategory, @Commisioned, @PromisedDelivery)";

        MySqlCommand cmd = new MySqlCommand(querynew, connection);

        // add parameters and their value
        cmd.Parameters.AddWithValue("@PromisedDate", promised_date);
        cmd.Parameters.AddWithValue("@ClientName", strClientName);
        cmd.Parameters.AddWithValue("@ClientReference", strClientReference);
        cmd.Parameters.AddWithValue("@JobCategory", strJobCategory);
        cmd.Parameters.AddWithValue("@Commissioned", datCommissioned);
        cmd.Parameters.AddWithValue("@PromisedDelivery", datPromisedDelivery);

        cmd.ExecuteNonQuery(); 

        this.CloseConnection();
    }
}

**[Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/)** — Ňɏssa Pøngjǣrdenlarp, Apr 27 '18 at 00:24
Ryan, @Plutonix makes a good point. You should consider following his advice. Click on his answer and it will link you to an article on why it's not always great to use `Parmeters.AddWithValue()`. Assuming you know the database field data types, you should probably instead use `Parameters.Add()` using appropriate data types. — STLDev, Apr 27 '18 at 00:45

Remove a specific character from a string to make INSERT accept string with quotes

1 Answers1