Database Error: There is no row at position 0

Question

I believe this question was asked several months back, but i believe my situation is different and the same rules may not apply.

Everytime I execute this method that same error pops up. There is no row at position 0. If I change [0] to [1] or [15]; There is no row at [1] and etc. Could this mean that my database isnt even connecting? Should I write some kind of if statement to determine to check if the rows are even there?

    public bool UpdateOrderToShipped(string order)
{
    orderNumber = order;
    string batch = ConfigurationManager.AppSettings["SuccessfulOrderBatch"];
    string statement = "UPDATE SOP10100 SET BACHNUMB = '"+ batch +"' WHERE SOPNUMBE = @SOPNUMBE";
    SqlCommand comm = new SqlCommand(statement, connectionPCI);
    comm.Parameters.Add("SOPNUMBE", orderNumber);
    try
    {
        comm.Connection.Open();
        comm.ExecuteNonQuery();
        comm.Connection.Close();
    }
    catch(Exception e)
    {
        comm.Connection.Close();
        KaplanFTP.errorMsg = "Database error: " + e.Message;
    }

    statement = "SELECT SOPTYPE FROM SOP10100 WHERE SOPNUMBE = @SOPNUMBE";
    comm.CommandText = statement;
    SqlDataAdapter da = new SqlDataAdapter(comm);
    DataTable dt = new DataTable();
    da.Fill(dt);
    soptype = dt.Rows[0]["SOPTYPE"].ToString();    //errror here

    return true;
}

Answer 1

This is very simple ... it means that no results were returned from your query. You always have to code defensively and check to see if the Rows array has any items in it before trying to index into it. Something like:

if (dt.Rows.Count > 0)
    soptype = dt.Rows[0]["SOPTYPE"].ToString();
else
    somethingWentWrong();

Answer 2

you might have data in the table but I think the connection closes after the first query. try opening the connection again. Also you have string concatenation in the first SQL query, which isn't a good practice. try using block instead of try.. catch, just for a better code. And as Joel suggested, use a check

Answer 3

for (int i = 0; i <= dt.rows.count; i++)
{
    // do something till rows in DT
}