Question posted in
The official documentation can be found here.

Asp.net – dapper must decalre Must declare the scalar variable "@pDBMode"

three call GetDetailData，but only "BOML" call when error Must declare the scalar variable "@pDBMode",other call not error

string SQLStr = "Exec PR_SPSA03_Qry @pDBMode, @pDBKind, @pCompNo, @pProdNo, @pflow, @pSeq "
add space "@pDBMode ," , after not error

What is the reason?


switch (DBKind)
{
    case "BOML":
        bls = GetDetailData<BOMDTO>(DBKind, ProdNo, ref boms, ref Msg);
        if (bls)
        {
            HF_BData.Set("BOML", JsonConvert.SerializeObject(boms));
            grid_BOM.DataSource = boms;
            grid_BOM.DataBind();
        }
        break;
    case "PDML":
        bls = GetDetailData<PDMDTO>(DBKind, ProdNo, ref pdms, ref Msg);
        if (bls)
        {
            HF_BData.Set("PDML", JsonConvert.SerializeObject(pdms));
            grid_PDM.DataSource = pdms;
            grid_PDM.DataBind();
        }
        break;
    case "PDFL":
        bls = GetDetailData<PDFDTO>(DBKind, ProdNo, ref pdfs, ref Msg);
        if (bls)
        {
            HF_BData.Set("PDFL", JsonConvert.SerializeObject(pdfs));
            grid_PDF.DataSource = pdfs;
            grid_PDF.DataBind();
        }
        break;
}
private bool GetDetailData<T>(string DBKind, string ProdNo, ref List<T> Data, ref string Msg)
{
    string SQLStr = "Exec PR_SPSA03_Qry @pDBMode, @pDBKind, @pCompNo, @pProdNo, @pflow, @pSeq ";
    Function fn = new Function();
    DynamicParameters dynParams = new DynamicParameters();
    dynParams.Add("@pDBMode", "B");
    dynParams.Add("@pDBKind", DBKind);
    dynParams.Add("@pCompNo", Session["CompNo"].ToString());
    dynParams.Add("@pProdNo", ProdNo);
    dynParams.Add("@pflow", "");
    dynParams.Add("@pSeq", "");
    bool bls = fn.GetData<T>("SPS", SQLStr, dynParams, ref Data, ref Msg);
    return bls;
}

public bool GetData<T>(string ConStr, string SqlStr, DynamicParameters parameters, ref List<T> ReturnData, ref string Msg)
{
    bool bls = false;
    ConStr = GetConStr(ConStr);
    try
    {
        using (SqlConnection conn = new SqlConnection(ConStr))
        {
            conn.Open();
            ReturnData = conn.Query<T>(SqlStr, parameters).ToList();
        }
        bls = true;
    }
    catch (Exception ex)
    {
        Msg = ex.Message;
    }
    return bls;
}

add spapec "@pDBMode ," , after not error

What is the reason?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    look sql profiler not ok check enter image description here

    ok call check enter image description here


  2. 0

    The error "Must declare the scalar variable ‘@pDBMode’" typically occurs when you’re trying to execute a SQL query that includes a parameter, but the parameter has not been properly declared or assigned a value before the query is executed. In the context of using Dapper with C#, this error can happen if the DynamicParameters object does not include the parameter you’re trying to use in your SQL query, or if there’s a mismatch between the parameter names in your query and those added to the DynamicParameters object.

    From the provided code snippet, it seems like you’re correctly adding the @pDBMode parameter to the DynamicParameters object before executing the query. However, the error suggests that there might be an issue with how the parameter is being recognized or used within the SQL query.

    Here are a few steps to troubleshoot and potentially resolve this issue:

    1. Ensure Parameter Names Match: Double-check that the parameter names in your SQL query exactly match those you’re adding to the DynamicParameters object. Parameter names are case-sensitive.

    2. Check for Typos: A common cause of this error is a simple typo in the parameter name either in the SQL query or in the DynamicParameters object.

    3. Parameter Value Assignment: Ensure that the value you’re assigning to @pDBMode is correct and that it’s being recognized as a scalar value. In your code, you’re assigning "B" to @pDBMode, which seems correct assuming the stored procedure expects a scalar value.

    4. Stored Procedure Expectation: Verify that the stored procedure PR_SPSA03_Qry indeed expects a parameter named @pDBMode. If the stored procedure does not expect this parameter, or if it expects a different type of parameter, you’ll need to adjust your query or the stored procedure accordingly.

    5. DynamicParameters Usage: Your usage of DynamicParameters seems correct, but ensure that the DynamicParameters object is being passed correctly to the Query method of Dapper.

    6. Debugging: Add debugging statements before the query execution to print out the SQL query and the parameters being passed. This can help identify if the issue is with the query itself or with how the parameters are being passed.

    If after these checks the issue persists, consider isolating the problem by executing a simple query with just the @pDBMode parameter to ensure that the issue is not with the stored procedure or the database connection.

    Here’s a simplified example of how you might execute a query with Dapper and DynamicParameters:

    using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    var parameters = new DynamicParameters();
    parameters.Add("@pDBMode", "B");
    var result = conn.Query<YourResultType>("Your SQL Query Here", parameters);
}

    Ensure that "Your SQL Query Here" is replaced with your actual SQL query and YourResultType is replaced with the type of data you’re expecting to retrieve.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search