none
Data Columns are not showing up in DataSet Designer when Choose the stored procedure to call and specify required parameters RRS feed

  • Question

  • Hi

    I've created a dynamic query under a stored procedure.. 

    DECLARE @DynamicSQL nvarchar(4000)
    SET @DynamicSQL = N'SELECT * FROM ' +@TableName + 
    ' WHERE ClassName='+@OperationName+' AND (RunTime BETWEEN '+@Date1+' AND '+@Date2+')
    AND TestCaseId = ISNULL('+@TestCaseId+', TestCaseId)
     AND TestRunResult = ISNULL('+@TestRunResult+',TestRunResult) 

    EXECUTE sp_executesql @DynamicSQL

    And then I want to use this stored procedure in TableAdapter of dataset in visual studio 2013. Data columns are not showing up the parameter and field for SELECT operation. Please find the screenshot. 

    I'm not sure if this dynamic statement will work in this case.. Anybody know any possible way to use this stored procedure as datasource in my report.

     '

    Friday, January 23, 2015 1:44 PM

Answers

All replies

  • Hi Ahsan,

    I am moving your thread into the ADO.NET DataSetForum for dedicated support. Thanks for your understanding.

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 26, 2015 5:38 AM
  • Hello,

    >> Anybody know any possible way to use this stored procedure as datasource in my report.

    I am afraid this is by deisgend, the data columns for the select operation is different from the three others, it is based on your selected columns, for example, stored produce as below:

    select [Control].CompanyID from  [Control]

    Then it will only show the CompanyID, since you write a dynamic sql statement, the columns are unknow to the typed dataset at designed time, it shows empty.

    You consider using the ADO.NET way.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 26, 2015 6:42 AM
    Moderator
  • You might want to consider just using a DataAdapter and forget about the TableAdapter. I have written a blog posts that might help.

    First, a post about creating an XSD using a Stored Procedure. You can then create a Typed DataSet without using a TableAdapter:

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    You'll also want to be aware of this little "gotcha" that sometimes happens with the MSDataSetGenerator:

    http://geek-goddess-bonnie.blogspot.com/2012/08/msdatasetgenerator-gone-wild.html

    And, lastly, if you're not sure how to go about using DataAdapters instead of TableAdapters, my 3-post series on DataAccess might help:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post gets into using anonymous delegates (which, to be honest, I don't use the anonymous delegates anymore, but I used to on previous projects several years ago).

    Hope this helps ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, January 28, 2015 6:00 AM