Update data using Entity model

Niketa Kapoor

How to force Entity Framework to work with stored procedure with XML input parameter? Data is Updating Perfectly with Sql connection but I only have to use Entity framework

I have tried ExecuteFunction and ExecuteStoreQuery both are giving different Error as below

Both statements are giving Error

return base.ExecuteFunction("pr_UpdateSupplierSubmittedExportdata_1", tblExportDataParameter, batchIDParameter, userNameParameter); ex = {"The stored procedure or function 'pr_UpdateSupplierSubmittedExportdata_1' does not have a return type. ExecuteFunction only supports stored procedures and functions that have a return type. "}

return base.ExecuteStoreQuery("Exec pr_UpdateSupplierSubmittedExportdata_1 @tblExportData, @BatchID, @UserName ", tblExportData, batchID, userName); Must declare the scalar variable "@tblExportData".

var getColumn = (from sp in (objLCSEntities.pr_UpdateSupplierSubmittedExportdata_1(xmlString1, Convert.ToInt32(sExportID), strUserFullName)) select sp).First().ToString();

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Niketa Kapoor7y ago
I am passing all parameters with same datatype.Still facing Error.
Munesh Sharma7y ago
if it is giving error for conversion then yoy have see the field data type in sql server and the passing value to that field .,may be its mitchmatch ...hence giving error.
Niketa Kapoor7y ago
Getting Error at Line that Convertion Error From Nvarchar to int

return base.ExecuteStoreQuery("exec pr_UpdateSupplierSubmittedExportdata_1 tblExportData, BatchID,UserName ", tblExportData, batchID, userName);

Below is my SP
CREATE PROCEDURE [UpdateSupplierSubmittedExportdata_1]

(

@tblExportData XML,

@BatchID INT,

@UserName varchar(200)

)

AS

BEGIN

SET NOCOUNT ON;

--UPDATE EXISTING RECORDS

-- import xml into temp table

-- Creating Temporary Table

CREATE TABLE #TEMP_TABLES

(

Updated_Accepted_Shipment_Date_EX_Works nvarchar(max),

Updated_Invoice_Number nvarchar(max),

Updated_Invoice_Date nvarchar(max),

Updated_Handover_Date nvarchar(max),

Updated_Revised_Delivery_Date_Ex_Works nvarchar(max),

Updated_PO_Line_Progress nvarchar(max),

Updated_Mode_of_Shipment nvarchar(max),

Updated_Supplier_Remarks nvarchar(max),

Updated_GSM_Remarks nvarchar(max),

Updated_Weight int,

Updated_Inspection_Offer_Date nvarchar(max),

Updated_Development nvarchar(max),

Updated_Transit_Qty int,

Order_x0020_ID int

);

-- insert into @temp

--select T.c.value('@CustomerID', 'nchar(5)'),

-- T.c.value('@ContactName', 'nvarchar(40)')

--from @XML.nodes('/ROOT/Customer') AS T(c)

INSERT INTO

#TEMP_TABLES

SELECT

(x.c.value('Updated_Accepted_Shipment_Date_EX_Works[1]', 'nvarchar(max)')),--as Updated_Accepted_Shipment_Date_EX_Works,

x.c.value('Updated_Invoice_Number[1]', 'nvarchar(max)'),-- as Updated_Invoice_Number,

x.c.value('Updated_Invoice_Date[1]', 'nvarchar(max)'),--as Updated_Invoice_Date,

x.c.value('Updated_Handover_Date[1]', 'nvarchar(max)'),-- as Updated_Handover_Date,

x.c.value('Updated_Revised_Delivery_Date_Ex_Works[1]', 'nvarchar(max)'),--as Updated_Revised_Delivery_Date_Ex_Works,

x.c.value('Updated_PO_Line_Progress[1]', 'nvarchar(max)'),--as Updated_PO_Line_Progress,

x.c.value('Updated_Mode_of_Shipment[1]', 'nvarchar(max)'),--as Updated_Mode_of_Shipment,

x.c.value('Updated_Supplier_Remarks[1]', 'nvarchar(max)'),--as Updated_Supplier_Remarks,

x.c.value('Updated_GSM_Remarks[1]', 'nvarchar(max)'),--as Updated_GSM_Remarks,

x.c.value('Updated_Weight[1]', 'int'),--as Updated_Weight,

x.c.value('Updated_Inspection_Offer_Date[1]', 'nvarchar(max)'),--as Updated_Inspection_Offer_Date,

x.c.value('Updated_Development[1]', 'nvarchar(max)'), --as Updated_Development

x.c.value('Updated_Transit_Qty[1]', 'int'), --as Updated_Development

x.c.value('Order_x0020_ID[1]', 'int') --as Updated_Development

FROM @tblExportData.nodes('/DocumentElement/MyTableName') as x(c)

--select * from #TEMP_TABLE232

--UPDATE EXISTING RECORDS

UPDATE dbo.tblExportOrderdata

SET Original_Ship_Dt = convert(datetime,c2.[Updated_Accepted_Shipment_Date_EX_Works])

,In_Transit_Qty = c2.[Updated_Transit_Qty]

,Invoice_No=c2.[Updated_Invoice_Number]

,Invoice_Date=c2.[Updated_Invoice_Date]

,Handover_Dt=c2.[Updated_Handover_Date]

,Rev_Delivery_Dt=c2.[Updated_Revised_Delivery_Date_Ex_Works]

,PO_line_progress=c2.[Updated_PO_Line_Progress]

,Mode_Of_Shipment=c2.[Updated_Mode_of_Shipment]

--,GSM_Buyer_Remark=c2.[Updated_GSM Remarks]

,Supplier_Remark=c2.[Updated_Supplier_Remarks]

,Inspection_Offer_Date=c2.[Updated_Inspection_Offer_Date]

,weight=c2.[Updated_Weight]

,Development=c2.[Updated_Development]

--,Status='Updated by Supplier'

FROM dbo.tblExportOrderdata c1

INNER JOIN #TEMP_TABLES c2

ON c1.Order_Id = c2.Order_x0020_ID and c1.Batch_ID=@BatchID

DROP table #TEMP_TABLES

UPDATE tblExportOrderdata SET Original_Ship_Dt=NULL where Original_Ship_Dt='1900-01-01 00:00:00.000' and Batch_ID=@BatchID

UPDATE tblExportOrderdata SET Invoice_Date=NULL where Invoice_Date='1900-01-01 00:00:00.000' and Batch_ID=@BatchID

UPDATE tblExportOrderdata SET Handover_Dt=NULL where Handover_Dt='1900-01-01 00:00:00.000' and Batch_ID=@BatchID

UPDATE tblExportOrderdata SET Rev_Delivery_Dt=NULL where Rev_Delivery_Dt='1900-01-01 00:00:00.000' and Batch_ID=@BatchID

UPDATE tblExportOrderdata SET Inspection_Offer_Date=NULL where Inspection_Offer_Date='1900-01-01 00:00:00.000' and Batch_ID=@BatchID

IF EXISTS

(select 1 from [LCS_QA].[dbo].[tblExportInbox]

where Batch_No=@BatchID AND Status='Submitted To Supplier' )

--and Fls_Site_Name IN (SELECT * FROM DBO.CSVTOTABLE(@FlsSiteName))

--and Country IN (SELECT * FROM DBO.CSVTOTABLE(@Country))) --

BEGIN

UPDATE [LCS_QA].[dbo].[tblExportInbox] SET Status='Updated By Supplier' , Updated_Date=getdate() , Updated_By=@UserName

WHERE Batch_No=@BatchID --AND Status='Submitted To Supplier'

END

DECLARE @ResultValue int

SET @ResultValue=-1

select @ResultValue

END
Madan Shekar7y ago
please go with debug mode put brak point bigining only and please let me know where you exactly getting error
Niketa Kapoor7y ago
I have done it Using the article but Now it is giving me error of convertion failure from nvarchar to int
public ObjectResult pr_UpdateSupplierSubmittedExportdata_1(global::System.String tblExportData, Nullable batchID, global::System.String userName)

{

ObjectParameter tblExportDataParameter;

if (tblExportData != null)

{

tblExportDataParameter = new ObjectParameter("tblExportData", tblExportData);

}

else

{

tblExportDataParameter = new ObjectParameter("tblExportData", typeof(global::System.String));

}

ObjectParameter batchIDParameter;

if (batchID.HasValue)

{

batchIDParameter = new ObjectParameter("BatchID", batchID);

}

else

{

batchIDParameter = new ObjectParameter("BatchID", typeof(global::System.Int32));

}

ObjectParameter userNameParameter;

if (userName != null)

{

userNameParameter = new ObjectParameter("UserName", userName);

}

else

{

userNameParameter = new ObjectParameter("UserName", typeof(global::System.String));

}

return base.ExecuteStoreQuery("exec pr_UpdateSupplierSubmittedExportdata_1 tblExportData, BatchID,UserName ", tblExportData, batchID, userName);

//return base.ExecuteFunction("pr_UpdateSupplierSubmittedExportdata_1", tblExportDataParameter, batchIDParameter, userNameParameter);

// return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery("exec Reason_Codes_Search_SP @SearchField, @SearchText ", searchFieldParameter, searchTextParameter);

// return base.ExecuteStoreQuery("Exec pr_UpdateSupplierSubmittedExportdata_1 tblExportData, BatchID,UserName", tblExportData, batchID, userName);

// return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery

//("exec Reason_Codes_Search_SP @SearchField, @SearchText ", searchFieldParameter, searchTextParameter);

}
Madan Shekar7y ago
http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

if this link helps you means pls accept the ans