I'm getting this error whenever I try to insert a sale data into th database, I've been doing some research, but failed to get or to understand an answer.
This is the procedure on the DB:
CREATE PROC SPInsertar_Venta
@IDVentas int = NULL OUTPUT,
@NumeroVentas nvarchar(30),
@Fecha date,
@Estado nvarchar(10),
@IDCliente int
as
INSERT INTO Ventas VALUES(@NumeroVentas, @Fecha, @Estado, @IDCliente)
SET @IDVentas = @@IDENTITY
go
CREATE PROC SPInsertar_DetallesVenta
@Cantidad int,
@Descripcion nvarchar(256),
@Precio decimal,
@Impuestos decimal,
@Totales money,
@IDVentas int
as
INSERT INTO DetallesVenta VALUES(@Cantidad, @Descripcion, @Precio, @Impuestos, @Totales, @IDVentas)
go
This is the code that makes the insertion on the DB:
public string InsertSales(E_Sales _Sales, List<E_SaleDetails> _SaleDetails)
{
string answer;
try
{
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand
{
Connection = connection,
Transaction = sqlTransaction,
CommandText = "SPInsertar_Venta",
CommandType = CommandType.StoredProcedure
};
SqlParameter sqlParameterIDVentas = new SqlParameter
{
ParameterName = "@IDVentas",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output
};
command.Parameters.Add(sqlParameterIDVentas);
command.Parameters.AddWithValue("@NumeroVentas", _Sales.NumberOfSales);
command.Parameters.AddWithValue("@Fecha", _Sales.Date);
command.Parameters.AddWithValue("@Estado", _Sales.State);
command.Parameters.AddWithValue("@IDCliente", _Sales.IdClient);
answer = command.ExecuteNonQuery() == 1 ? "OK" : "No se pudo insertar la venta.";
if (answer.Equals("OK"))
{
_Sales.IdSales = Convert.ToInt32(command.Parameters["IDVentas"].Value);
foreach (E_SaleDetails details in _SaleDetails)
{
details.IdSales = _Sales.IdSales;
answer = d_SaleDetails.InsertSaleDetails(details, ref connection, ref sqlTransaction);
if (!answer.Equals("OK"))
{
break;
}
}
}
if (answer.Equals("OK"))
{
sqlTransaction.Commit();
}
else
{
sqlTransaction.Rollback();
}
}
catch (Exception ex)
{
answer = ex.Message;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return answer;
}
Lastly, here's the controller:
public static string InsertSales(E_Sales _Sales, DataTable _SaleDetails)
{
D_Sales objSales = new D_Sales();
List<E_SaleDetails> saleDetails = new List<E_SaleDetails>();
foreach (DataRow row in _SaleDetails.Rows)
{
E_SaleDetails details = new E_SaleDetails
{
Quantity = Convert.ToInt32(row["Cantidad"].ToString()),
Description = row["Descripcion"].ToString(),
Price = Convert.ToDecimal(row["Precio"].ToString()),
Taxes = Convert.ToDecimal(row["Impuestos"].ToString()),
Totals = Convert.ToDecimal(row["Totales"].ToString())
};
saleDetails.Add(details);
}
return objSales.InsertSales(_Sales, saleDetails);
}
What I have tried:
I've been excluding some parts of the code while trying to get to the problem itself, but then, as I said, I don't really get what I'm supposed to do now to fix the error. Any help is highly appreciated.