locked
How to fix the error: CommandText property has not been initialized RRS feed

  • Question

  • User546194788 posted

    I created a function "GetCountScalar" to get a count of records from select statement.

    But, it causes an error:

    System.InvalidOperationException: ExecuteScalar: CommandText property has not been initialized

    In the project, here is code.

    dim strCount as string = "select count(MEMBER_ID) from MEMBER"

    dim iCount as integer = GetCountScalar(strCount)

    Public Function GetCountScalar(ByVal strSQL As String) As Integer
    Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) // "ConnectionString" is from web.config file
    Dim mySQLCommand As SqlCommand

    Using myConnection
    myConnection.Open()
    mySQLCommand = New SqlCommand(strSQL, myConnection)
    mySQLCommand.CommandType = CommandType.Text 
    Return CInt(mySQLCommand.ExecuteScalar)
    myConnection.Close()
    End Using

    Wednesday, July 15, 2020 7:45 PM

All replies

  • User1535942433 posted

    Hi aspfun,

    Accroding to your description and codes,I create a test and it works fine.

    Is this your full codes?Do you have other operations before calling the GetCountScalar function?

    As far as I think,you may not get the select query of the string strCount. I suggest you could debug and check  wheather the strCount is null.

    This is my test:

    Protected Sub Button1_Click(sender As Object, e As EventArgs)
            Dim strCount As String = "select count (TransId) from Test0"
            Dim iCount As Integer = GetCountScalar(strCount)
    
        End Sub
        Public Function GetCountScalar(ByVal strSQL As String) As Integer
            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("MyConnectionString"))
            Dim mySQLCommand As SqlCommand
    
            Using myConnection
                myConnection.Open()
                mySQLCommand = New SqlCommand(strSQL, myConnection)
                mySQLCommand.CommandType = CommandType.Text
                Return CInt(mySQLCommand.ExecuteScalar)
                myConnection.Close()
            End Using
        End Function

    When I set strCount is null,I occure your error:

     Dim strCount As String = Nothing '= "select count(TransId) from Test0"
     Dim iCount As Integer = GetCountScalar(strCount)

    I suggest you could post your full codes to us.

    Best regards,

    Yijing Sun

    Thursday, July 16, 2020 2:48 AM
  • User1738843376 posted

    Hi,

    Try this:

    Public Function GetCountScalar(ByVal strSQL As String) As Integer
        Dim countResult As Integer = 0
        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) 
        Dim mySQLCommand As New SqlCommand
    
        With mySQLCommand
            .Connection = myConnection
            .CommandType = CommandType.Text 
            .CommandText = strCount
        End With
    
        myConnection.Open()

    countResult = CInt(mySQLCommand.ExecuteScalar)
    myConnection.Close()
    return countResult End Function

    Dim strCount as string = "select count(MEMBER_ID) from MEMBER"
    Dim iCount AS integer = GetCountScalar(strCount)

    Thursday, July 16, 2020 2:34 PM