SqlDataReader.GetValue(0) cannot get anything

Question

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();

John · Accepted Answer · 2022-07-28 05:42:02Z

1

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();

answered Jul 28, 2022 at 5:42

John

3,2021 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shing9301 Over a year ago

Because I need to have a variable to save the result of objDR.Read() to trace the issue, because Intouch/Wonderware script don't have any script troubleshooting tools for me. Anyway thanks for your help, I have found the issue of my script - The data type of the sql query output is an int32 instead of a string.

Md. Suman Kabir · Accepted Answer · 2022-07-28 05:41:18Z

0

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;

answered Jul 28, 2022 at 5:41

Md. Suman Kabir

5,4515 gold badges29 silver badges45 bronze badges

Collectives™ on Stack Overflow

SqlDataReader.GetValue(0) cannot get anything

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related