how to add single quotation as a string in a query?

Question

I'm trying to get file destination path using a text box and pass it into the database.(Developing in Visual Studio) When the query is running below error is displayed....

The method updateClark in the user class is shown below

public void updateClark(string _cid, string _path)
{
    SqlCommand cmd = new SqlCommand(@"UPDATE tbl_Path SET folder_path='" + _path + "' WHERE clark_id='" + _cid + "'", ConnectionDB.connection());
    cmd.ExecuteNonQuery();
}

What I missed in my code?

Answer 1

Now that you edited your question to include the actual relevant code, you should do something like this:

public void updateClark(string _cid, string _path)
{
    string command = "UPDATE tbl_Path SET folder_path=@folderPath WHERE clark_id=@clarkId";

    using (SqlCommand cmd = new SqlCommand(command, ConnectionDB.connection()))
    {
       cmd.Parameters.AddWithValue("folderPath", _path);
       cmd.Parameters.AddWithValue("clarkId", _cid);
       cmd.ExecuteNonQuery();
    }
}

Answer 2

If you use SQLParameters, the single quote will be handled correctly and you don't have to worry about SQL Injection (where someone can enter nested SQL commands in the textbox and they blindly get passed back to the server).

The code below assumes that the ConnectionDB.connection() method opens a new connection to the database. You should consider putting your disposable objects, like the connection and the command, in using blocks so that they are closed and disposed of automatically.

public static void updateClark(string cid, string path)
{
    var cmdStr = "UPDATE tbl_Path SET folder_path=@path WHERE clark_id=@cid";

    using (var con = ConnectionDB.connection())
    {
        con.Open();

        using (var cmd = new SqlCommand(cmdStr, con))
        {
            cmd.Parameters.AddWithValue(new SqlParameter("@path", path));
            cmd.Parameters.AddWithValue(new SqlParameter("@cid", cid));
            cmd.ExecuteNonQuery();
        }
    }
}

Answer 3

This is how you should safely access DB without need to juggle your data.

private static void SqlCommandPrepareEx(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlCommand command = new SqlCommand(null, connection);

        // Create and prepare an SQL statement.
        command.CommandText =
            "INSERT INTO Region (RegionID, RegionDescription) " +
            "VALUES (@id, @desc)";
        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
        SqlParameter descParam = 
            new SqlParameter("@desc", SqlDbType.Text, 100);
        idParam.Value = 20;
        descParam.Value = "First Region";
        command.Parameters.Add(idParam);
        command.Parameters.Add(descParam);

        // Call Prepare after setting the Commandtext and Parameters.
        command.Prepare();
        command.ExecuteNonQuery();

        // Change parameter values and call ExecuteNonQuery.
        command.Parameters[0].Value = 21;
        command.Parameters[1].Value = "Second Region";
        command.ExecuteNonQuery();
    }
}

Code snippet stolen from MSDN

Answer 4

There are many good answers above, just in case you look for an alternative.

public void updateClark(string _cid, string _path)
{
    string sql = String.Format("UPDATE tbl_Path SET folder_path={0} WHERE clark_id={1}",path,cId);
    SqlCommand cmd = new SqlCommand(sql, ConnectionDB.connection());
    cmd.ExecuteNonQuery();
}

Answer 5

You need to replace a single quotation mark with two quotation marks. In your example above, it would seem to be the variable txtPath. The value "F:\Softwares\IDE's" would need to be passed in as "F:\Softwares\IDE''s" .

Good luck!