Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server ADO.NET
Hi,

I make stored procedure that has two sql statement as like that:
 
CREATE PROCEDURE uspSelectAddressContent
    @TopicID int
AS
select TopicAddress,TopicContent from  Topics where Topics.TopicID=@TopicID
 
select count(FavoriteID) as BookmarkNum from Favorites where TopicID=@TopicID
GO
 
I want to receive the data in one dataset that has one table like that:
 
ourDataAdapter.Fill(SpecificTopicDataSet, "topic");
 

but it gives me an error that:
 
Column 'BookmarkNum' does not belong to table topic.
Posted 30-Dec-10 19:44pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Use ourDataAdapter.Fill(SpecificTopicDataSet); and the first result set will be in the first datatable in your dataset and the second will be in the second datatable(SpecificTopicDataSet.tables(1)).
As an alternate change your query like:
 
select 
  TopicAddress,TopicContent,
  (select count(FavoriteID) from Favorites where TopicID=@TopicID)
   as BookmarkNum
from Topics 
where Topics.TopicID=@TopicID
This will fetch the result in one result set
  Permalink  
Comments
MrProgrammer_78 at 31-Dec-10 1:38am
   
thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

CREATE PROCEDURE uspSelectAddressContent
    @TopicID int
AS
select TopicAddress,TopicContent from  Topics where Topics.TopicID=@TopicID
select count(FavoriteID) as BookmarkNum from Favorites where TopicID=@TopicID
GO
 
If I interperated correctly then You want the result with columns
1 - TopicAddress
2 - TopicContent
3 - BookMarkNumber (of the topic with TopicID=@topicID From Favourite Table)
 
For this particular Question you can use Sub Query
 
CREATE PROCEDURE uspSelectAddressContent
    @TopicID int
AS
select TopicAddress,TopicContent, 
(select count(FavoriteID) from Favorites where Favorites.TopicID=Topics.TopicID) as BookmarkNum
 from  Topics where Topics.TopicID=@TopicID
 
GO;
 
For eaxmple This Query can be used to get all the topics with total Bookmarks (thats why the condition "Favorites.TopicID=Topics.TopicID" )
 
select TopicAddress,TopicContent,
(select count(FavoriteID) from Favorites where Favorites.TopicID=Topics.TopicID) as BookmarkNum
 from  Topics
GO;
  Permalink  
Comments
MrProgrammer_78 at 31-Dec-10 2:13am
   
follow me...
AnupKumarYadav at 31-Dec-10 2:21am
   
Sorry, I didn't get you what is "Follow me...", thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Why you are not using JOIN for this.
  Permalink  
Comments
MrProgrammer_78 at 31-Dec-10 1:05am
   
because there are no relation between tables allow me to do that,........ I have certain limits to use join. ........i want to receive all data in one table in one dataset
vivekse at 31-Dec-10 1:12am
   
Two select statement always create two data table in one dataset.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

thanks man, your opinion is right
but it is not my complete procedure
see it :
 
CREATE PROCEDURE dbo.uspSelectTopicAddressContent
    @TopicID int
AS
select TopicAddress,TopicContent,TopicCommentNum,TopicDate,TopicViews,
TopicEvaluation,CategoryName,
(select count(FavoriteID) from Favorites where TopicID=@TopicID) as BookmarkNum
from  Topics join Category
on Topics.CategoryID=Category.CategoryID
where Topics.TopicID=@TopicID
GO
 
i am resticted to use select from Topics table not favorites table
because i want also CategoryName form Category table.
 
i hope it clear for u now
  Permalink  
v2
Comments
AnupKumarYadav at 31-Dec-10 2:20am
   
You must change the condition in the subquery
(select count(FavoriteID) from Favorites where TopicID=@TopicID )
From TopicID=@TopicID to Favorites.TopicID= Topics.TopicID , To get correct reqult , because the previous concition (where TopicID=@TopicID) will give same value for all the topics. so instead use Favorites.TopicID= Topics.TopicID
MrProgrammer_78 at 31-Dec-10 4:07am
   
no man , it is not important to change it because the TopicID in subquary (select count(FavoriteID) from Favorites where TopicID=@TopicID) is belong to the table Favorites ...... as table Favorites has its own TopicID field .... so this subquary is separated alone from the all procedure..... and it works successfully with me

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

  Print Answers RSS
0 Zoltán Zörgő 220
1 Peter Leow 160
2 CPallini 120
3 CHill60 100
4 BillWoodruff 95
0 Sergey Alexandrovich Kryukov 9,428
1 OriginalGriff 6,901
2 Peter Leow 4,737
3 Zoltán Zörgő 4,349
4 CHill60 2,932


Advertise | Privacy | Mobile
Web04 | 2.8.150129.1 | Last Updated 31 Dec 2010
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100