Solved

Calling a parametised SQL Stored Procedure from an expression

Hi, I am trying to call the following SQL Server stored procedure (note I will be looking to add additional parameters; this version is a basic version to make it easier to troubleshoot):

  1. USE [StockControlDemo]

    GO

    /****** Object: StoredProcedure [dbo].[stpr_WPC_UpdatePOReply] Script Date: 28/03/2024 10:55:57 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[stpr_WPC_UpdatePOReply]

    -- Add the parameters for the stored procedure here

    @eComments as varchar(200) = NULL

    AS

    BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    UPDATE dbo.SupplierProductionScheduleReplies SET Comments = @eComments WHERE id = 39906;

    END

I am using this code in an expression:

  1. var strcnx=CHelper.getParameterValue("SVxxSAGExx-xxxXXXX");

    var eComments = <m_WPC_WeeklyProductChase.idm_WPC_POCheck.eKPComments>

    var connection = new System.Data.SqlClient.SqlConnection();

    var cmd = new SqlCommand("dbo.stpr_WPC_UpdatePOReply");

    connection.ConnectionString = strcnx;

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Clear();

    cmd.Parameters.Add("@eComments", SqlDbType.VarChar, 200).Value = eComments;

    try{

    //running a stored procedure

    connection.Open();

    cmd.Connection = connection;

    cmd.ExecuteNonQuery();

    }

    catch(exc)

    {

    CHelper.ThrowValidationError(exc);

    }

    finally

    {

    connection.Close();

    }

The problem I am getting is an error message saying too many parameters are being passed.

When I run the SQL Server profile, the trace shows that parameter @eComments being passed with a null value, and also with the value I am looking to pass.

I have seen another topic, but it refers to a different system (What is the best method to connect and execute store procedure for AS400 using Bizagi?)

Please note, as mentioned above, I will be looking to improve this code to pass at least 7 other parameters, of types date, int and bit.

Can you advise what I am doing wrong in passing the parameter in the code above?

Comments (2)

photo
1

Dear Mick,

Thank you for contacting us. After looking into the code you sent, we would advise that you try the same code in a different component since that code is not Bizagi native, it would be best to connect it through a channel that is not Bizagi directly. One of the best way to deal with errors like this is to write different code as part of a component library. You can learn more about component libraries in this article: https://help.bizagi.com/platform/en/index.html?component-library-guidelines.htm

Kind regards,

Laura G

photo
1

Hi Laura, thanks for the response. I am not familiar with component libraries, but will have a look at the link. Can you point me towards a version of the above in Bizagi native code? Or give me a link to something so that I can convert it to Bizagi native code, please? (I thought I had written it in Bizagi native code - maybe I am missing something?) Regards, Mick

photo