none
Make a Select Statement to get the last ID and pass to a variable RRS feed

  • Question

  • Hi folks  good afternoon,

    First I would like to apologize myself, because I started this system in portuguese, the next one I will use only fields in english.

    I'm having a trouble to get the last ID of a table and pass it to a variable, to show in a Textbox.

    Could you please help-me with that?

    LastID is declared at the top of the form. 

    Int32 LastID = 0;

    Table: Base_Titulos_Pagos

    Table's ID : Cod_Base_Titulos_Pagos

    Here follows the code. I'm using SELECT MAX, but is returning "0". There are four (4) registers in this table.

    If you guys could help-me with a Lambda expression also.

    Thanks a lot for your help.

       private void btn_InserirPagto_Click(object sender, EventArgs e)
            {
    
                Data_Agora = DateTime.Now;
            //  data_LoginTextBox.Text = Data_Agora.ToShortDateString();
            //  login_NameTextBox.Text = Login.DadosGerais.Loginusuario;
                btn_salvar.Enabled = true;
                btn_salvar.BackColor = Color.DarkOrange;
    
                if (data_PagamentoDateTimePicker1.Text != " " && valor_PagoTextBox1.Text != " " && banco_DebitadoTextBox1.Text != " " && numero_ChequeTextBox1.Text != " ")
                {
                    try
                    {
                        cmd = new SqlCommand("INSERT INTO Base_Titulos_Pagos(Data_Pagamento, Total_Pago, Banco_Debitado, Numero_Cheque, Historico, Data_Login, Login_Name)"
                        + "VALUES(@Data_Pagamento, @Total_Pago, @Banco_Debitado, @Numero_Cheque, @Historico, @Data_Login, @Login_Name)", conn);
                        conn.Open();
                        DateTime DataPagamento = Convert.ToDateTime(data_PagamentoDateTimePicker1.Value.ToShortDateString());
                        cmd.Parameters.AddWithValue("@Data_Pagamento", DataPagamento);
                        decimal TotalPago = Convert.ToDecimal(valor_PagoTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Total_Pago", TotalPago);
                        Int32 BancoDebitado = Convert.ToInt32(banco_DebitadoTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Banco_Debitado", BancoDebitado);
                        String NumeroCheque = Convert.ToString(numero_ChequeTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Numero_Cheque", NumeroCheque);
                        String Historico = Convert.ToString(historicoTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Historico", Historico);
                        DateTime Data_Agora = DateTime.Now;
                        cmd.Parameters.AddWithValue("@Data_Login", Data_Agora);
                        String LoginName = Login.DadosGerais.Loginusuario;
                        cmd.Parameters.AddWithValue("@Login_Name", LoginName);
                        Int32 result = cmd.ExecuteNonQuery();
                        MessageBox.Show("Inicie a Seleção de Títulos para Pagamentos! " + result.ToString() + "Cheque Inserido!");
                        cmd = new SqlCommand("SET @LastID = SELECT MAX (Cod_Base_Titulos_Pagos) FROM Base_Titulos_Pagos");
                        cod_Base_Titulos_PagosTextBox.Text = Convert.ToString(LastID);
                        cod_Base_Titulos_PagosTextBox.Refresh();
                        numero_Parcelas_a_PagarDataGridView.Enabled = true;
                    }
                    catch (Exception Ex)
                    {
                        MessageBox.Show("Erro! " + Ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                        btn_fechar.Enabled = true;
                    }
                }
                else
                {
                    MessageBox.Show("Informe todos os Dados necessários para incluir o Pagamento do Título!");
                }
    
            }

    Sunday, July 7, 2019 4:17 PM

Answers

  • Hi F.oliveirarocha,

    Thank you for posting here.

    Based on your description, you want to find the max ID from the table.

    You could try the following code:

                string con = @"";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                string sql = "SELECT MAX (LastID) FROM Customer";
                SqlCommand command = new SqlCommand(sql, connection);
                var result = command.ExecuteScalar();
                int m = Convert.ToInt32(result);
                textBox1.Text = m.ToString();

    Database:

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 8, 2019 8:46 AM
    Moderator

All replies

  • As i understand from your query you are trying to get the ID of the last inserted column. To get this you can call a stored procedure and with-in the procedure you can call SELECT IDENT_CURRENT(‘tablename’) to get the ID.

    Call the stored procedure from C# to get the id and bind it to textbox on the form as required. sample sp as below:

    Create PROCEDURE [dbo].[p_SPInsert]         

     @id int ,    
     @s_x int,
     @s_y int,
     @s_height int,
     @s_width int
    AS     
     BEGIN     
       Declare @RID int = -1;
       if (@id = -1 )    
        BEGIN     
         INSERT INTO TableName(S_x,S_y,S_height, s_width)     
         VALUES     
         (@s_x,@s_y,@s_height,@s_width)     

         SELECT @RID = IDENT_CURRENT('TableName')     
        END     
     END  

    GO

    OR

    The other query could be as below to get the last record based on the condition.

     select top 1 ID from TableName where lastid  =@lastid order by id desc
       
    Sunday, July 7, 2019 4:40 PM
  • Try something like this:

     

       cmd = new SqlCommand( "INSERT INTO … VALUES (…) ; SELECT SCOPE_IDENTITY()" );

       . . .

       Int32 result = Convert.ToInt32( cmd.ExecuteScalar( ) );

     

    Sunday, July 7, 2019 4:42 PM
  • Hi KGund,

    It didn't work. I've Tried the Selec Top 1, still return 0.

    Sunday, July 7, 2019 5:23 PM
  • Viorel 

    thanks for your help.

    It did not work because I'm using ExecuteNonQuery after my insert.

    A good Idea should be to make a separete procedure to get the last ID, 

    Can you put this in a Method ? So then I catn execute this method after my insert correct?

    Thanks for your effort.


    Sunday, July 7, 2019 5:26 PM
  • Hi F.oliveirarocha,

    Thank you for posting here.

    Based on your description, you want to find the max ID from the table.

    You could try the following code:

                string con = @"";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                string sql = "SELECT MAX (LastID) FROM Customer";
                SqlCommand command = new SqlCommand(sql, connection);
                var result = command.ExecuteScalar();
                int m = Convert.ToInt32(result);
                textBox1.Text = m.ToString();

    Database:

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 8, 2019 8:46 AM
    Moderator
  • Hello.

    You need to replace your 

    SELECT MAX(...)

    to 

    SELECT SCOPE_IDENTITY()

    in the same batch or immediately after, before you make another insert.

    Any other option will give wrong result in some situation.


    Sincerely, Highly skilled coding monkey.

    Monday, July 8, 2019 9:05 AM
  • > INSERT INTO TableName

    > SELECT @RID = IDENT_CURRENT('TableName')

    This would time-to-time give a wrong IDs if there are more than one user...


    Sincerely, Highly skilled coding monkey.

    Monday, July 8, 2019 9:10 AM
  • > I've Tried the Selec Top 1, still return 0.

    You must have an IDENTITY on at least one column in the table to get this work. 



    Sincerely, Highly skilled coding monkey.

    Monday, July 8, 2019 9:13 AM
  • > SELECT MAX(...)

    There would be returned different IDs when more than one user inserting data. 

    you want to find the max ID from the table

    Most likely he need an IDs for last inserted row. 


    Sincerely, Highly skilled coding monkey.

    Monday, July 8, 2019 9:17 AM
  • >It did not work because I'm using ExecuteNonQuery after my insert.

    What prevent you to use ExecuteScalar? 

    ExecuteNonQuery returns a number of affected rows, not a last IDs...


    Sincerely, Highly skilled coding monkey.


    Monday, July 8, 2019 9:22 AM
  • Jack Thanks a lot.

    Worked perfectly. Exactly what I wanted. 

    Marked as correct answer.

    Monday, July 8, 2019 3:56 PM
  • Andrey,

    As I'm capturing the last ID, just after insert, I will use this ID utill the form is closed.

    That way I will prevent that multiple user gets the same ID.

    Do you have another suggestion or code to avoid this risk?

    Thanks for your concern and advise.

    best regards.

    Monday, July 8, 2019 4:00 PM
  • >capturing the last ID

    You capturing latest IDs on the table.

    It not the same as IDs for last row.

    You have to use scope_identity() to get IDs from last inserted row in the batch. This solve multi user problem. 


    Sincerely, Highly skilled coding monkey.

    Monday, July 8, 2019 4:43 PM
  • Hi Andrey,
    I understood now. 

    Can you help-me with the code. I´ve tried and didn't work.

    Here follows the code that is working now.

    Could you make the replacements and send me back, please ?

    I appreciate your help.

           private void btn_InserirPagto_Click(object sender, EventArgs e)
            {
    
                Data_Agora = DateTime.Now;
            //  data_LoginTextBox.Text = Data_Agora.ToShortDateString();
            //  login_NameTextBox.Text = Login.DadosGerais.Loginusuario;
                btn_salvar.Enabled = true;
                btn_salvar.BackColor = Color.DarkOrange;
                string sql = "SELECT MAX(Cod_Base_Titulos_Pagos) FROM Base_Titulos_Pagos";
                if (data_PagamentoDateTimePicker1.Text != " " && valor_PagoTextBox1.Text != " " && banco_DebitadoTextBox1.Text != " " && numero_ChequeTextBox1.Text != " ")
                {
                    try
                    {
                        cmd = new SqlCommand("INSERT INTO Base_Titulos_Pagos(Data_Pagamento, Total_Pago, Banco_Debitado, Numero_Cheque, Historico, Data_Login, Login_Name)"
                        + "VALUES(@Data_Pagamento, @Total_Pago, @Banco_Debitado, @Numero_Cheque, @Historico, @Data_Login, @Login_Name)", conn);
                        conn.Open();
                        DateTime DataPagamento = Convert.ToDateTime(data_PagamentoDateTimePicker1.Value.ToShortDateString());
                        cmd.Parameters.AddWithValue("@Data_Pagamento", DataPagamento);
                        decimal TotalPago = Convert.ToDecimal(valor_PagoTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Total_Pago", TotalPago);
                        Int32 BancoDebitado = Convert.ToInt32(banco_DebitadoTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Banco_Debitado", BancoDebitado);
                        String NumeroCheque = Convert.ToString(numero_ChequeTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Numero_Cheque", NumeroCheque);
                        String Historico = Convert.ToString(historicoTextBox1.Text);
                        cmd.Parameters.AddWithValue("@Historico", Historico);
                        DateTime Data_Agora = DateTime.Now;
                        cmd.Parameters.AddWithValue("@Data_Login", Data_Agora);
                        String LoginName = Login.DadosGerais.Loginusuario;
                        cmd.Parameters.AddWithValue("@Login_Name", LoginName);
                        Int32 result = cmd.ExecuteNonQuery();
                        MessageBox.Show("Inicie a Seleção de Títulos para Pagamentos! " + result.ToString() + "Cheque Inserido!");
                        cod_Base_Titulos_PagosTextBox1.Refresh();
                  //    Here I select the Last ID from the table tha was Inserted above and pass to the textboxes.
                        SqlCommand command = new SqlCommand(sql, conn);
                        var UltimoID = command.ExecuteScalar();
                        int LastID = Convert.ToInt32(UltimoID);
                        cod_Base_Titulos_PagosTextBox.Text = Convert.ToString(LastID);
                        cod_Base_Titulos_PagosTextBox1.Text = Convert.ToString(LastID);
                        cod_Base_Titulos_PagosTextBox.Refresh();
                        numero_Parcelas_a_PagarDataGridView.Enabled = true;
                    }
                    catch (Exception Ex)
                    {
                        MessageBox.Show("Erro! " + Ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                        btn_fechar.Enabled = true;
                    }
                }
                else
                {
                    MessageBox.Show("Informe todos os Dados necessários para incluir o Pagamento do Título!");
                }
    
            }
    

    Tuesday, July 9, 2019 7:31 PM
  • You already have an example from Viorel_.

    Just pickup an idea and debug your code.

    My example would not help you - I would implement separate class with IDbCommand interface for sql-command, handle all parameters in it and remove 90% of your code. As addition would add an something like interface IReturnRowIDs and make executional system to deal with this type of the commands. This too far from what needed for you. 


    Sincerely, Highly skilled coding monkey.

    Wednesday, July 10, 2019 8:30 AM
  • Andrey, 

    Thank you very much anyway.

    I'm just starting. I'll get there later. Step by step.

    best regards.

    Thursday, July 11, 2019 12:02 AM