SQL Stored Procedures and VB.NET

[Enyi]

Hey there!

I need some help getting some code to work. This particular bit is nearly done, just that it's not working.

Situation:
Trying to get the row count of an table in an SQL database using VB.NET 2003 and stored procedures in SQL Server 2000 Developer Edition. Dam, it's also just started to rain, oh dear. Anyways...

Somewhere in my project is the code:

        Try
            Dim cmdVideoCount As New SqlCommand
            cmdVideoCount.Connection = sqlConn
            cmdVideoCount.CommandText = "GetVideoCount"
            cmdVideoCount.CommandType = CommandType.StoredProcedure
 
            Dim paramResult As New SqlParameter
            paramResult.ParameterName = "@VideoCount"
            paramResult.DbType = DbType.Int16
            paramResult.Direction = ParameterDirection.Output
            cmdVideoCount.Parameters.Add(paramResult)
 
            sqlConn.Open()
            Dim intVideos As Integer = cmdVideoCount.ExecuteScalar()
            MsgBox(intVideos)
            sqlConn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            sqlConn.Close()
        End Try
 

The code for the stored procedure is:

CREATE PROCEDURE GetVideoCount ( @VideoCount int OUTPUT )
AS
SELECT @VideoCount = Count(Video_ID) FROM Videos
GO
 

At the moment, it has now stopped raining, yay! and the stored procedure returns 0.

There is one test record in the database at the moment.

Any help would be appreciated, thanks.

Enyi

[Petrosoft]

change the procedure
from

CREATE PROCEDURE GetVideoCount ( @VideoCount int OUTPUT )
AS
SELECT @VideoCount = Count(Video_ID) FROM Videos
GO

to

CREATE PROCEDURE GetVideoCount ( )
AS
SELECT Count(Video_ID) FROM Videos
GO

and remove the following lines from your code
Dim paramResult As New SqlParameter
paramResult.ParameterName = "@VideoCount"
paramResult.DbType = DbType.Int16
paramResult.Direction = ParameterDirection.Output
cmdVideoCount.Parameters.Add(paramResult)

ExecuteScalar returns the first field of the first record as an object.
in your case the Count(Video_ID) is returned from the call to ExecuteScalar

[Enyi]

Thank you very much. Had been getting annoyed with this code. The procedure and code makes a bit more sense now. Thanks.