实体框架代码首先存储Proc错误语法/必须声明标量变量 [英] Entity Framework Code First Stored Proc Incorrect Syntax / Must Declare Scalar Varible

问题描述

正在使存储过程从Web应用程序执行。我可以验证值是否应该像应有的那样填充，但我要么得到两个错误之一。

Working on getting a stored procedure to execute from a web app. I can verify that the values are being populated like the should but I either get one of two errors.

这里是相关代码，我也环顾四周并尝试了

Here is the relevant code, I have also looked around and tried a few different solutions, just stuck as to why it's not working.

// incorrect syntax near newCompany - have tried with both the parameter1 and parameter1.Value, parameter2 and parameter2.Value
public virtual int usp_TransferRecords(int oldCompany, int newCompany)
{
    SqlParameter parameter1 = new SqlParameter("OldCompany", oldCompany);
    SqlParameter parameter2 = new SqlParameter("NewCompany", newCompany);
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("usp_TransferRecords @OldCompany @NewCompany", parameter1, parameter2);
}

// Message = "Must declare the scalar variable \"@OldCompany\"." - have tried with both the parameter1 and parameter1.Value, parameter2 and parameter2.Value
public virtual int usp_TransferRecords(int oldCompany, int newCompany)
{
    SqlParameter parameter1 = new SqlParameter("OldCompany", oldCompany);
    SqlParameter parameter2 = new SqlParameter("NewCompany", newCompany);

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("usp_TransferRecords @OldCompany, @NewCompany", parameter1, parameter2);
}

这是存储过程：

CREATE PROCEDURE [dbo].[usp_TransferRecords]
    @OldCompany int,
    @NewCompany int
AS
BEGIN
    -- delete existing records from new company
    DELETE FROM [dbo].[Table] WHERE CompanyID = @NewCompany

    -- copy records from old company to new new company
    INSERT INTO [dbo].[Table] ([ID], [CompanyID], [City], [State], [Priority])
        SELECT NEWID(), @NewCompany, City, State, Priority
        FROM [dbo].[TABLE]
        WHERE CompanyID = @OldCompany

    -- reset record pages for both new and old companies
    SET NOCOUNT ON;

    -- deletes Dynamic Content Cache for both new and old companies
    DELETE FROM [dbo].[Table]
    WHERE CompanyID IN (@OldCompany, @NewCompany) 
      AND (ContentKey LIKE '%-generic-service-page' OR
           ContentKey LIKE '%-item-custom-service-page' OR
           ContentKey LIKE '%-about-page')

    -- deletes contents to so they will be regenerated
    DELETE FROM [dbo].[TABLE]
    WHERE CompanyID IN (@OldCompany, @NewCompany) 
      AND IsCityPage = 1
END

Web UI标记：<​​/ p>

Web UI markup:

@using (Html.BeginForm(null, null, FormMethod.Post, new { id = "Form", enctype = "multipart/form-data" }))
{
    <h2 style="text-align:center"> This copy Records from old Company to new Company</h2>
    <div class="centered" style="width:400px">
    @Html.TextBox("oldCompany", id, new { onkeyup = "this.value=this.value.replace(/[^0-9]/g,'')", watermark = "Old Company #", @class = "watermark", style = "display:inline-block;" })
    @Html.TextBox("newCompany", String.Empty, new { onkeyup = "this.value=this.value.replace(/[^0-9]/g,'')", watermark = "New Company #", @class = "watermark", style = "display:inline-block;" })
    <input type="submit" name="submitButton" value="Copy" class="right orangebutton" style="position:absolute;left:62%;" />
    </div>
}

我通过更改以下内容使其正常工作

I got it to work properly by changing the following

// Original
public virtual int usp_TransferRecords(int oldCompany, int newCompany)
{
    SqlParameter parameter1 = new SqlParameter("OldCompany", oldCompany);
    SqlParameter parameter2 = new SqlParameter("NewCompany", newCompany);
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("usp_TransferRecords @OldCompany, @NewCompany", parameter1, parameter2);
}

至：

// Working
public virtual int usp_TransferRecords(int oldCompany, int newCompany)
{
   var @params = new SqlParameter[]
   {
       new SqlParameter("OldCompany", oldCompany),
       new SqlParameter("NewCompany", newCompany)
   };
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("usp_TransferRecords @OldCompany, @NewCompany", @params);
}

如果有任何见解可以说明为什么此方法优于其他原始方法我很想找出答案。谢谢所有回答。

If any has any insight as to why this method worked over the other original one I would love to find out. Thanks all who replied.

推荐答案

// Message = "Must declare the scalar variable \"@OldCompany\"." - have tried with both the parameter1 and parameter1.Value, parameter2 and parameter2.Value
public virtual int usp_TransferRecords(int oldCompany, int newCompany)
{
    SqlParameter parameter1 = new SqlParameter("OldCompany", oldCompany);
    SqlParameter parameter2 = new SqlParameter("NewCompany", newCompany);
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("usp_CopyRecord @OldCompany, @NewCompany", parameter1, parameter2);
}

您的错误消息告诉您问题所在。

Your error message tells you the problem.

必须声明标量变量\ @ OldCompany\。

"Must declare the scalar variable \"@OldCompany\"."

"@OldCompany" != "OldCompany"

这篇关于实体框架代码首先存储Proc错误语法/必须声明标量变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！