I am writing some script to read the sql query result at Intouch environment, it's not exactly C# language but similar. I just want to get the "1" stored in my "SQLTest" variable (Define as a string data type).

Here's the result of my sql query

And here is my code:

Dim objDB As System.Data.SqlClient.SqlConnection;
Dim objCmd As System.Data.SqlClient.SqlCommand;
Dim objDR As System.Data.SqlClient.SqlDataReader;
Dim objTbl As System.Data.DataTable;
Dim sDBConnStr As String;
Dim sSQL As String;
Dim bOk As Boolean;
sDBConnStr = "Server=Desktop-3J641FK;Database=Runtime;Integrated Security=True;";
'' Connect and open the database
objDB = New System.Data.SqlClient.SqlConnection(sDBConnStr);
objDB.Open();
sSQL = "SELECT sum (case when EventLogKey = '5' and DetectDateTime between '2022-07-21 11:00:20' and '2022-07-25 11:00:20' then 1 else 0 end) FROM [Runtime].[dbo].[EventHistory]";
'' Invoke the SQL command
objCmd = New System.Data.SqlClient.SqlCommand(sSQL, objDB);
'' Retrieve the queried fields from the query into the reader
objDR = objCmd.ExecuteReader();

InTouch:SQLTesting = objDR.Read();

while objDR.Read() == true

InTouch:SQLTest = objDR.GetValue(0).ToString;

endwhile;


objDR.Close();
objDB.Dispose();
objCmd.Dispose();

CodePudding user response：

InTouch:SQLTesting = objDR.Read();

while objDR.Read() == true

You are calling Read twice, so what do you expect to happen?

• If there will be exactly one row, just call Read.
• If there will be zero or one row, call Read with an if statement.
• If there may be more than one row, call Read with a while loop.

Do one and only one of the above. If there might be more than one row and you want to do something different if there are no rows, use the HasRows property first, then use the while loop.

Having said all that, if there will only be one value in the result set then you should be calling ExecuteScalar, so the data reader is irrelevant:

InTouch:SQLTest = objCmd.ExecuteScalar().ToString();

CodePudding user response：

I think your While loop is unnecessary here as you Read the sqldata reader already before While and you are using Sum in your sql which will return one value always. Try this :

InTouch:SQLTesting = objDR.Read();

InTouch:SQLTest = objDR.GetValue(0).ToString;