Share via

In Visual Basic How Do I Declare Scalar Variable Using SQL

Gary Simpson 471 Reputation points
Mar 28, 2023, 3:43 PM

Hi Good People,

I am trying to get a sum of all values in a specific column from SQL Database. But I keep getting an error.

The latest error is now I have to declare Scalar Variable. I have tried many ways to fix the issue. Could someone have a look at my code and tell me where I have gone wrong.

Basically I want to get a sum of a column where the date is between a Date From and a Date To then show the Value in a Textbox.

The Code I have at the Moment is Below And A Picture Below That.


Public Sub PaymentOutReceivedNet()

        'Connect to Database
        Using cn As New SqlConnection With {.ConnectionString = "My Connection"}

            'Only show Value where the Date is between DateFrom and DateTo
            Using cmd As New SqlCommand With {.Connection = cn}

                ' Get Values of Net where Date is between DateFrom and DateTo
                SQL.AddParam("@DateFrom", GetDateFromString(TxtDateFrom.Text))
                SQL.AddParam("@DateTo", GetDateFromString(TxtDateTo.Text))

                cmd.CommandText = "SELECT SUM(NET) AS TOTAL " &
                                  "FROM PaymentsOut " &
                                  "WHERE DatePurchased between @DateFrom And @DateTo "

                'OPEN CONNECTION
                cn.Open()

                Dim _DateFrom As Date = GetDateFromString(TxtDateFrom.Text)
                Dim _DateTo As Date = GetDateFromString(TxtDateTo.Text)
                Dim sumResult = cmd.ExecuteScalar()

                'CHECK FOR NO VALUES
                If IsDBNull(sumResult) Then
                    TxtBox7.Text = "£0.00"
                Else
                    'if results is found then Load TxtBox1 with result
                    TxtBox7.Text = FormatCurrency(sumResult)

                End If
            End Using
        End Using

    End Sub

Scalar Variable Issue

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,236 questions
Visual Studio Setup
Visual Studio Setup
Visual Studio: A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.Setup: The procedures involved in preparing a software program or application to operate within a computer or mobile device.
1,067 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 68,801 Reputation points
    Mar 28, 2023, 3:57 PM

    you never added the required parameters to the "cmd" instance you executed the query on.

    cmd.Parameters.AddWithValue("@DateFrom", GetDateFromString (TxtDateFrom.Text))
    cmd.Parameters.AddWithValue("@DateTo", GetDateFromString (TxtDateTo.Text))
    cmd.ExecuteScalar()
    
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.