Exception error says no data is present. Possible error in coding

Question

I got two databases. I'm trying to get the latest ID in test2 and forward that ID to test

    SqlCommand readcmd = new SqlCommand("select MAX(PID) from test2;");
    SqlDataReader rdr = cmd.ExecuteReader();
    rdr.Read();
    int pid = (int)rdr["PID"];
    cmd.CommandText = "insert into test (PID) values ('"+ rdr.GetInt32(0) +"')";

there should be data because i've checked my table data and there's a lot of records that I intentionally filled. What seems to be wrong?

Answer 1

SqlDataReader is a forward only reader.The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop.

SqlCommand readcmd = new SqlCommand("select MAX(PID) from test2;");
SqlDataReader rdr = cmd.ExecuteReader();
int pid = 0;
if(rdr.HasRows)
{  
        while (rdr.Read())
        {
           pid = rdr.GetInt32(0);
        }
}
cmd.CommandText = "insert into test (PID) values (@pid)";
cmd.Parameters.AddWithValue("@pid", pid);

Also, in general, you should not directly concatenate the variables into your command text. Use parameters instead to prevent SQL Injection attacks.