How to return output varchar from a precedent in c#?

Asked

Viewed 837 times

1

Error that appears:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Error converting data type varchar to int.

public static string CriarPessoa(string procedureName, 
                                 string tableName, 
                                 string nome, 
                                 double cpf)
{

       string returnStringOutput;

       SqlCommand sqlComando = ConexaoComParametro(procedureName);
      
       sqlComando.Parameters.Add(new SqlParameter("@nome", nome));
       sqlComando.Parameters.Add(new SqlParameter("@cpf", cpf));
       sqlComando.Parameters.Add(new SqlParameter("@outputmsg", DbType.String))
                                               .Direction = ParameterDirection.Output;

       SqlParameter outputmsg = sqlComando.Parameters.Add("@ouputmsg", DbType.String);
       outputmsg.Direction = ParameterDirection.ReturnValue;
       sqlComando.ExecuteNonQuery();
       returnStringOutput = (string)sqlComando.Parameters["@ouputmsg"].Value;    
       return returnStringOutput;

}

Procedure:

ALTER PROCEDURE [dbo].[sp_c_funcionario]

@operacao [char](1),
@nome [varchar](20),
@cpf [bigint],
@outputmsg [varchar](50) OUTPUT

AS

IF EXISTS (SELECT 1 FROM [dbo].[Funcionario] WHERE [cpf]=@cpf)
BEGIN

SET @outputmsg = 'Lamento, mas esse CPF já existe'

END
ELSE
BEGIN


IF @operacao='c'
BEGIN
    INSERT INTO [dbo].[Funcionario] ([nome], [cpf])
    VALUES (@nome,@cpf)

END 

END
RETURN
GO
asp.net-mvc sql-server stored-procedures

  • What is the line of error in the code C#?

  • @israel3D - The sp_c_funcionario procedure has 3 input parameters: @operation, @ name and@Cpf. It seems to me that the first parameter, @operation, was not reported. I could check?

  • @israel3D - In the Funcio table, how is the Cpf column declared? The error message, returned by SQL Server, is "Error Converting data type varchar to int". In the Creat Person procedure, the Cpf parameter would not be Int64?

  • No error noted in the code, only in the return of the bank @Virgilionovic

  • Actually @Josédiz, missed the operation. But in the code is right, it was only a copy/Paste error when publishing the question. And the error itself is that it does not return my output. All parameters arrive correctly in the past.

1 answer

1


Grateful for everyone’s help, but I was able to solve the problem: It was precisely because I put "(new Sqlparameter)" before, when passing the output parameter. It was as follows:

public static string CriarPessoa(string procedureName, char operacao, string nome, double cpf)
    {


        //INSTANCIO O PARÂMETRO ANTES DE PASSAR
        SqlParameter outputmsg = new SqlParameter();
        outputmsg.ParameterName = "@outputmsg";
        outputmsg.Direction = ParameterDirection.Output;
        outputmsg.DbType = DbType.String;
        outputmsg.Size = 2000;

        string returnStringOutput;

        SqlCommand sqlComando = ConexaoComParametro(procedureName);//faz conexão
        sqlComando.Parameters.Add(new SqlParameter("@operacao", operacao));
        sqlComando.Parameters.Add(new SqlParameter("@nome", nome));
        sqlComando.Parameters.Add(new SqlParameter("@cpf", cpf));
        sqlComando.Parameters.Add(outputmsg); //AGORA ESTÁ ASSIM, SEM O "(new SqlParameter) COMO NO EXEMPLO ACIMA DO CPF"

        sqlComando.ExecuteNonQuery();

        outputmsg = sqlComando.Parameters["@outputmsg"];

        returnStringOutput = outputmsg.Value.ToString();

        return returnStringOutput;

    }

Browser other questions tagged asp.net-mvc sql-server stored-procedures

You are not signed in. Login or sign up in order to post.