dusk dusk - 2 months ago 6
C# Question

How do I correctly use wildcards in a prepared statement?

My SQL prepared statement using a wildcard is returning zero records. All of the examples I've found do not use the .Prepare() method, which requires the SqlParameter to have a specified type. So when I switch my parameter setup to using

.AddWithValue("@cid", cid+"%")
Prepare() throws an exception about not having an explicitly set type.

ctx.Open();
string sql = "SELECT GRAD_COMMENCEMENT_DATE FROM GRADUATES WHERE GRADUATES_ID LIKE @cid";
SqlCommand query = new SqlCommand(sql,ctx);
SqlParameter id = new SqlParameter("@cid", SqlDbType.VarChar, 7);
id.Value = string.Format("{0}%", cid);
query.Parameters.Add(id);
//query.Prepare();
SqlDataReader rs = query.ExecuteReader();

string grad_date = "";
while (rs.Read())
{
//no records =(
grad_date = rs["GRAD_COMMENCEMENT_DATE"].ToString();
Console.WriteLine("grad date: "+grad_date);
}
rs.Close();
ctx.Close();

Answer

After you changed

id.Value = string.Format("{0}%", cid);

and removed

query.Prepare();

It will return all results for you. I tested on my local and I don't know why you need prepared here?

Comments