Reputation: 3521
I'm using this same procedure on an old project and it has been working for a while, so i can't figure out what is wrong with this one and why it returns 0 when I insert.
IF OBJECT_ID('dbo.spInsertArtigo') IS NOT NULL
DROP PROCEDURE spInsertArtigo
GO
CREATE PROCEDURE spInsertArtigo
@IdUser INT,
----- hArtigos -----
@MotivoCriacao NVARCHAR(100) = NULL,
@CodeRequest TINYINT,
@Cliente TINYINT,
@ReferenciaCliente NVARCHAR(50),
@ReferenciaInterna NVARCHAR(7),
@Indice NVARCHAR(10),
@Projecto NVARCHAR(50),
@Desenho NVARCHAR(50),
@CadenciaMensal INT,
@NumCOMDEV NVARCHAR(50),
@QTDLancamentoFormas INT,
@CapacidadeReal NVARCHAR(20) = NULL,
@Observacoes NVARCHAR(100) = NULL,
----- hCaixa -----
@TipoCaixa NVARCHAR(50) = NULL,
@QTDPecasCaixa INT = NULL,
@UnidadeProducao NVARCHAR(50) = NULL,
@CelulaProducao NVARCHAR(50) = NULL,
@Peso FLOAT,
@NumKanbansProducao INT = NULL,
----- hMetodologiaProducao
@TipoMetodologia nchar(3) = NULL,
@QTDMetodoProducao INT = NULL,
@ID INT = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON
BEGIN
INSERT INTO hArtigos (MotivoCriacao, CodeRequest, Cliente, ReferenciaCliente, ReferenciaInterna, Indice, Projecto, Desenho,
CadenciaMensal, NumCOMDEV, QTDLancamentoFormas, CapacidadeReal, Observacoes, IdUser)
VALUES (@MotivoCriacao, @CodeRequest, @Cliente, @ReferenciaCliente, @ReferenciaInterna, @Indice, @Projecto, @Desenho,
@CadenciaMensal, @NumCOMDEV, @QTDLancamentoFormas, @CapacidadeReal, @Observacoes, @IdUser)
INSERT INTO hCaixa (TipoCaixa, QTDPecasCaixa, UnidadeProducao, CelulaProducao, Peso, NumKanbansProducao, IdArtigo)
VALUES (@TipoCaixa, @QTDPecasCaixa, @UnidadeProducao, @CelulaProducao, @Peso, @NumKanbansProducao, SCOPE_IDENTITY())
INSERT INTO hMetodologiaProducao(TipoMetodologia, QTDMetodoProducao, IdArtigo)
VALUES (@TipoMetodologia, @QTDMetodoProducao, SCOPE_IDENTITY())
INSERT INTO hData(DataCriacao, DataAlteracao, IdArtigo)
VALUES (GETDATE(), GETDATE(), SCOPE_IDENTITY())
SELECT @ID = SCOPE_IDENTITY()
END
END
This is where I call the procedure
public static INT InsertRequisicao(Requisicao req)
{
string constr = ConfigurationManager.ConnectionStrings["GestaoRequisicoes_ConnectionString"].ConnectionString;
string query = "spInsertArtigo";
try
{
using (SqlConnection con = new SqlConnection(constr))
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IdUser", HttpContext.Current.User.Identity.Name);
cmd.Parameters.AddWithValue("@MotivoCriacao", req.MotivoCriacao);
cmd.Parameters.AddWithValue("@CodeRequest", req.CodeRequest);
cmd.Parameters.AddWithValue("@Cliente", req.Cliente);
cmd.Parameters.AddWithValue("@ReferenciaCliente", req.ReferenciaCliente);
cmd.Parameters.AddWithValue("@ReferenciaInterna", req.ReferenciaInterna);
cmd.Parameters.AddWithValue("@Indice", req.Indice);
cmd.Parameters.AddWithValue("@Projecto", req.Projecto);
cmd.Parameters.AddWithValue("@Desenho", req.Desenho);
cmd.Parameters.AddWithValue("@CadenciaMensal", req.CadenciaMensal);
cmd.Parameters.AddWithValue("@NumCOMDEV", req.NumCOMDEV);
cmd.Parameters.AddWithValue("@QTDLancamentoFormas", req.NumLancamentoFormas);
cmd.Parameters.AddWithValue("@CapacidadeReal", req.CapacidadeReal);
cmd.Parameters.AddWithValue("@TipoCaixa", req.TipoCaixa);
cmd.Parameters.AddWithValue("@QTDPecasCaixa", req.NumPecasCaixa);
cmd.Parameters.AddWithValue("@UnidadeProducao", req.UnidadeProducao);
cmd.Parameters.AddWithValue("@CelulaProducao", req.CelulaProducao);
cmd.Parameters.AddWithValue("@Peso", req.Peso);
cmd.Parameters.AddWithValue("@NumKanbansProducao", req.NumKanbansProducao);
cmd.Parameters.AddWithValue("@TipoMetodologia", req.TipoMetodologia);
cmd.Parameters.AddWithValue("@QTDMetodoProducao", req.NumMetodoProducao);
cmd.Parameters.AddWithValue("@Observacoes", req.Observacoes);
con.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
catch (SqlException ex)
{
throw ex;
}
}
I think everything looks ok... Procedure is exactly as the same as the other and i don't think there's anything wrong on C# code
Upvotes: 1
Views: 109
Reputation: 31785
Instead of getting the RETURN value of ExecuteScalar(), you need to get the value of the output parameter @ID
Upvotes: 4