How to generate new code from DBO

Question

I have a table with the name Produtos in which I inserted 3 products for testing. Now I’m assembling the product registration form and I need to always generate a sequential product code from the last id already registered + 1.

The code I made is this, I followed my logic but n is working.

    private void GerarNovoCodigo()
    {
        string strCon = "Data Source=MEUPC; Initial Catalog=MEUDB; Integrated Security=SSPI";
        SqlConnection sqlCon = new SqlConnection(strCon);
        SqlCommand sqlCom = new SqlCommand();
        sqlCom.Connection = sqlCon;
        sqlCon.Open();

        sqlCom.CommandText = "Select max(prod_codigo) + 1  from Produtos";

        txt_prod_cod.Text = Convert.ToString(sqlCom.ExecuteNonQuery());
    }

Answer 1

The specific problem is that the method used returns the number of affected lines and not the generated code. The right one is to use the ExecuteScalar().

In cases where there is concurrent access (almost always has) picking up a code like this is problematic. You have to have the data recorded and then generate a new code, otherwise you have a running condition.

Also it seems that the application has enough repeated code. It is not a good idea to dry afterwards, but that’s up to you.

Answer 2

I elaborated this answer only to encompass and try to remedy all the doubts I found in the question about SQL, including.

Let’s talk about SQL first...

What you want the database itself already does. For this, just mark the field of Key as Auto Increment (Identity). Once done, you don’t need to enter the code in INSERT, that the bank makes everything automatic for you.

An example would be:

CREATE TABLE Produtos
(  
 Id int IDENTITY(1,1),  
 Nome varchar (100) 
); 

INSERT INTO Produtos VALUES ('Produto1')
INSERT INTO Produtos VALUES ('Produto2')

Once done, the database will save the data as:

Id  | Nome
----------------
1   | Produto1
2   | Produto2

If you want to know what the next code will be, you can use the abaix code:

SELECT isnull(IDENT_CURRENT('Produtos') + IDENT_INCR('Produtos'),1)

This code will fetch the last id IDENT_CURRENT() and add to the incremental value, with the IDENT_INCR().

In your example you are increasing over 1, but if it were 2, 3, etc... The IDENT_INCR() gets that value from Key automatically.

Problem solving...

As already pointed out in the other answers, just change the Executenonquery() for Executescalar() that will solve your problem.

If you want to understand a little more the difference between the methods, this link explains a little about them.

Answer 3

The method you are using to run SQL (ExecuteNonQuery()) is a method used to Inserts and updates and its return is the amount of affected lines as described in documentation.

Use the method ExecuteScalar(), that the same will return what you wish.

It should stay this way:

txt_prod_cod.Text = Convert.ToString(sqlCom.ExecuteScalar());

Documentation of ExecuteScalar().

This can solve your problem, more as suggested by Maniero this is not a good practice, and you should probably be repeating a lot of code in your application, it would be good as soon as possible to re-evaluate the way of work and check for improvements.