read a table with uniqueidentifier ID

Question

I want search in database and if the code is exist set ID equal by session[SID]. the ID of my table is uniqueidentifier and it has error. the error is on r.read();:

Conversion failed when converting from a character string to uniqueidentifier

Code:

protected void btnSearch_Click(object sender, EventArgs e)
{
    string CodeReg = txtCodeReg.Text;

    Guid g = new Guid();

    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Data Source=.;Initial Catalog=Db_Hospital;Integrated Security=True";

    con.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "Select CodeMeli From Tbl_Staff where (ID=@ID)";
    cmd.Parameters.AddWithValue("@ID", txtCode.Text);

    SqlDataReader r = cmd.ExecuteReader();
    r.Read();

    if (r.HasRows)
    {
        Session["SID"] = g;
    }
}

Answer 1

AddWithValue will create a parameter whose type is the type of the value passed. In your case this parameter is of type string. But to work correctly you should pass an uniqueidentifier instead.

if (!Guid.TryParse(txtCode.Text, out Guid result))
{
    Console.WriteLine("not a valid guid");
    return;
}
cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = result;

In general you should try to avoid this AddWithValue because there are many undesiderable side effects. You can read about this scenario in

Using Parameters.AddWithValue in SqlDataAdapter

Answer 2

Another posible solution. Just convert the ID uniqueidentifier to VARCHAR(36).

protected void btnSearch_Click(object sender, EventArgs e)
{
    string CodeReg = txtCodeReg.Text;

    Guid g = new Guid();

    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Data Source=.;Initial Catalog=Db_Hospital;Integrated Security=True";

    con.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "Select CodeMeli From Tbl_Staff where (CONVERT(VARCHAR(36),ID)=@ID)";
    cmd.Parameters.AddWithValue("@ID", txtCode.Text);

    SqlDataReader r = cmd.ExecuteReader();
    r.Read();

    if (r.HasRows)
    {
        Session["SID"] = g;
    }
}