Hi Fabian,

Please post the code for the GetNextNumber method as well as any stored procedure being called or TSQL code.

Edhy Rijo

basically it receive a data from the bo to be saved (idTalonario) and with that execute a max(number) from the invoice table to get the max value for that idTalonario

I have to call this method twice, one for invoice BO and another time for the payments BO, the first time it executes (I suppose that it are out of transaction, but return value) and the 2nd time (for payments) it lock and give me timeout

I see in ms help that the "executescalar" (for ado.net?) have a IDbTransaction that can be passed... I doen't know how I can pass something that tell the executescalar the transation here...

thanks Edhy!

update:I see that I'm calling the scalar on a new object... 

if I pass the BO as a parameter to that method and call the executescalar method over it, that can take care of the transaction? (or can I do something like that that call the execute scalar on the transaction...)

thanks!

-----------------------------------

code:

public static int GetHastaNumeroProximo(int idTalonario, string dataSourceKey){

            int result;

            var campoHastaNumero = ComprobantesBOFieldNames.HastaNumero.ToString();

            var campoNumero = ComprobantesBOFieldNames.Numero.ToString();

            var campoIdTalonario = ComprobantesBOFieldNames.IdTalonario.ToString();

            using (var bo = new ComprobantesBO() { DataSourceKey = dataSourceKey })

            {

                // select (max)numero from comprobantes where IdTalonario= Comprobantes.IdTalonario and anulado=0

                var sql = string.Format("select max({0}) from {1} where {2} = @valor", campoHastaNumero, bo.TableName, campoIdTalonario);

                var sqlCmd = new SqlCommand(sql);

                sqlCmd.Parameters.Add("@valor", SqlDbType.VarChar).Value = idTalonario;

                var res = bo.ExecuteScalar(sqlCmd);

                result = (res == null) || res == DBNull.Value ? 1 : ((int)res) + 1;

            } // end using

            return result;

}

Edhy Rijo (8/13/2012)
Hi Fabian,

Please post the code for the GetNextNumber method as well as any stored procedure being called or TSQL code.

The GetHastaNumeroProximo method should be in the ComprobantesBO or if you have a BO Base class, then create it in that class and pass the parametes of the fields to use in the SELECT MAX() statement.
I am not a C# developer, but here is the version I came up with:

        public static int GetHastaNumeroProximo(int idTalonario)
         {
             int result;
             using (var sqlCmd = new SqlCommand())
             {
                 // select (max)numero from comprobantes where IdTalonario= Comprobantes.IdTalonario and anulado=0
                 var sql = string.Format("select max({0}) from {1} where {2} = @valor", ComprobantesBOFieldNames.HastaNumero.ToString, this.TableNameAndSchema, ComprobantesBOFieldNames.IdTalonario.ToString);
                 sqlCmd.CommandTimeout = 0; // Added this to avoid taking to much time, also make sure your tables are properly indexed for the SQL condition
                 sqlCmd.CommandText = sql;
                 sqlCmd.Parameters.Add("@valor", SqlDbType.Int).Value = idTalonario; //Also noticed that this parameter should be Integer and not String as you had it originaly.
                 var res = this.ExecuteScalar(sqlCmd);
                 result = (res == null) || res == DBNull.Value ? 1 : ((int)res) + 1;
             } // end using
             return result;
         }

Edhy Rijo

-

I see that I was wrong on what I tried to accomplish, I will explain better.