How to generate new code from DBO

Navigation
2

I have a table with the name Produtos where I inserted 3 products for testing. Now I'm setting up 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 it is not 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());
    }
    
asked by anonymous 14.07.2016 / 15:26

3 answers

4
  

I've worked out this answer just to encompass and try to heal all the questions I've encountered in the SQL question, including.

Let's talk about SQL first ...

What you want the database itself already does. To do this, simply mark the Key field as Auto Increment (Identity) . That done, you do not need to enter the code in INSERT , that the bank does everything automatically 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 this is 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 code below: 

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

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

In your example you are increasing by 1, but if it was 2, 3, etc ... IDENT_INCR() gets this value from Key automatically.

Troubleshooting ...

As already pointed out in the other answers, just change the ExecuteNonQuery () by ExecuteScalar () that will solve your problem.

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

    
14.07.2016 / 18:50
3

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

In cases where there is concurrent access (almost always has) catching such a code is problematic. You have to have the data written and generated a new code, otherwise you have a race condition .

In addition it seems that the application has enough code repeated. It's not a good idea to wipe it off later, but that's up to you.

    
14.07.2016 / 15:55
2

The method you are using to run SQL ( ExecuteNonQuery() ) is a method used for inserts and updates and the return method is the number of rows affected as described in the documentation: link

Use the ExecuteScalar () method, which will return what you want.

It should look like this: 

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

ExecuteScalar () documentation: link

This may solve your problem, as suggested by bigown this is not a good practice, and probably you should be repeating too much code in your application, it would be nice as soon as possible to re-evaluate the way you work and check for improvements. p>     

14.07.2016 / 15:52
Rand between numbers with comma How to get index of the current iteration of a foreach?