Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot
my sproc:
CREATE PROCEDURE ng_AddCotacao
(
...
@.Codigo_cotacao int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO
Negocios_cotacoes
(
...
)
VALUES
(
...
)
SELECT @.Codigo_cotacao=SCOPE_IDENTITY()
SET NOCOUNT OFF
END
GO
class file
public
class Cotacoes{
publicint codigoCotacao;
}
public class CotacaoAtualiza
{
public Cotacoes cotacoes = new Cotacoes();
public CotacaoAtualiza()
{
}
public void AdicionarCotacao(
...
)
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]);
SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
...
SqlParameter paramCodigo_cotacao = new SqlParameter("@.Codigo_cotacao", SqlDbType.Int, 4);
paramCodigo_cotacao.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(paramCodigo_cotacao);
...
myConnection.Open();
SqlDataReader result = myCommand.ExecuteReader();
while(result.Read())
{
this.cotacoes.codigoCotacao = (int) result["@.Codigo_cotacao"];
}
myConnection.Close();
}
calling into code-behind file:
CotacaoAtualiza ca =
new CotacaoAtualiza();Cotacoes cotacoes = ca.cotacoes;
Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
You don't get the result in the resultset but rather in the parameter - ex:
System.Data.SqlClient.
SqlCommand Cmd =new System.Data.SqlClient.SqlCommand("AddSomething", oConn);Cmd.CommandType =
CommandType.StoredProcedure;System.Data.SqlClient.
SqlParameter oParam = Cmd.Parameters.AddWithValue("@.zonename", sName);oParam = Cmd.Parameters.AddWithValue(
"@.desc", sDesc);oParam = Cmd.Parameters.AddWithValue(
"@.TheNewId", 0);oParam.Direction =
ParameterDirection.Output;Cmd.ExecuteNonQuery();
oConn.Close();
int nTheNewId =Convert.ToInt32(Cmd.Parameters["@.TheNewId"].Value);