Returning Max with a stored procedure

Question

I have read and retried rebuilding the below in many ways but to keep it clear I will show my last attempt.

Aim - To get the Max value of column "UniqueID"

The column field 'uniqueID' is set as a bigint

I assume the error is in the line with addwithvalue in as I get "int" as the return value

If I run the query SELECT MAX(UniqueID) FROM tblResults in SQL it works

Code

 Dim connection5 As SqlConnection
    Dim command5 As New SqlCommand
    Dim ds5 As New DataSet
    Dim ConnectionString5 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString()
    connection5 = New SqlConnection(ConnectionString5)

    connection5.Open()
    command5.Connection = connection5
    command5.Parameters.Clear()

    command5.CommandText = "spUniqueUserID"
    command5.Parameters.AddWithValue("@UniqueID", SqlDbType.BigInt)
    command5.Parameters("@UniqueID").Direction = ParameterDirection.Output
    command5.CommandType = CommandType.StoredProcedure
    command5.ExecuteNonQuery()

    Session.Item("UniqueID") = command5.Parameters("@UniqueID").Value
    connection5.Close()

    Dim vShow As String
    vShow = ""
    vShow = Session.Item("UniqueID").ToString

SP

USE [DB]
GO
/****** Object:  StoredProcedure [dbo].[spUniqueUserID]    Script Date: 09/10/2013 08:51:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spUniqueUserID]

@UniqueID bigint OUTPUT

AS

BEGIN

select @UniqueID = (SELECT MAX(UniqueID) FROM tblResults )

Return @UniqueID

END

Solution 1

I personally wouldnt mess around with the output parameter. Simply use

ALTER PROCEDURE [dbo].[spUniqueUserID]

AS

BEGIN

 SELECT MAX(UniqueID) FROM tblResults

END

in your proc, and

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim returnValue As Object

cmd.CommandText = "spUniqueUserID"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()

returnValue = cmd.ExecuteScalar()

sqlConnection1.Close()

In your code. (best with using statements for your connection and command, here missing, for brevity)

Solution 2

Try with

 command5.Parameters.AddWithValue("@UniqueID", 0L)

The AddWithValue determines the DataType of the parameter to pass to the underlying engine looking at the datatype of the second parameter. You pass a string and this is certainly wrong.

In alternative you could define explicitly the parameter

 Dim parameter = new SqlParameter("@UniqueID", SqlDbType.BigInt)
 parameter.Direction = ParameterDirection.Output
 parameter.Size = 8
 parameter.Value = 0
 command5.Parameters.Add(parameter)

And, as last but fundamental steps, don't forget to specify that this command executes a storedprocedure and then execute the command

 command5.CommandType = CommandType.StoredProcedure
 command5.ExecuteNonQuery()

As an alterative, but this requires a change to your stored procedure, is to use ExecuteScalar. This method should be used when you need a single result from your code.

ALTER PROCEDURE [dbo].[spUniqueUserID]
AS

BEGIN
    select SELECT MAX(UniqueID) FROM tblResults
END

And in your code

    Using connection5 = New SqlConnection(ConnectionString5)
    Using command5 As New SqlCommand("spUniqueUserID", connection5)
         connection5.Open()
         command5.CommandType = CommandType.StoredProcedure
         Dim result = command5.ExecuteScalar()
         .....
    End Using
    End Using

But at this point the usefulness of the storedprocedure is really minimal and you could code directly the sql in the constructor of the SqlCommand and remove the CommandType setting

    Using connection5 = New SqlConnection(ConnectionString5)
    Using command5 As New SqlCommand("SELECT MAX(UniqueID) FROM tblResults", connection5)
         connection5.Open()
         Dim result = command5.ExecuteScalar()
         .....
    End Using
    End Using

Comments

[indofraiser]

If I put OL I get "0" as the result (If I put 99 I get "99" as the result.)

[indofraiser]

Jenson you might need to walk me though that a bit more. i.e. what am I keeping/dumping from my code. Still a little green around the edges!

[indofraiser]

Yes the Int64 comes up as "is a type and cannot be used as an expression. So I tried 'Long' but that did not fix that. I also tried the cmd.executescalar on a new line (just encase) Really appreciate the help by the way!

[indofraiser]

I am trying to get both ways to work btw so I understnad both. Steve, no still blank return. I can only comment so put my version (updated) at top of the page with the command5.executenon.query()

[Steve]

Did you try the last code? Let's forget the storedprocedure for the moment and pass the query directly. The result variable should be set to something. If you put a breakpoint on the ExecuteScalar line and execute it what is the value assigned to result?

[indofraiser]

Will try - just have to pop out for 30 - sorry!

[indofraiser]

Working... Dim ConnectionString5 As String = System.Configuration.ConfigurationManager.ConnectionStrings(‌​"mySQLConnectionStri‌​ng").ToString() Using connection5 = New SqlConnection(ConnectionString5) Using command5 As New SqlCommand("SELECT MAX(UniqueID) FROM tblResults", connection5) connection5.Open() Dim result = command5.ExecuteScalar() Session.Item("UniqueID") = result connection5.Close() End Using End Using

[Steve]

Well, that's good to know, I would remove the SP because now it is really pointless. Also with the Using statement you don't need to explicitly call connection5.Close(). It is already included in the End Using statement