Escape single quote in sql query c#

Here I have this method in my CandidateOp class file.

public SqlDataReader getpartyID(string partyName)
{
    string query = "EXEC partyIDtoInsert'" +partyName+ "'";
    return new DataAccessLayer().executeQuerys(query);
}

I’m passing the ComboBox text in the form and I am getting the ID to the integer type variable tempPrID.

SqlDataReader reader02 = new CandidateOP().getpartyID(cmbParty.Text);
if (reader02.HasRows)
{
    while (reader02.Read())
    {
         tempPrID = (Int32)reader02[0];
    }
    reader02.Close();
}
else
{
    MessageBox.Show("Please enter a valid Party Name", "Invalid DATA");
} 

The partyIDtoInsert, is a stored procedure I have created and it is being called in the method getpartyID as shown before, to get the id of united national party.

EXEC partyIDtoInsert 'United National Party'; 

If I have a party name called “United People’s Freedom Alliance”, so If I like to insert that name, my stored procedure executing code it looks like this.

EXEC partyIDtoInsert 'United People's Freedom Alliance';

In this case the it considers the end point as People’s single quote and returns me with sql exception.

How to escape this single quote problem in the statement to execute my stored procedure.

Answer

Just replace single quote (‘) by double single quote like ”.

so your function will look like:

public SqlDataReader getpartyID(string partyName)
            {
                string query = "EXEC partyIDtoInsert'" +partyName.Replace("'", "''") + "'";
                return new DataAccessLayer().executeQuerys(query);
             }

I hope it will solve your problem. 🙂