Dynamic SQL query causes "Unclosed quotation mark" error

I'm getting this error:

Unclosed quotation mark after the character string ''.

With the following 开发者_开发问答code:

lSQL = "DELETE FROM tblCourses where courseCode='" + aCourseCode + "'";

Where lSQL is local variable and aCourseCode is a store value. Any suggestions?

You should always use parametrized queries or your code is vulnerable to errors as the one you are getting and even worse to SQL Injection attacks. Never use string concatenations as in your code when building SQL queries. Here's the correct way:

using (var conn = new SqlConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "DELETE FROM tblCourses WHERE courseCode = @courseCode";
    cmd.Parameters.AddWithValue("@courseCode", aCourseCode);
    int deletedRowsCount = cmd.ExecuteNonQuery();
}

This will ensure that even if the aCourseCode variable contains some escape and dangerous characters they will be properly handled.

You probably have a single or double quote coming through in your aCourseCode variable.

A better way to format strings is to use something like this:

lSQL = String.Format("DELETE FROM tblCourses where courseCode='{0}'", aCourseCode);

Also make sure you do not have any embedded double quotes or single quotes in your variable aCourseCode.

Hope that helps.