How to get id from other table and insert in other table

Jieha Lee

How to I get id from 'Admin' table based on username(which is Session["admin"]) to insert in 'Message' table? I got the error as below when I use SqlDataReader to detect the id.

The error:

System.Data.SqlClient.SqlException: Must declare the scalar variable "@tel_no".

Here is the code that I use:

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=USER1-PC\SQLEXPRESS;Integrated Security=true;Database=STAFF_TEST");
con.Open();
SqlCommand getName = new SqlCommand("select id, customer_id from Admin", con);
SqlDataReader name = getName.ExecuteReader();
SqlCommand cmd = new SqlCommand("insert into Message(tel_no, message, timestamp, staff_id) values(@tel_no, @message, @timestamp, @staff_id)", con);
if (name.Read())
{
if (name["customer_id"].Equals(Session["admin"].ToString()))
{
cmd.Parameters.Add("@tel_no", SqlDbType.NVarChar).Value = Request.Form["offcell"];
cmd.Parameters.Add("@message", SqlDbType.NVarChar).Value = Request.Form["message"];
cmd.Parameters.Add("@timestamp", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@staff_id", SqlDbType.NVarChar).Value = name["id"];
}
name.Close();
}
cmd.ExecuteNonQuery();
con.Close();
Response.Redirect("Default.aspx");
}

Thanks.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmraj Thakur8y ago
You dont need to loop over all result and check one by one with session value...
protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source=USER1-PC\SQLEXPRESS;Integrated Security=true;Database=STAFF_TEST");
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into Message(tel_no, message, timestamp, staff_id) values(@tel_no, @message, @timestamp, (select id from Admin where customer_id=@customer_id))", con);
    cmd.Parameters.Add("@tel_no", SqlDbType.NVarChar).Value = Request.Form["offcell"];
    cmd.Parameters.Add("@message", SqlDbType.NVarChar).Value = Request.Form["message"];
    cmd.Parameters.Add("@timestamp", SqlDbType.DateTime).Value = DateTime.Now;
    cmd.Parameters.Add("@customer_id", SqlDbType.NVarChar).Value = Convert.ToString(Session["admin"]);
    cmd.ExecuteNonQuery();
    con.Close();
    Response.Redirect("Default.aspx");
}
Bryian Tan8y ago
I think it skipping the below line?
if (name["customer_id"].Equals(Session["admin"].ToString()))
name["customer_id"] is an object, it need to be cast to a string in order to compare with another string.
if ((string)name["customer_id"].Equals(Session["admin"].ToString()))

or column index will work too
name.GetValue(ColumnIndexOf_customer_id)