Click here to Skip to main content
15,946,342 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I make stored procedure that has two sql statement as like that:

SQL
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

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
 
Share this answer
 
Comments
MrLonely_2 31-Dec-10 1:38am    
thanks
SQL
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

SQL
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" )

SQL
select TopicAddress,TopicContent,
(select count(FavoriteID) from Favorites where Favorites.TopicID=Topics.TopicID) as BookmarkNum
 from  Topics
GO;
 
Share this answer
 
Comments
MrLonely_2 31-Dec-10 2:13am    
follow me...
AnupKumarYadav 31-Dec-10 2:21am    
Sorry, I didn't get you what is "Follow me...", thanks
Hi,

Why you are not using JOIN for this.
 
Share this answer
 
Comments
MrLonely_2 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 31-Dec-10 1:12am    
Two select statement always create two data table in one dataset.
thanks man, your opinion is right
but it is not my complete procedure
see it :

SQL
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
 
Share this answer
 
v2
Comments
AnupKumarYadav 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
MrLonely_2 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900