I Want to get id from select query in C# but every time i run the program, The query returns me "-1" [duplicate]

Question

I am trying To get ID against selected name in Drop Down list by using select query but it always returns the value "-1" instead of relevant result.

SqlCommand cmd2 = con.CreateCommand();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "Select Pid From Provinces where Pname = '" + pr + "'";
cmd2.CommandText = "Select Pid From Provinces where Pname = '" + prov.Text + "'";
int pid = cmd2.ExecuteNonQuery();

Answer 1

You need to use ExecuteScalar instead of ExecuteNonQuery

int pid = Convert.ToInt32(cmd2.ExecuteScalar());

For more details please refer Link

Answer 2

The reason is that ExecuteNonQuery doesn't return the database value when using a Select command - It returns a return code for success or failure.

If you want to read the database value, use the following code. Note that I used an SqlParameter instead of your parameter concatenation, which can cause SQL injections and is a poor practice:

SqlCommand cmd2 = con.CreateCommand();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "Select Pid From Provinces where Pname=@pr";
cmd2.Parameters.Add(new SqlParameter("pr", pr));
int result = Convert.ToInt32(cmd2.ExecuteScalar());

---

Alternativly, you can use fill a DataTable with multiple results:

SqlCommand cmd2 = con.CreateCommand();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "Select Pid From Provinces where Pname=@pr";
cmd2.Parameters.Add(new SqlParameter("pr", pr));

SqlConnection Connection = new SqlConnection(ConnectionString);
SqlDataAdapter adp = new SqlDataAdapter(cmd2);

// Create a new datatable which will hold the query results:
DataTable dt = new DataTable();

Connection.Open();

// Fill a datatable with the query results:
adp.Fill(dt);

Connection.Close();