Reputation: 21
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();
Upvotes: 2
Views: 267
Reputation: 3182
InTouch:SQLTesting = objDR.Read();
while objDR.Read() == true
You are calling Read
twice, so what do you expect to happen?
Read
.Read
with an if
statement.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();
Upvotes: 1
Reputation: 5453
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;
Upvotes: 0