Help with the Use of SqlDataReader (VB)

[GornHorse]

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 that's 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:

[Cywizz]

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 :)

[bungpeng]

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

 

Unless you don't know your column name?

[Derek Stone]

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.

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 column's 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.

[wyrd]

Hmm, never knew there was a thing like GetOrdinal. I've been using enumerators, good to know I can stop doing that now.

[bungpeng]

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") ?

[wyrd]

My understanding is that there's 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.