CODEWITHSUNDEEP
c#entity-frameworkstored-procedures.net-4.5sql-server-2012-express
TheColonel26
TheColonel26

Reputation: 2728

Getting error when calling Stored Procedure w/ Parameters

When I try to call my stored procedure using the a entity framework context, I get the following error at run time. "The parameterized query '(@Mac nvarchar(30),@tagList [TagListType] READONLY)EXEC SaveMsg' expects the parameter '@Mac', which was not supplied."

on the line 

var result = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)databaseAPI).ObjectContext.ExecuteStoreCommand(command, new SqlParameter("@Mac",SqlDbType.NVarChar,30,reader.MAC),sqlParamTags);

Here is the rest of the relevant code.

DataTable dtTags = getDataTableTags(info.TagList.ToList());

//**call stored procedure

SqlParameter sqlParamTags = new SqlParameter("tagList", SqlDbType.Structured);
sqlParamTags.Value = dtTags;
sqlParamTags.TypeName = "TagListType";



string command = "EXEC " + "SaveMsgData" + " @Mac, @tagList;";

var result = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)databaseAPI).ObjectContext.ExecuteStoreCommand(command, new SqlParameter("@Mac",SqlDbType.NVarChar,100,reader.MAC),sqlParamTags);

Here is my Stored Procedure

CREATE TYPE TagListType AS TABLE
(                     
    TagID nvarchar(100),                
    AntNum int
);
GO

CREATE PROCEDURE SaveMsgData 
@Mac nvarchar(30),
@tagList TagListType READONLY
AS

DECLARE @ReaderID int

DECLARE @AntList TABLE (AntennaID int, AntNum int, TargetNumOfTags int)

--SELECT @ReaderID = -1

SELECT @ReaderID = Id
    FROM dbo.Readers
    WHERE MAC = @Mac

IF (@ReaderID >= 0)
BEGIN
    INSERT INTO @AntList (AntennaID,Antnum,TargetNumOfTags) 
    SELECT id, AntennaNumber, numOfTags 
    FROM Antennae
    WHERE ID = @ReaderID

    DECLARE @count int
    DECLARE @len int                    
    SET @count = 0
    SET @len = 0

    WHILE @count <= @len
    BEGIN
        DECLARE @LocalAnt int
        DECLARE @LocalTarget int
        DECLARE @LocalAntID int
        DECLARE @LocalStatus int
        DECLARE @LocalTagsRead int

        --set local variables need in loop--
        SELECT @LocalAnt = Antnum, @LocalTarget = TargetNumOfTags, @LocalAntID = AntID FROM #AntList WHERE Antnum = @count

        --Decided where reading passed or failed--
        SELECT @LocalTagsRead = COUNT(*) FROM @tagList WHERE AntNum = @count

        IF @LocalTagsRead = @LocalTarget
            BEGIN
                SET @LocalStatus = 0
            END
        ELSE
            BEGIN
                SET @LocalStatus = 1
            END

        --insert Tags in to tag table--
        DECLARE @ReadingID int

        INSERT INTO Readings (Status,TargetNumOfTags,numOfTagsRead, TimeStamp, Antenna_Id, Reader_Id) VALUES ( @LocalStatus, @LocalTarget, @LocalTagsRead, CURRENT_TIMESTAMP, @LocalAntID, @ReaderID) 

        SELECT @ReadingID = SCOPE_IDENTITY()

        --insert Tags in to tag table--
        INSERT INTO Tags (TagID) SELECT TagID FROM @tagList WHERE AntNum = @LocalAnt

        --insert Tags and Readings in to TagReading table--
        INSERT INTO TagReadings (Tag_Id, Reading_ID) SELECT TagID,  @ReadingID FROM @tagList WHERE AntNum = @LocalAnt 

        SET @count = @count + 1
    END
END
GO

Upvotes: 0

Views: 494

Answers (1)

mikeo
mikeo

Reputation: 1075

I don't think SqlParameter has an overload with the signature (Name, SqlDbType, Size, Value). The overload you're using is expecting "SourceColumn" as the final parameter. So, you're creating the parameter but not specifying it's value.

You're going to need to change it to something like this:

new SqlParameter("@Mac", reader.MAC);

Reference: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx

Upvotes: 1

Related Questions