Click here to Skip to main content
Click here to Skip to main content

Tagged as

Design TypeDataSet from Storedprocedure using temptable

, 7 Nov 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Design TypeDataSet from Storedprocedure using temptable

I found one problem recently while designing TypeDataset in VisualStudio using storedprocedure which is making use of temporary table to get result.
 
Here is detail of the it what I did and how I resolved the issue. 

 
Step 1: Created Procedure with Temporary table

create PROCEDURE [dbo].[GetData]
AS
begin
   create TABLE #MyTable  (
  ID int,
  Name nvarchar(50) )
 
 INSERT INTO #MyTable (ID, Name)
 SELECT  PersonID, FirstName + ' ' + LastName
 FROM  dbo.Person
 
 SELECT ID,
  Name 
 FROM #MyTable
end
 

Step 2: Add TableAdapter in the design view of TypeDataSet and create database connection
 

Step 3: Create Procedure or Select Existing procedure

 

Step 4 : Select Procedure that I created already

Note : here it's not displaying DataColumn of select statement related to proceudre


Step 5 : Click on finish it display that in valid object #table


 
so it doesn't able to create tableadapter for procedure and display like this

 
Solution

To resolve this issue you can try one of the following solution , I tried first solution because its easy and not require to change in my existing storedprocedure code
 

Solution 1

Just add below line at top of the procedure after begin statement

SET FMTONLY OFF
This will resolve the issue and allow to create tableadapter easily without any error. So procedure will be
create PROCEDURE [dbo].[GetData]
AS
begin
  SET FMTONLY OFF
   //code of the procedure as above
end

Solution 2

To try this solution just create table variable instead of temporary table in procedure. So procedure will be

create PROCEDURE [dbo].[GetData]
AS
begin
  DECLARE @MyTable TABLE (
  ID int,
  Name nvarchar(50) )
 
 INSERT INTO @MyTable (ID, Name)
 SELECT  PersonID, FirstName + ' ' + LastName
 FROM  dbo.Person
 
 SELECT ID,
  Name 
 FROM @MyTable
end
 
After trying the above solution tableadapter on XSD file will be like this

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pranay Rana
Software Developer (Senior) GMind Solusion
India India

Microsoft C# MVP (12-13)


Hey, I am Pranay Rana, working as a ITA in MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.

For me def. of programming is : Programming is something that you do once and that get used by multiple for many years

You can visit my blog

StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr

Awards:


Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150414.1 | Last Updated 7 Nov 2012
Article Copyright 2012 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid