how to read SQL column for a row, by specifying the column name, in Visual Basic ASP.NET Web Application project

Question

I have a table in a SQL Server 2008 database.

How can I read a row's column value by specifying the column name in a SqlDataReader after doing a SELECT * FROM table WHERE <row selection>?

The error is: "Additional information: Invalid attempt to read when no data is present." if I do .GetString before .Close() and "Invalid attempt to call CheckDataIsReady when reader is closed." if I close then do GetString. Or should it be GetSqlString()?

Here's my SQL command:

SELECT * FROM CLIENT_JOBS_TABLE WHERE CLIENT_ID = 'JOES_BURGERS' AND JOB_ID = 'FRY_COOK_2014_07_05'

In my code below, I get an error trying to read the value of column JOB_ID.

some_data = query_result2.GetString(field_ordinal)    '  field_ordinal=1

..gets error: "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" ..and 'some_data' is set to nothing

Private Function query_SQL_database(ByVal sql_text_command As String, ByRef query_result As SqlDataReader)
   ' Open the connection
   Debug.WriteLine(sql_text_command)
   ConnectionString = "Data Source=66.7.250.100;Initial Catalog=ehiresdb;Persist Security Info=True;User ID=Doug;Password=ThankYou!!"
   Dim connection As New SqlConnection(ConnectionString)
   connection.ConnectionString = ConnectionString
   connection.Open()
   SQL_command = New SqlCommand(sql_text_command, connection)
   Dim command_successful = True
   On Error Resume Next

   Dim query_result2 As SqlDataReader
   query_result2 = SQL_command.ExecuteReader()
   If Err.Number Then
       command_successful = False
       Dim reason = Err.Description
       MsgBox(Err.Description, MsgBoxStyle.Exclamation, "issue_SQL_command")
   Else
       command_successful = True
   End If

query_result2.Read() ' <<<<<<<<<<<<<<<<<<<<<<<<<<<< SOLUTION!

   field_ordinal = query_result2.GetOrdinal("CLIENT_ID")
   field_ordinal = query_result2.GetOrdinal("JOB_ID")
   some_data = query_result2.GetString(field_ordinal)       <<<<< FAILS BEFORE THE .CLOSE

   connection.Close()



   Return command_successful
End Function  'issue_SQL_command

Steve · Answer 1 · 2014-07-12T22:43:50.610

1

There is no (in System.Data.SqlClient namespace) a version of the reader.GetInt32 (or GetString, GetBoolean, etc...) that accepts the string name of the column. You could retrieve the value of the column using the ordinal position and, from the value returned by GetOrdinal you could get the value of the corresponding column.

Your code above has another problem. After the creation of the reader you need to call the Read() method that position the reader on the first record returned by your query. The call returns True id the query produced any rows to read otherwise your query has not returned any record.

if query_result.Read() then
    job_table_client_id = query_result.GetOrdinal("JOB_TABLE_CLIENT_ID")   
    return query_result.GetString(job_table_client_id)
else
    return string.Empty
End If

However you could write an extension method in VB.NET

Imports System.Runtime.CompilerServices

Module SqlDataReaderExtensions

    <Extension()> 
    Public Function GetInt32(ByVal reader As SqlDataReader, ByVal columnName As String) as Int32
        Dim result As Integer = 0
        Dim pos = reader.GetOrdinal(columnName)
        if pos >= 0 then
             result = reader.GetInt32(pos)
        End If
        return result
    End Function

    <Extension()> 
    Public Function GetString(ByVal reader As SqlDataReader, columnName As String) as String
        ......
    End Function

End Module

And now you could call

return query_result.GetInt32("JOB_TABLE_CLIENT_ID")

Notice that this is just an example. I have made no tests and more important, there is no safe handling of null values in column or any kind of protection if you try to pass inexistant column or column that contains values of different data type

edited Jul 12 '14 at 22:43

answered Jul 12 '14 at 16:14

Steve

213,761
22
232
286

thanks; I added .GetOrdinal() as you suggested but still get error. I updated code with my latest. – Doug Null Jul 12 '14 at 22:06
Did you close the connection and then try to read from the reader or is it just a typo? – Steve Jul 12 '14 at 22:08
Tried both. I'm supposed to close reader first, right? Also, the .GetString is generating this error in Output window: "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" And actually the .GetOrdinal generates that error too. – Doug Null Jul 12 '14 at 22:24
And the SQL command is: "SELECT * FROM CLIENT_JOBS_TABLE WHERE CLIENT_ID = 'NATIONS_BURGERS' AND JOB_ID = 'FRY_COOK_2014_07_05'" – Doug Null Jul 12 '14 at 22:32
If you look closer at the exception there is an InnerException with more details about the error – Steve Jul 12 '14 at 22:35
Probably you are missing the call to `query_result.Read()`. Without that call the reader is not positioned correctly on the first row to read (if any is returned by the query) – Steve Jul 12 '14 at 22:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/57214/discussion-between-doug-null-and-steve). – Doug Null Jul 12 '14 at 23:13

score 0 · Accepted Answer · answered Jul 13 '14 at 13:37

0

I got it working.

SOLUTION 1:

I was missing the line:

query_result.Read()

before the line with .GetString

SOLUTION 2:

Also, must retrieve the column value BEFORE the connection.Close() using this:

query_result.GetString(query_result2.GetOrdinal(""))

answered Jul 13 '14 at 13:37

Doug Null

7,989
15
69
148

With due respect I can't understand why you post your own answer when I have given you all the help required to pinpoint the problem in my answer. Ah well, wasted time. – Steve Jul 14 '14 at 16:37

how to read SQL column for a row, by specifying the column name, in Visual Basic ASP.NET Web Application project

2 Answers2