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:

Expand|Select|Wrap|Line Numbers

 
        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:

Expand|Select|Wrap|Line Numbers

 
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.

SQL Stored Procedures and VB.NET

re: SQL Stored Procedures and VB.NET

re: SQL Stored Procedures and VB.NET

Similar Visual Basic .NET bytes

/bytes/development

/bytes/it

/bytes/about