Help with the Use of SqlDataReader (VB)

G

GornHorse

Well-known member
Joined
May 27, 2003
Messages
105
Location
Australia
Help with the Use of SqlDataReader

Hi There,

What i need to do is retrieve each field from a select query individually into a variable for use later on.

This is my code as it stands so far...

try
do while myReader.Read
val1 = myReader.GetString(1)
val2 = myReader.GetString(2)
val3 = myReader.GetString(3)
loop
catch ex as system.exception
msgbox(ex.message)
end try
myReader.close()

Unfortunately, when it gets to retrieving 2, it throws an exceptionsaying that it is null, and therefore will not go on to retrieve val3. The thing thats really odd about this, is that i know for a fact that 2 is not null at all.

So, what i think i might need to do is create an array of what myReader reads, but i have absolutely no idea how to do this, and how then to access the values within the array.

My select query is " select * from table1 where id = 00001 ".

Your help with this will be much appreciated.

Regards,
Michelle
:confused:
 
Hi Michelle..

How many columns are there in the table1 table?
The value you specify in the GetString() method points to a zero based column reference. This means you must start with 0 and end with 2.
Another problem might be with the types of the columns?

Hope it helped :)
 
There is no problem with sqlDataReader, I will rather suggest you use "myReader.Item("Mycolumn")" rather than your GetString...

Unless you dont know your column name?
 
You should pass the name of the column to the GetOrdinal method of the current instance of the SqlDataReader, and then proceed to pass that to the individual method that returns the unique .NET type.
Code: 
Dim sField As String

Try
    Dim i As Integer = dataReader.GetOrdinal("fieldName")
    sField = dataReader.GetString(i)
Catch ex As IndexOutOfRangeException
    Column does not exist
End Try
It is considered better practice to use each columns name instead of their index, since reordering even one column will break any data procedure. This is trade-off performance wise, but its a smart choice nonetheless.
 
Hmm, never knew there was a thing like GetOrdinal. Ive been using enumerators, good to know I can stop doing that now.
 
Code: 
Dim sField As String

Try
    Dim i As Integer = dataReader.GetOrdinal("fieldName")
    sField = dataReader.GetString(i)
Catch ex As IndexOutOfRangeException
    Column does not exist
End Try

What different if I use dataReader.Item("fieldname") ?
 
My understanding is that theres some overhead in using the Item property because it has to convert the data retrieved into a generic object, then back into whatever type you cast it to (as the Item property obviously returns an object). Using the Getxxx is much speedier.
 
Back
Top