By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,272 Members | 1,272 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,272 IT Pros & Developers. It's quick & easy.

Stored Procedure with multiple select statements

P: 62
Is it possible to use to select statements in a stored procedure?

I am building a movie rating system, what I am doing is creating a table with movies and individual user ratings.

The code needs to get a count of the ratings, then the sum of them to get the percentage.


I am getting a error
Exception Details: System.IndexOutOfRangeException: columncount

it seems my stored procedure returns the first select, but the value for the second select is non existent.

Is this possible? I've never tried a Stored Procedure with multiple selects?
and if so, what am I doing wrong?




Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT columncount = COUNT(*) FROM MovieRatings WHERE(MoviesID = @MoviesID)





---------------------------------------------------------------------------------------------------




Vb.net code ---------------------------------------------------------------------------------------


Public Function GetMovieRating(ByVal MoviesID As Integer) As Integer

Dim Conn As New SqlConnection(ConnectionString)
Dim CmdSelect As New SqlCommand("GetMovieRating", Conn)
CmdSelect.CommandType = CommandType.StoredProcedure
CmdSelect.Parameters.Add(New SqlParameter("@MoviesID", MoviesID))
' Try
Conn.Open()
Dim Dr As SqlDataReader = CmdSelect.ExecuteReader()

While Dr.Read()

Dim Rating As Integer = Dr("RatingSum") / Dr("columncount")

Return Rating

End While

' Catch ex As Exception
' Throw New ApplicationException("Data Error")
' Finally
Conn.Close()
' End Try

End Function


---------------------------------------------------------------------------------------------------
Aug 1 '07 #1
Share this Question
Share on Google+
4 Replies


prabunewindia
100+
P: 199
hi friend,
try to use SqlDataAdapter instead of datareader
and get the output into DataSet
now ur dataset will have two tables
1.DatasetName.Tables[0]
2.DatasetName.Tables[1]

code

Dim Conn As New SqlConnection(ConnectionString)
Dim CmdSelect As New SqlCommand("GetMovieRating", Conn)
CmdSelect.CommandType = CommandType.StoredProcedure
CmdSelect.Parameters.Add(New SqlParameter("@MoviesID", MoviesID))
SqlDataAdapter da=new SqlDataAdapter((CmdSelect);
Dataset ds=new Dataset();
da.Fill(ds);

use this dataset for ur process
Prabu



Is it possible to use to select statements in a stored procedure?

I am building a movie rating system, what I am doing is creating a table with movies and individual user ratings.

The code needs to get a count of the ratings, then the sum of them to get the percentage.


I am getting a error
Exception Details: System.IndexOutOfRangeException: columncount

it seems my stored procedure returns the first select, but the value for the second select is non existent.

Is this possible? I've never tried a Stored Procedure with multiple selects?
and if so, what am I doing wrong?




Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT columncount = COUNT(*) FROM MovieRatings WHERE(MoviesID = @MoviesID)





---------------------------------------------------------------------------------------------------




Vb.net code ---------------------------------------------------------------------------------------


Public Function GetMovieRating(ByVal MoviesID As Integer) As Integer

Dim Conn As New SqlConnection(ConnectionString)
Dim CmdSelect As New SqlCommand("GetMovieRating", Conn)
CmdSelect.CommandType = CommandType.StoredProcedure
CmdSelect.Parameters.Add(New SqlParameter("@MoviesID", MoviesID))
' Try
Conn.Open()
Dim Dr As SqlDataReader = CmdSelect.ExecuteReader()

While Dr.Read()

Dim Rating As Integer = Dr("RatingSum") / Dr("columncount")

Return Rating

End While

' Catch ex As Exception
' Throw New ApplicationException("Data Error")
' Finally
Conn.Close()
' End Try

End Function


---------------------------------------------------------------------------------------------------
Aug 1 '07 #2

gagandeepgupta16
P: 56
Is it possible to use to select statements in a stored procedure?

I am building a movie rating system, what I am doing is creating a table with movies and individual user ratings.

The code needs to get a count of the ratings, then the sum of them to get the percentage.


I am getting a error
Exception Details: System.IndexOutOfRangeException: columncount

it seems my stored procedure returns the first select, but the value for the second select is non existent.

Is this possible? I've never tried a Stored Procedure with multiple selects?
and if so, what am I doing wrong?




Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT columncount = COUNT(*) FROM MovieRatings WHERE(MoviesID = @MoviesID)





---------------------------------------------------------------------------------------------------




Vb.net code ---------------------------------------------------------------------------------------


Public Function GetMovieRating(ByVal MoviesID As Integer) As Integer

Dim Conn As New SqlConnection(ConnectionString)
Dim CmdSelect As New SqlCommand("GetMovieRating", Conn)
CmdSelect.CommandType = CommandType.StoredProcedure
CmdSelect.Parameters.Add(New SqlParameter("@MoviesID", MoviesID))
' Try
Conn.Open()
Dim Dr As SqlDataReader = CmdSelect.ExecuteReader()

While Dr.Read()

Dim Rating As Integer = Dr("RatingSum") / Dr("columncount")

Return Rating

End While

' Catch ex As Exception
' Throw New ApplicationException("Data Error")
' Finally
Conn.Close()
' End Try

End Function


---------------------------------------------------------------------------------------------------

hi
there can be multiple select query, the thing is how you return the output.
having two output parameters or returning to datareader.

Your Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT COUNT(*) as columncount FROM MovieRatings WHERE(MoviesID = @MoviesID)


hope this solves your prob
Aug 1 '07 #3

P: 62
I guess my question is, how do I retreive both those column values.

the reason I am trying to use a datareader is so I can grab both values, and dived them. then put them into a variable and return the function.

I don't know if you can grab values out of a dataset like that. I've never used a dataset in that way. I've just used them to fill controls with a dataset.

any help would be appreciated.
Aug 1 '07 #4

P: 62
Ok, it's working.
I took prabunewindia's advice.

I created a dataset and pulled the 2 variables.

here is my code ----------------------------------------------------------------

Dim Conn As New SqlConnection(ConnectionString)
Dim CmdSelect As New SqlCommand("GetMovieRating", Conn)
CmdSelect.CommandType = CommandType.StoredProcedure
CmdSelect.Parameters.AddWithValue("@MoviesID", MoviesID)

Dim Da As New SqlDataAdapter(CmdSelect)
Dim Ds As New DataSet

'Fill the Dataset
'Try
Da.Fill(Ds)

Dim RatingSum As Integer = Ds.Tables(0).Rows(0)("RatingSum").ToString()
Dim columncount As Integer = Ds.Tables(1).Rows(0)("columncount").ToString()



Dim Rating As Integer = RatingSum / columncount

Return Rating



'Catch ex As Exception
Throw New ApplicationException("Data Error")
'Finally
Conn.Close()
'End Try

-----------------------------------------------------------------


My only concern is that this code is going to query the database for every movie listed. I hope this method of counting and suming of a database table for every movie in a recordset isn't too taxing on the database as it gets large and there are a lot of users using the system.

Thanks for all your help
Aug 2 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.