locked
DataSet can't preview data from StoredProcedure RRS feed

  • Question

  • User-1153310292 posted

    hi, i have a StoredProcedure (SP_Batch) like this :

    select Batch_Id, Batch_Date
    from Batch 
    where Batch_Id in(@Batch_Id)

    i use 'where Batch_Id in(@Batch_Id)' because i want to list more than one Batch_Id. i can exec this SP in sql server.

    i add new table adapter in my dataset using that SP. but when i try to preview data i just only can use single value ('BG_001'). when i try to preview data using more than one value ('BG_001','BG_002') it shows nothing. i dont get any list from my SP. 

    is there something wrong with my SP ? it is possible to use more than one value in dataset ?

    thanks

    Wednesday, October 26, 2011 6:07 AM

Answers

  • User-1153310292 posted

    i have a solution for my own question refering to this link

    http://stackoverflow.com/questions/7815780/dataset-xsd-cannot-list-column-from-stored-procedure

    i try and it works, now i can use my original StoredProcedure in my DataSet.xsd

    thanks for reply

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 27, 2011 10:18 PM

All replies

  • User-1618234021 posted

    Hi

    Can you post the code how u are calling stored procedure in asp.net ?

    Wednesday, October 26, 2011 6:25 AM
  • User-1208031168 posted

    Please check how you declared @Batch_Id

    Wednesday, October 26, 2011 11:49 AM
  • User-1153310292 posted

    the original for the SP is like this :

    ALTER PROCEDURE [dbo].[SP_Batch] (
    	@batchId varchar(8000)
    )
    AS
    --temp batch ids
    CREATE TABLE #temp_batch (batch_id VARCHAR(20))  
    EXEC SP_StrListToTable @batchId, ',', #temp_batch
    select Batch_Id, Batch_Date
    from Batch 
    where Batch_Id in (select * from #temp_batch)
    
    

    but in DataSet.xsd i cant Create Table. so i change the stored procedure like this :

    ALTER PROCEDURE [dbo].[SP_Batch] (
    	@batchId varchar(50)
    	)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
        -- Insert statements for procedure here
        select Batch_Id, Batch_Date
    	from Batch 
    	where 
    	Batch_id = (@batchId) 
    END
    

    then i add TableAdapter in DataSet.xsd and try to preview data with one Batch_Id and i can get the result. but when i try to preview data with more than one Batch_Id there is no result. 

    is there something wrong with  my procedure ?

    Wednesday, October 26, 2011 9:56 PM
  • User3866881 posted

    select Batch_Id, Batch_Date from Batch where Batch_id = (@batchId)

    string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
    const string cmdText
    = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

    using
    (SqlCommand cmd = new SqlCommand(cmdText)) {
       cmd
    .Parameters.AddWithValue("@tags", string.Join("|", tags);
    }
    Plz see:http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause

    Thursday, October 27, 2011 9:09 PM
  • User-1153310292 posted

    i have a solution for my own question refering to this link

    http://stackoverflow.com/questions/7815780/dataset-xsd-cannot-list-column-from-stored-procedure

    i try and it works, now i can use my original StoredProcedure in my DataSet.xsd

    thanks for reply

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 27, 2011 10:18 PM