How do I pass guid in parameterised query?

My current best code is:

string delNonQuery = "DELETE FROM " + Settings.DataSource + " WHERE @keycolumn=@keyuid";

SqlCommand cmd = new SqlCommand(delNonQuery,readerConn);
SqlParameter kc = new SqlParameter("keycolumn", SqlDbType.VarChar);
SqlParameter key = new SqlParameter("keyuid", SqlDbType.VarChar);
cmd.Parameters.Add(kc).Value = Settings.KeyColumn;
cmd.Parameters.Add(key).Value = Page.Request["key"].ToString().Trim();

readerConn.Open();
cmd.ExecuteScalar();
readerConn.Close();

This executes but affects a whopping zero rows. If I change the SqlDbType on keyuid to UniqueIdentifier it just ends up with me getting a dozen variations on "failed to conv开发者_C百科ert character string into uniqueidentifier". I have to use a parameterized query for data cleanliness, I'm just really stuck as to how...

---

You can't specify a parameter for a column name - you need to concatenate it the same way you do for the table name.

This:

"DELETE FROM " + Settings.DataSource + " WHERE @keycolumn=@keyuid"

Should change to:

"DELETE FROM " + Settings.DataSource + " WHERE " + Settings.KeyColumn + " =@keyuid"

Though I would probably write it as:

string delNonQuery = string.Format("DELETE FROM {0} WHERE {1} = @keyuid", 
                                   Settings.DataSource, 
                                   Settings.KeyColumn);

---

For completeness sake, I will mention that this is open to SQL injection. You need to make sure your Settings values are clean.

---

I don't think you can parameterise the column name ("keycolumn")

Try this:

string delNonQuery = string.Format("DELETE FROM " + Settings.DataSource + " WHERE {0}=@keyuid", Settings.KeyColumn);

SqlCommand cmd = new SqlCommand(delNonQuery,readerConn);
SqlParameter key = new SqlParameter("keyuid", SqlDbType.VarChar);
cmd.Parameters.Add(key).Value = Page.Request["key"].ToString().Trim();

readerConn.Open();
cmd.ExecuteScalar();
readerConn.Close();

Usual warnings apply regarding concatanating strings to build SQL; this is likely a security risk.

The best method might be to encapsulate your SQL in a stored procedure, pass the column name and value as parameters and then execute using dynamic SQL.

---

You need to convert the string to GUID:

Relevant Lines:

SqlParameter key = new SqlParameter("keyuid", SqlDbType.UniqueIdentifier);
...
cmd.Parameters.Add(key).Value = new Guid(Page.Request["key"].ToString().Trim());

Which only solves the GUID/UniqueIdentifer issue