VB SQL CommandText property has not been initialized

Question

I'm new to working with background workers, and I'm trying to run the following code. But I recieve a run-time error on the m._Value_CreatedDate = m._MyCMD.ExecuteScalar() line. The error is:

Additional information: ExecuteScalar: CommandText property has not been initialized

        Try
            Dim m As MyParameters = DirectCast(e.Argument, MyParameters) 
            m._Con.Open()
            m._QueryStr = "SELECT TOP 1 CONVERT(varchar(10),aCreated,103) FROM Account WHERE aMember_ID = " & m._Selected_MemberID & ""
            m._MyCMD.CommandType = CommandType.Text
            m._Value_CreatedDate = m._MyCMD.ExecuteScalar()
        Catch ex As Exception
            m._Value_CreatedDate = "N/A"
        End Try

Here's the Parameter's I'm using:

 Class MyParameters
        Friend _QueryStr As String
        Friend _Value_CreatedDate As Object
        Friend _AccountID As Object
        Friend _Selected_MemberID As String = Committee_Database.GridView1.GetFocusedRowCellValue("Unique ID").ToString
        Friend _Con As New SqlConnection('Connection string ommitted)
        Friend _MyCMD As New SqlCommand(_QueryStr, _Con)
    End Class

Please forgive me if I'm doing something extremely wrong, I'm self taught and experimenting with the backgroundworker. It's worth noting that _QueryStr will change multiple times as the background worker runs multiple queries against the same database and (as I understand it) stores each of the returned values from the queries into variables - _Value_CreatedDate is the variable I am using in this code. I've included an example of how I'm recycling the _QueryStr variable below and storing the returned value into a diffent Variable each time.

            Try
            m._QueryStr = "SELECT TOP 1 aAccount_ID FROM Account WHERE aUser_Name='" & _Selected_AccountID & "'"
            m._MyCMD.CommandType = CommandType.Text
            m._AccountID = m._MyCMD.ExecuteScalar()
        Catch ex As Exception
        End Try

Am I doing something wrong?

Answer 1

In the implementation of your class MyParameters you initialize the SqlCommand directly with the declaration using the value of the variable _QueryStr. At that point in time the variable _QueryStr is not yet initialized, so it is an empty string.

After the initialization of an instance of MyParameters, you change the value of _QueryStr (many times according to you) but these changes are not automatically passed to the CommandText of your SqlCommand. It still contains the empty initial value.

You could fix this problem building appropriate getter and setter for a new QueryStr property. When someone tries to set the property the code below change the value of the internal field _QueryStr (now private) and reinitializes the CommandText property of your SqlCommand.

Class MyParameters
   Private _QueryStr As String
   Public Property QueryStr() As String
      Get
         Return _QueryStr
    End Get
    Set(ByVal value As String)
        _QueryStr = value
        _MyCMD.CommandText = _QueryStr
    End Set
    End Property
    ..... other properties
    Friend _MyCMD As New SqlCommand(_QueryStr, _Con)
End Class

Now when you write

Try
   m.QueryStr = "SELECT ...."
   ....

the new command text is correctly assigned to your command.

As a side note: I suggest to use plain ADO.NET objects (or learn how to use an ORM tool). Do not try to encapsulate them in custom classes unless you have a very deep understanding on how these ADO.NET objects works. You gain nothing and expose your code to many problems. For example your code is very easy to exploit with Sql Injection because the MyParameters class has no provision to use a parameterized query. Your code has no method to close and dispose the SqlConnection embedded in your class thus leading to resource leaks.