Using External Content Types with Stored Procedures with Input Parameters
You must have tried External Content Types - the exciting new feature in SharePoint 2010 to connect to an external SQL table and provide a list where it lists, creates, updates data straight into the SQL table.
People always wonder, how do you get a ECT to work with a Stored Procedure, one that has input parameters.
You could do it this way...
Step 1: In your SharePoint Designer 2010, create a new external content type
Step 2: Connect to your SQL Server and then make sure you see the Routines (your stored procedures must be listed here)
Step 3: Rt Click on the Stored Procedure name and create operations for "Read Item" and "Read List" - both are required for the External Content Type's SharePoint list to be created.
During both these wizards, in the Input Parameters, your SP's Input Parameter will be listed. Here, you need to create a filter, which sends out it's value to the SP's filter. For example, if your SP's Input Parameter is @EmpID, and your SP is having a Select * from Employees where ID is present, you need to create a filter for ID, which maps to @EmpID.
Step 4. Create a WebPart Page and add the "Business Data Connectivity List" and in the Filters, you will see your Filter... there you specify the value for the Filter and it will be sent right back to your Input Parameter and bingo, you will find the data for the list being bound.
Screenshots below:
People always wonder, how do you get a ECT to work with a Stored Procedure, one that has input parameters.
You could do it this way...
Step 1: In your SharePoint Designer 2010, create a new external content type
Step 2: Connect to your SQL Server and then make sure you see the Routines (your stored procedures must be listed here)
Step 3: Rt Click on the Stored Procedure name and create operations for "Read Item" and "Read List" - both are required for the External Content Type's SharePoint list to be created.
During both these wizards, in the Input Parameters, your SP's Input Parameter will be listed. Here, you need to create a filter, which sends out it's value to the SP's filter. For example, if your SP's Input Parameter is @EmpID, and your SP is having a Select * from Employees where ID is present, you need to create a filter for ID, which maps to @EmpID.
Step 4. Create a WebPart Page and add the "Business Data Connectivity List" and in the Filters, you will see your Filter... there you specify the value for the Filter and it will be sent right back to your Input Parameter and bingo, you will find the data for the list being bound.
Screenshots below:
I have a table "spstrial" which consists of 2 parameters "Name"(varchar 20) and "gender"(varchar 6). I have made a stored procedure names "spspstrial"
ReplyDeletecreate procedure spspstrial
@name varchar(20),@gender varchar(6)
as
begin
insert into spstrial values(@name,@gender)
select * from spstrial
end
then i compile it and it compiled successfully. then i execute the stored procedure as exec spspstrial 'sidhanta','male'.Finally it executed sucessfully. Then in ect i create all type of operation for that stored procedure.When i create external list from that external stored procedure it gives error "The query against the database caused an error".From event viewer i come to know that " Cannot insert the value NULL into column 'Name', table
'SidhantaDB.dbo.spstrial'; column does not allow nulls. INSERT fails." . Can anyone please share their ideas with me.
With Thanks & Regards
Sidhanta Tripathy
Hey - thanks for this post - it saved me a bunch of time! You don't happen to have any tricks to pass those filter parms from another WebPart - would you?
ReplyDeleteHi,
ReplyDeleteThe Business Data Connectivity List is not available in category section. Please advise me how could i get that option.
Thanks.
no deposit bonus forex 2021 - takipçi satın al - takipçi satın al - takipçi satın al - takipcialdim.com/tiktok-takipci-satin-al/ - instagram beğeni satın al - instagram beğeni satın al - google haritalara yer ekleme - btcturk - tiktok izlenme satın al - sms onay - youtube izlenme satın al - google haritalara yer ekleme - no deposit bonus forex 2021 - tiktok jeton hilesi - tiktok beğeni satın al - binance - takipçi satın al - uc satın al - finanspedia.com - sms onay - sms onay - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - perde modelleri - instagram takipçi satın al - instagram takipçi satın al - cami avizesi - marsbahis
ReplyDeletemmorpg oyunlar
ReplyDeleteinstagram takipçi satın al
tiktok jeton hilesi
tiktok jeton hilesi
antalya saç ekimi
referans kimliği nedir
İNSTAGRAM TAKİPÇİ SATIN AL
Mt2 pvp serverlar
TAKİPCİ SATIN AL
özel ambulans
ReplyDeletelisans satın al
yurtdışı kargo
en son çıkan perde modelleri
nft nasıl alınır
uc satın al
en son çıkan perde modelleri
minecraft premium
I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it upEmbedded Systems Course in Hyderabad
ReplyDelete"Looking to kickstart your career in SAP EWM? Bangalore offers a plethora of options for SAP EWM training.
ReplyDeleteHere’s what to consider:
SAP EWM Training in Bangalore at SAP Masters
1.Quality Training:
Opt for best institutes with a solid reputation and accreditation from SAP Masters Training institute.
Look for expert faculty who can provide comprehensive insights.
2.Curriculum:Ensure the curriculum covers core EWM concepts and offers hands-on projects for practical learning.
3.Infrastructure: Check for modern facilities and labs equipped with the latest software to support your training needs.
4.Placement Support: Choose institutes that offer robust placement assistance, including resume building and interview preparation.
Consider institutes like SAP Masters Institute of Technology, sap masters Academy, and sapmasters training institute bangalore,
known for their quality training and successful placements. Choose wisely, and jumpstart your SAP EWM journey in Bangalore!
Visit SAP Masters - Best SAP Training in Bangalore"
Visit SAP Masters - SAP EWM Training in Bangalore"