Returning SQL Values

[tehon3299]

I am wondering how I can return this query to a variable:

SessionCmd = "SELECT SUM(deposit-withdrawl) FROM tmTransactions WHERE UserRecord = " & user & "" 
MyCommand = New SqlCommand(SessionCmd, MyConnection)

It will return an integer value and I need to store it in a variable so I can add to it and update it. How can this be done?

Thanks

 

[quwiltw]

What DB are you using? What do you mean add to and update it? Its the results of a SUM function and can only be changed if the underlying data changes. You are of course free to add to it in code (because its just an int), but updating it back is not possible.

 

[Heiko]

    Friend Shared Function GetSingleValue( _
        ByVal sql As String) As Object

        Dim cmd As New SqlCommand()
        Dim retval As Object
        Dim mySqlConnection As SqlConnection

        Try
            mySqlConnection = GetConnection()
            PrepareCommand(cmd, mySqlConnection, CType(Nothing, SqlTransaction), CommandType.Text, sql, Nothing)

            execute the command & return the results
            retval = cmd.ExecuteScalar()

            detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear()

            Return retval
        Finally
            mySqlConnection.Close()
        End Try
    End Function

     This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
     to the provided command.
     Parameters:
     -command - the SqlCommand to be prepared
     -connection - a valid SqlConnection, on which to execute this command
     -transaction - a valid SqlTransaction, or null
     -commandType - the CommandType (stored procedure, text, etc.)
     -commandText - the stored procedure name or T-SQL command
     -commandParameters - an array of SqlParameters to be associated with the command or null if no parameters are required
    Private Shared Sub PrepareCommand(ByVal command As SqlCommand, _
                                      ByVal connection As SqlConnection, _
                                      ByVal transaction As SqlTransaction, _
                                      ByVal commandType As CommandType, _
                                      ByVal commandText As String, _
                                      ByVal commandParameters() As SqlParameter)

        if the provided connection is not open, we will open it
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If

        associate the connection with the command
        command.Connection = connection

        set the command text (stored procedure name or SQL statement)
        command.CommandText = commandText

        if we were provided a transaction, assign it.
        If Not (transaction Is Nothing) Then
            command.Transaction = transaction
        End If

        set the command type
        command.CommandType = commandType

        attach the command parameters if they are provided
        If Not (commandParameters Is Nothing) Then
            AttachParameters(command, commandParameters)
        End If

        Return
    End Sub PrepareCommand

     This method is used to attach array of SqlParameters to a SqlCommand.
     This method will assign a value of DbNull to any parameter with a direction of
     InputOutput and a value of null.  
     This behavior will prevent default values from being used, but
     this will be the less common case than an intended pure output parameter (derived as InputOutput)
     where the user provided no input value.
     Parameters:
     -command - The command to which the parameters will be added
     -commandParameters - an array of SqlParameters tho be added to command
    Private Shared Sub AttachParameters(ByVal command As SqlCommand, ByVal commandParameters() As SqlParameter)
        Dim p As SqlParameter
        For Each p In commandParameters
            check for derived output value with no value assigned
            If p.Direction = ParameterDirection.InputOutput And p.Value Is Nothing Then
                p.Value = Nothing
            End If
            command.Parameters.Add(p)
        Next p
    End Sub AttachParameters