locked
Return a Max() RRS feed

  • Question

  • User1210767569 posted

    I am wanting to get the max value and return it. Seems simple but I keep hitting an error. The column if refers to exists so i suspect it is the Stored Procedure.

    SP.

    ALTER PROCEDURE [dbo].[spUniqueUserID]
    
    @UniqueID INT OUTPUT
    AS
    
    BEGIN
    
    SELECT MAX(UniqueID) FROM tblResults 
    
    Return @UniqueID
    
    END

    Code:

    Segment:

     command2.CommandText = "spUniqueUserID"
                        command2.Parameters("@UniqueID").Direction = ParameterDirection.Output
                        Session.Item("UniqueID") = command2.Parameters("@UniqueID").Value

    Error: 

    An SqlParameter with ParameterName '@UniqueID' is not contained by this SqlParameterCollection.

    Full:

     Dim sConnString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("mySQLConnectionString").ConnectionString
            Dim dsNames As SqlDataSource
            Dim sReturnString As String = ""
            Dim sSQL As String
    
            dsNames = New SqlDataSource
            dsNames.ConnectionString = sConnString
    
            sSQL = "SELECT distinct userfirstname, usersurname from [tblResults]"
    
            dsNames.SelectCommand = sSQL
            dsNames.SelectParameters.Clear()
    
            Dim vUserFirstName As String
            Dim vUserSurname As String
    
            For Each datarow As Data.DataRowView In dsNames.Select(DataSourceSelectArguments.Empty)
    
                Dim sConnString1 As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("mySQLConnectionString").ConnectionString
                Dim dsNames1 As SqlDataSource
                Dim sReturnString1 As String = ""
                Dim sSQL1 As String
    
                dsNames1 = New SqlDataSource
                dsNames1.ConnectionString = sConnString
    
                vUserFirstName = Replace(datarow("userfirstname").ToString, "'", "''")
                vUserSurname = Replace(datarow("usersurname").ToString, "'", "''")
    
                sSQL1 = "SELECT * from [tblResults] where userfirstname = '" & vUserFirstName & "' and usersurname = '" & vUserSurname & "'"
    
                dsNames1.SelectCommand = sSQL1
                dsNames1.SelectParameters.Clear()
    
                Session.Item("UniqueID") = ""
                For Each datarow1 As Data.DataRowView In dsNames.Select(DataSourceSelectArguments.Empty)
    
                    Dim connection1 As SqlConnection
                    Dim command1 As New SqlCommand
                    Dim ds1 As New DataSet
                    Dim ConnectionString1 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString()
                    connection1 = New SqlConnection(ConnectionString1)
    
                    connection1.Open()
                    command1.Connection = connection1
    
                    If Session.Item("UniqueID").ToString = "" Then
                        'continue to loop, we stop if we find an ID 
    
                        Try
                            command1.CommandText = "spUniqueUser"
                            command1.Parameters("@ID").Direction = ParameterDirection.Output
    
                            command1.Parameters.AddWithValue("@userFirstname", datarow("userfirstname").ToString)
                            command1.Parameters.AddWithValue("@userSurname", datarow("usersurname").ToString)
    
                            Session.Item("UniqueID") = command1.Parameters("@ID").Value
                        Catch ex As IndexOutOfRangeException
                            Console.WriteLine("IndexOutOfRangeException caught")
                        End Try
    
                    Else
                    End If
    
    
                    'now we have the new unique user ID we can add it back to the others 
                    If Session.Item("UniqueID").ToString = "" Then
                        'we need to create a new uniqueID
                        Dim connection2 As SqlConnection
                        Dim command2 As New SqlCommand
                        Dim ds2 As New DataSet
                        Dim ConnectionString2 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString()
                        connection2 = New SqlConnection(ConnectionString2)
    
                        connection2.Open()
                        command2.Connection = connection2
    
                        command2.CommandText = "spUniqueUserID"
                        command2.Parameters("@UniqueID").Direction = ParameterDirection.Output
                        Session.Item("UniqueID") = command2.Parameters("@UniqueID").Value
                    End If
    
                    Dim connection3 As SqlConnection
                    Dim command3 As New SqlCommand
                    Dim ds3 As New DataSet
                    Dim ConnectionString3 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString()
                    connection3 = New SqlConnection(ConnectionString3)
                    'now update
                    command3.CommandText = "spUniqueUserUpdate"
                    command3.Parameters.AddWithValue("@userFirstname", datarow("userfirstname").ToString)
                    command3.Parameters.AddWithValue("@userSurname", datarow("usersurname").ToString)
                    command3.Parameters.AddWithValue("@UniqueID", Session.Item("UniqueID"))
                    '########################################
                Next
            Next




    Friday, September 6, 2013 9:27 AM

Answers

All replies

  • User-760709272 posted

    You have to add a parameter for @UniqueID to the parameters collection before you can give it properties such as direction, value etc.  Most of these can be supplied when you add it

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

    Or you can use AddWithValue

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx

    command2.CommandText = "spUniqueUserID"
    command2.Parameters.Add ("@UniqueID" ....
    command2.Parameters("@UniqueID").Direction = ParameterDirection.Output
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 6, 2013 9:37 AM
  • User1210767569 posted

    Opps! Wood/Trees! 

    Friday, September 6, 2013 9:42 AM
  • User1210767569 posted

    Not quite there...

    I am getting the result in put in, out! :-( 

    When i try SELECT MAX(UniqueID) FROM tblResults I get the answer I am expecting

    VB code:

    Dim connection2 As SqlConnection
            Dim command2 As New SqlCommand
            Dim ds2 As New DataSet
            Dim ConnectionString2 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString()
            connection2 = New SqlConnection(ConnectionString2)
    
            connection2.Open()
            command2.Connection = connection2
    
            command2.CommandText = "spUniqueUserID"
            ccommand2.Parameters.Add("@usr_id", SqlDbType.Int)
            command2.Parameters("@usr_id").Direction = ParameterDirection.Output
    
            Session.Item("UniqueID") = command2.Parameters("@usr_id").Value

    SQL

    use [db]
    GO
    /****** Object:  StoredProcedure [dbo].[spUniqueUserID]    Script Date: 09/06/2013 14:48:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[spUniqueUserID]
    
    @usr_id int
    
    AS
    
    BEGIN
    
    set @usr_id = (SELECT MAX(UniqueID) FROM tblResults )
    
    Return @usr_id
    
    END


    Friday, September 6, 2013 10:55 AM
  • User-760709272 posted

    You need to define it as OUTPUT in your SP

    ALTER PROCEDURE [dbo].[spUniqueUserID]
    
    @usr_id int OUTPUT

    Friday, September 6, 2013 11:16 AM