July 22, 2020 at 6:49 pm
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
July 23, 2020 at 6:14 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 23, 2020 at 6:47 pm
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
July 23, 2020 at 7:43 pm
My process when i want to know things like that is to check the docs:
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).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 24, 2020 at 7:26 pm
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
July 24, 2020 at 7:42 pm
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
July 24, 2020 at 7:56 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 27, 2020 at 5:18 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 27, 2020 at 9:11 pm
Yes, this my fault. Should be "strSQL"
July 27, 2020 at 9:20 pm
did you get this sorted out?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 27, 2020 at 9:44 pm
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
July 27, 2020 at 9:54 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 28, 2020 at 3:27 pm
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]
July 28, 2020 at 5:43 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 28, 2020 at 7:08 pm
Thank you.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply