.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Entity Model- Add Function Import - selected stored procedure returns no columns

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :ASP.Net
I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

View Complete Post

More Related Resource Links

Entity Data Model and database view returning the same columns as there are in a table


When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?

I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.

var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...

Thanks, Jenda

EF 4: Function Import problem with Stored Procedures


I have the following SP which includes a temp table:

 DECLARE @pos int

--create table to hold parsed values
CREATE TABLE #list (val varchar

Entity Framework 4.0 Dynamic Data - stored procedure


I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.

Insert the results of a stored procedure into specific columns

Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

using stored procedures in ado.net entity framework model

Hi, I have a issue, i am calling stored procedure through entity model like result=ctx.spname(parmas), i am expecting 0 or 1 as a result after the execution of sp, but its returning unknown values like 178 for number records. Please suggest what went wrong Thanks

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

stored procedure returns numbers ()

I would like to write a stored procedure which will return some numbers based on query, and it will go something like this: - find an id of a person, if his username exists - if  id is not found retun 0 - else if the id is found, look for person`s password - if password is NOT found return 1 - else if password IS found return 2   I did some coding, but it seems its not working somehow. It always retuns null value: ps: It is not important if I use "IF EXISTS", it can also be used "IF IS NOT NULL". And I tried to use if its null or not, but I got the same result - nothing (only null)   ALTER PROCEDURE myStoredProcedure01 ( @strValue varchar(50) ) AS BEGIN SET NOCOUNT ON IF EXISTS(SELECT PersonID FROM Person WHERE UserName = @strValue) BEGIN DECLARE @password varchar(50) SELECT @password = Password FROM Person WHERE UserName = @strValue IF(@password IS NULL ) RETURN 2 ELSE RETURN 1 END ELSE RETURN 0 END    

How do I use a GridView with a Stored Procedure with changing columns?


I have a stored procedure that returns a dataset with a different number of columns and different column names depending on user input.  How can I use this with a GridView?  Normally I define the columns beforehand in a static fashion, but, have never done a dynamic dataset like this.

Stored procedure and table value function


I have a function called F_TBL_VALS_FROM_STRING this function returns a table and uses '|' as a delimiter to distinguish between values.
this function is used in a procedure (bellow), while I've tested this function and it returns the requested values, using it in this stored procedure does not work.

ALTER PROCEDURE dbo.mytestproc
    @doorList     varchar(4000),
    --From Date
    @FromDateYear   int,
    @FromDateMonth  int,
    @FromDateDay   int,
    --To Date
    @ToDateYear    int,
    @ToDateMonth   int,
    @ToDateDay    int,
    --From Time
    @FromTimeHour   int,
    @FromTimeMinute  int,
    @FromTimeSecond  int,
    --To Time
    @ToTimeHour     int,
    @ToTimeMinute   int,

How to Bind Selective Columns from Stored Proc to Gridview using the entity framework


Hi Guys,

I have this entity model>> http://img840.imageshack.us/img840/306/schemaj.jpg and I would like to bind  a list  which consists of: employee's

  • emloyee_firstname(employees table) + employee_lastname(employees table)
  • employee's extension(employee_extension table)
  • deparment name(departments table)

to my gridview. I already created my stored procedure

    Concat(employees.employee_firstname," ",employees.employee_lastname) as name
    , extensions.extension_number
    , departments.department_description
    INNER JOIN employees 
        ON (employee_extensions.employee_id = employees.employee_id)
    INNER JOIN extensions 
        ON (employee_extensions.extension_id = extensions.extension_id)
    INNER JOIN departments 
        ON (employees.department_id = departments.department_id)

But my problem is, i don't know how to bind it since i don't have an entity that contains the employee's name, extension# and departments name. Do I need to create a custom class that consists of said columns? or is their a proper way to do this? I'

call to stored procedure occasionally returns empty recordset



I have a devil of an intermittent problem that I have so far found nothing to help me solve.

I have a web application/module in Dotnetnuke that requires data for a simple form from another SQL server DB on a different server.  The app uses a connection string in the web.config like this:

<add name="CompanyUpdate" connectionString="Data Source=mail.mydatabase.be;Initial Catalog=MA_Online;Persist Security Info=True;User ID=Gus;Password=xxxxxx" providerName="System.Data.SqlClient" />

The application calls a stored proc and most of the time data is returned as expected. There should always be a row returned from the SP.  However, if the form is idle for a while or the module has only just loaded the call to the stored proc returns as empty recordset. A 'table not found' error occurs.

There is no connection issue otherwise there would be a connection error instead of an empty recordset right?. But I cannot work out why sometimes no data is returned. I can go onto the server and run management studio and run the sp over and again and always get data.

I don't have control over the target DB but the person who does thinks it's something to do with connection pooling. I tried turning this off in the connection string and found that I got an empty recordset every time the sp was called wit

Entity Framework - stored procedure mapping, excecuting SP with float parameters separated by commas



Im using Entity framework with POCO entity generator template + SQL Server. I have mapping to stored procedure that takes two floats as input.

After executing function from code with two doubles as parameters: 1.23 and 4.56, SQL Profiler shows:

exec storedProcedureName @arg1 = 1,23, @arg2= 4,56

This is causing an error because it looks like that stored procedure takes 4 arguments, but it should take only two and there should be dots insted of commas in these floats.

My SQL Server collation is SQL_Latin1_General_CP1_CI_AS. I tried to set:

           Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
           Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;

in DataContext Constructor, and di

JDBC - calling stored procedure always returns 1 result set


I am running the following against Northwind on Sql Server 2005. It always returns a result set of 1 row regardless of the parameters passed:

Connection conn;
// not showing conn creation

CallableStatement callStmt = conn.prepareCall("{ call [Sales by Year] (?,?) });
callStmt.setDate(1, new Date (long_Jan_1_1996));
callStmt.setDate(2, new Date (long_Jan_1_1997));
ResultSet rs = callStmt.executeQuery();

Why do I only get 1 row of results back?

thanks - dave

Very funny video - What's your Metaphor?

Stored procedure returns two select results


Hey guys

I am creating a buddy list and I need to select my entire buddy list and then the ones that are online

I have two querries one is your standard


FROM buddies


the other is a bit more complicated


SELECT Buddy as Online
FROM Buddies
FROM online 
WHERE online.Buddy = Buddies.Buddy)

I can't use the union command since I create another column

What I would like is to append the column to my original result so I can access it from some vb script using the sqlclient.datareader


Calling a stored procedure or function from another stored procedure


Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)


Finished running [dbo].[DeleteSetByTime].

This is my function:






RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:




Creating BCS entity for a SQL stored procedure that has parameters using visual studio


Hi Everyone,

                 can anyone help me in creating  BCS entity using BCDM model in visual studio using Linq to sql query for a SQL stored procedure.I am receiving remote debugging error and few other errors.Your step by step approach helps me a lot,I have been playing with this for long,but still no answer.


Thanks ,

function error regarding Stored procedure


I have defined a boolean function with is supposed to trigger a stored procedure. 

However when stepping to the code, I am receiving an error that says " Procedure or Function 'proc_admin_updateTraining' expects parameter '@Pin_Code', which was not supplied."

However this parameter is supplied within my code. Any help will be greatly appreciated.

I have implemented a similar function 6 other times and it's working for those. This is the only one returning the error.

public bool updateTraining()

        int UpdatedRep = 0;
        SqlConnection sqlcon = new SqlConnection(connstring);
        SqlCommand sqlcom = new SqlCommand("proc_admin_updateTraining", sqlcon);
        SqlParameter ParPageID,ParWebLogin, ParPinCode, ParTrainerTelephone, ParTrainerName, ParTrainerContactEmail, ParOutlookApptBody, ParOutlookApptPriority,
        ParOutlookApptSubject, ParOutlookApptLocation, ParSendOutlookAppt;
        ParPageID = sqlcom.Parameters.Add("@web_site_page_id", SqlDbType.Int);
        ParWebLogin = sqlcom.Parameters.Add("@Web_Login", Microsoft.SqlServer.Management.Smo.SqlDataType.VarCharMax);
        ParPinCode = sqlcom.Pa
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend