How to fix the error: CommandText property has not been initialized RSS

• • Reply
  aspfun

  Member

  47 Points

  1656 Posts

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

  Jul 15, 2020 07:45 PM|aspfun|LINK

  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

• • Reply
  yij sun

  Contributor

  3730 Points

  1417 Posts

  Re: How to fix the error: CommandText property has not been initialized

  Jul 16, 2020 02:48 AM|yij sun|LINK

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  0belix

  Participant

  1226 Points

  857 Posts

  Re: How to fix the error: CommandText property has not been initialized

  Jul 16, 2020 02:34 PM|0belix|LINK

  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)