CODEWITHSUNDEEP
sqlsql-servervb.net
indofraiser
indofraiser

Reputation: 1024

Returning Max with a stored procedure

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

Upvotes: 2

Views: 11182

Answers (3)

indofraiser
indofraiser

Reputation: 1024

 Dim ConnectionString5 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").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

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

Upvotes: 0

jenson-button-event
jenson-button-event

Reputation: 18961

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)

Upvotes: 4

Steve
Steve

Reputation: 216303

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

Upvotes: 1

Related Questions