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

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
– Ňɏssa Pøngjǣrdenlarp, Commented Apr 27, 2018 at 0:08
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
– Ctznkane525, Commented Apr 27, 2018 at 0:20

Ryan Oscar · Accepted Answer · 2018-04-27 00:26:40Z

4

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

edited Apr 27, 2018 at 0:26

Ryan Oscar

2791 gold badge5 silver badges20 bronze badges

answered Apr 27, 2018 at 0:20

STLDev

6,17829 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ňɏssa Pøngjǣrdenlarp Over a year ago

Can we stop using AddWithValue() already?

Ryan Oscar Over a year ago

You're a living legend my man! @STLDeveloper

STLDev Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related