ExecuteScalar: CommandText property has not been initialized

  • One ASP.NET project keeps on receiving a message below from "Try...Catch...End try" block.

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

    I searched Google but can't get help.

    Is this SQL script mistake? Please help.

    ---------------------------------------------------------

    It points at a function from app below

    dim iCount as Int32 = 0

    dim SQL as string = "Select count(*) from ORDER where[OrderCity]='London'"

    iCount = GetCountScalar(SQL) //Error message point at the function in this line

    Public Function GetCountScalar(ByVal strSQL As String) As Integer

    Dim ret As Int32 = 0

    Using myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) '

    myConnection.Open()

    Using mySQLCommand = New SqlCommand(strSQL, myConnection)

    mySQLCommand.CommandType = CommandType.Text

    ret = CInt(mySQLCommand.ExecuteScalar)

    End Using

    End Using

    Return ret

    End Function

     

  • Quick review of the code, the error isn't where you indicated.  How do I know - that exact line is assigning an integer a value.  That's it.  It is something inside your function GetCountScalar(SQL).

    I am also not seeing a TRY CATCH block in there unless you are declaring that entire thing in the try catch which would be weird design principle and not even sure that'd work.

    What I would do is toss in a breakpoint on this line:

    ret = CInt(mySQLCommand.ExecuteScalar)

    When that breakpoint gets hit, check the value of mySQLCommand; specifically looking at the value of CommandText.  My expectation is that it is NULL.  It SHOULDN'T be (as you are assigning it the value strSQL), but if it is coming back as NULL, you may want to go back a little bit in the code to this line:

    Using mySQLCommand = New SqlCommand(strSQL, myConnection)

    and check the value of strSQL.  If that is NULL, then you will want to go back to this line:

    iCount = GetCountScalar(SQL) //Error message point at the function in this line

    and check the value of SQL as something is goofy there.

    The error is pretty clear though - the value for CommandText (a property of a SqlCommand object) is not initialized and thus null.  The question becomes "why is it not initialized?".

    My coding habit is not to use the Using syntax as I find it more challenging to debug and I can clean things up myself (habit from my C coding days where you have to).  But that is just a style thing; it shouldn't break your code.

  • Thank you for help.

    As your advice, I updated codes without using "Using".

    I have a question.

    Does it need the line "mySQLCommand.CommandType = CommandType.Text" ?

    From the link below, it did not use "mySQLCommand.CommandType = CommandType.Text"

    http://vb.net-informations.com/ado.net-dataproviders/ado.net-executescalar-sqlcommand.htm

     

  • My process when i want to know things like that is to check the docs:

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtype?view=dotnet-plat-ext-3.1

    If you see here, the CommandType default when not assigned a value in this case is Text.  So that line is redundant.  That being said, the link I posted is specific to .NET 3.1, but applies still in 4.8.  But it COULD have the default changed in a future version.  Unlikely, but possible.  If I don't know what value is assigned to an object property by default, I like to assign it IF it needs to be a specific value.  In your case, you are passing query text along, I would leave that line in there.  It causes no harm, will never throw an error and gives peace of mind knowing that in the future if they decide in .NET 5.1 (for example) to change the default to StoredProcedure, your code won't break on you.

    Some people like the "Using" syntax, I personally feel it makes my code look hacky.  It looks like I don't know how to dispose of the object properly so I am just letting the compiler handle it.  That being said, I'm not a huge fan of lambda expressions either.  BUT it is all preference.

    One thing I am not 100% certain on with your code is if using Using statements will call myConnection.Close() before disposing of the object.  You may have orphan SQL connections if you don't close the connection before disposing of the object.

    With the above being said, Using statements like the ones you had and Lambda expressions can make some things easier.  You don't need to manually include all the cleanup code because as soon as you are done with your Using object, it is disposed of.  This may be what you want, or it may dispose of it earlier than you intended.  That is the big reason I'm not a fan of Using statements.  Lambda just feels like you are trying to save keystrokes at the cost of readability (my opinion... some people think Lambda improves readability...).  It all falls down to preference, coding style (if everything else in the application is using Using or Lambda, I'm not going to be the one person who doesn't), and coding standards (some people put stuff like that in the standards, so it may be required when possible).

  • it looks to me like you declare one variable, but use one that never existed a few lines later:

     

    i think you declared "SQL", but dynamically create "strSQL" a few lines later;

    i think VB has an Option strict command that might find those kinds of issues.

    dim SQL as string = "Select count(*) from ORDER where[OrderCity]='London'"
    ...
    Using mySQLCommand = New SqlCommand(strSQL, myConnection)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This error did not occur every time when user hit a button and this is very hard for me to reproduce the same error.

    If I updated code below, is it a good idea?

    Public Function GetCountScalar(ByVal strSQL As String) As Integer

    Dim ret As Int32 = 0

    Dim cnn As SqlConnection

    Dim cmd As SqlCommand

    cnn = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

    cnn.Open()

    cmd = New SqlCommand(strSQL, cnn)

    If cmd.ExecuteScalar() Is Nothing Then

    Return 0

    Else

    ret = Convert.ToInt32(cmd.ExecuteScalar())

    cmd.Dispose()

    cnn.Close()

    Return ret

    End If

    End Function

  • I see one big glaring red-flag in that bit of code - you are calling ExecuteScalar() TWICE!  So you are running the command against the SQL database TWICE!  That will be a performance hit and not beneficial to your code or your clients.

    What I'd do is take out everything below:

    cmd = NEW SqlCommand(strSQL, cnn)

    but leave that cmd part in, and then toss in a TRY CATCH block that will look similar to:

    try
    ret = Convert.ToInt32(cmd.ExecuteScalar())
    catch ex as exception
    messagebox.show(ex.message)
    finally
    cmd.Dispose()
    cnn.Close()
    end try
    Return ret

    Not tabbed over properly, and not 100% certain on that syntax as i typed that up in this code editor off the top of my head.

  • Lowell wrote:

    it looks to me like you declare one variable, but use one that never existed a few lines later:

    i think you declared "SQL", but dynamically create "strSQL" a few lines later;

    i think VB has an Option strict command that might find those kinds of issues.

    dim SQL as string = "Select count(*) from ORDER where[OrderCity]='London'"
    ...
    Using mySQLCommand = New SqlCommand(strSQL, myConnection)

    If I am not mistaken, strSQL is a parameter in the function GetCountScalar.  It is passed ByVal which means that the value is passed into the function parameter strSQL and thus the value of SQL should match the value of strSQL at the function start time.  I say "function start time" because the value of strSQL can be changed without affecting the value of SQL.

    If it was passed ByRef, then changes to strSQL would result in SQL being changed too.

    That being said, strSQL is not being changed once the function is called (in that bit of sample code), so I would not expect there to be an exception of CommandText property has not been initialized.

  • Yes, this my fault. Should be "strSQL"

  • did you get this sorted out?

  • Yes, thank you so much.

    (How to use BBCodes?  I still confused. It came out very ugly)

    Finally, I created a function below.

    Public Function GetCountScalar(ByVal strSQL As String) As Object

    'usage: select count(MRN) from tblAP_CH_4"

    Dim ret As Int32 = 0

    Dim cnn As SqlConnection

    Dim cmd As SqlCommand

     

    cnn = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

     

    cnn.Open()

    cmd = New SqlCommand(strSQL, cnn)

     

    Dim obj As Object = cmd.ExecuteScalar()

     

    If obj Is Nothing Then

    Return 0

    Else

    cmd.Dispose()

    cnn.Close()

    Return obj

    End If

    End Function

    • This reply was modified 6 months ago by  adonetok.
  • Nice!  Thanks for the followup!

    Only thing I see that may be of concern is that if cmd.ExecuteScaler() throws an exception, that exception is going to be passed back to the end user (or up to your TRY/CATCH block), so your "IF obj IS NOTHING THEN" statement will never actually fire.

    I would still put that cmd.ExecuteScalar() into a TRY-CATCH-FINALLY block myself as it could throw errors.  If you have no permission, you may get an exception that will bring you out of that code block and you could end up with orphan spids on the SQL side.

  • Here is my codes.

    I want to reproduce the same error message but I can't.

    Also, I received error message time by time not always when user clicking the button firing the codes

    This made me very hard to fix.

    Please help me to modify code to CREATE the error.

    [Code]

    Try

    Dim strType As String = CStr(Session("sType"))

    Select Case strType

    Case "AAA"

    strSQLCount = "select count(*) from T1

    Case "BBB"

    strSQLCount = "select count(*) from T2

    Case Else

    End Select

    intCount = GetCountScalar(strSQLCount)

    Select Case intCount

    Case 0

    //some code

    Case Is <= 3000

    //some code

    Case Is > 3000

    //some code

    End Select

    Catch ex As Exception

    EmailErrorTrackOut("Error catch in Order.aspx, btnSearch ", ex.ToString())

    End Try

    [/Code]

    • This reply was modified 5 months, 4 weeks ago by  adonetok.
  • to reproduce the error, set strType to a value other than AAA or BBB. This is easy to do in the debugger (breakpoint, add a watch on "strType" and change it to an invalid value or set it to "CCC" (for example) manually prior to the CASE statement).

    You are also missing the closing quotes on your strSQLCount values.

    What I'd probably do is in your ELSE clause for the CASE have a query like "SELECT 0".  that way your strSQLCount will always have a value and when you call GetCountScalar(strSQLCount) you won't get an exception being thrown.

     

  • Thank you.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply