Return last record from table

Asked

Viewed 269 times

0

I’m trying to make a method that returns the last record of the table but gives error:

Failure in operation"coditem" 

   public int UltimoItem()
    {
        con = conexao.obterConexao();
        try
        {
            cmd = new SqlCommand("SELECT MAX(Cod_Item) FROM Pedidos_Itens", con);
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            int Ultimo = 0;
            while (dr.Read())
            {
                Ultimo = Convert.ToInt32(dr["Cod_Item"]);

            }
            return Ultimo;
        }
        catch (Exception ex)
        {
            throw new Exception("Falha na operação: " + ex.Message);
        }
        finally
        {
            con.Close();
        }
    }
c# sql .net ado.net

  • The field Cod_Item exists?

  • Try putting a alias for the return of the function MAX and return that same name on Convert

  • Depending on the database you are using, when you do "MAX" you have to give it a name, so "MAX(Cod_item) AS Cod_item" if not it returns the unnamed column, then when you try to access dr["Cod_item"] it does not find the column and the error. See if that’s the problem. That’s exactly what @Leandropaixão said in the answer above.

  • SELECT Cod_Item FROM Pedidos_Itens order by Cod_Item desc limit 1

  • Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).

2 answers

4

It is much simpler and more efficient than this, just read with the method ExecuteScalar() which is correct:

public int UltimoItem() {
    using (var con = conexao.obterConexao())
    using (var cmd = new SqlCommand("SELECT MAX(Cod_Item) FROM Pedidos_Itens", con) {
        return Convert.ToInt32(cmd.ExecuteScalar());
    }
}

I put in the Github for future reference.

I took the opportunity to treat the release of resources correctly.

I removed the exception because it is not doing anything useful. First it is a mistake to capture Exception, it’s worse to cast another Exception. If you have done nothing to recover from the exception then leave to treat the exception elsewhere. But do only in a more specific one, at least one SqlException, perhaps even more specific. There is no obligation to treat all exceptions, only if you can do something useful.

  • Saved me this ExecuteScalar(), was using the ExecuteNonQuery() would only return to me -1

1


As @Leando said, it’s here : Ultimo = Convert.ToInt32(dr["Cod_Item"]); When using MAX you must name this field in order to retrieve it.

In this case I believe that you could access positionally using the syntax Ultimo = Convert.ToInt32(dr[0]);, however it is not recommended, that when changing your SELECT, you may make a mistake by changing the column position by inserting a new one or by changing the SELECT structure.

Try:

public int UltimoItem()
    {
        con = conexao.obterConexao();
        try
        {
            cmd = new SqlCommand("SELECT MAX(Cod_Item) AS 'CodItem' FROM Pedidos_Itens", con);
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            int Ultimo = 0;
            while (dr.Read())
            {
                Ultimo = Convert.ToInt32(dr["CodItem"]);

            }
            return Ultimo;
        }
        catch (Exception ex)
        {
            throw new Exception("Falha na operação: " + ex.Message);
        }
        finally
        {
            con.Close();
        }
    }

  • A small correction to your answer: Ultimo = Convert.ToInt32(dr[0]);

  • I edited, thank you very much!

Browser other questions tagged c# sql .net ado.net

You are not signed in. Login or sign up in order to post.