469,647 Members | 1,824 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Stored Procedure with multiple select statements

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
4 6494
prabunewindia
199 100+
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
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
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
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.

Similar topics

1 post views Thread by Dieter Gasser | last post: by
3 posts views Thread by Joe via DotNetMonster.com | last post: by
4 posts views Thread by Chris | last post: by
2 posts views Thread by =?Utf-8?B?VGVycnk=?= | last post: by
reply views Thread by Richard Silvers | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.