Fieldcount is not right.

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

The following sql statement is used to fill a datareader

Select dbo.SessionsFTP.DateEtHeure AS DateEtHeureRecue,
dbo.PigesRecuesViaFTP.IdentificationDuPoste as CarrierSiteID,
dbo.PigesRecuesViaFTP.NumeroReservoirDuFournisseur as TankId,
dbo.PigesRecuesViaFTP.CodeCarburant as CarrierProductCode,
dbo.PigesRecuesViaFTP.NomDuPoste as CarrierSiteName,
dbo.PigesRecuesViaFTP.ErreursDansEnregistrementRecu as Inconsistencies FROM
dbo.SessionsFTP INNER JOIN dbo.PigesRecuesViaFTP ON
dbo.SessionsFTP.IdSessionFtp = dbo.PigesRecuesViaFTP.IdSessionFtp where
dbo.PigesRecuesViaFTP.IdSessionFtp = 4457 AND ErreursDansEnregistrementRecu
NOT LIKE '' Order by ClientIdDuFournisseur,NumeroReservoirDuFournisseur

There are 6 fields requested from the database and when I execute this sql
statement in my SQL server query analyzer it does return the 6 columns
expected.
When I execute the following code in my vb.net program after filling the
datareader and executing a read command
(so I'm positioned on a record that has data in it, which I can check by
retrieving the contents of any of the columns up to index 4)
Dim myfieldCt as integer
myfieldct = dr.FieldCount

Myfieldct value returned is 5, it should be 6, the number of columns
returned is NOT zero based count.
If I execute a dr.getstring(4) I get the correct value expected from the
datareader, the one in column CarrierSiteName in the sql statement.
If I excute a dr.getstring(5) 'to get the value of the sixth column I get
an error "Index was outside the bounds of the array.". Indicating that the
datareader as not been correctly filled with all the fields requested by the
sql statement. Initially the last field was defined as an Ntext Field in the
database. I thought that might have had something to do with my problem, I
changed it to Nvarchar 255 for testing, same behaviour.

Its as if the last field does not get picked up by executing my sql
statement to fill the datareader. But I don't get any error message except
when I try to access the 6th field in any way.

Can anyone tell me what I need to do to be able to access that darn field. I
been f.... around for two days trying to get my finger on what's wrong here.
It sure starting to look as if there's a bug in the darn datareader class
that needs a workaround. Any help would be greatly appreciated.

Thanks,

Bob
 
You say you changed the ntext column to nvarchar and STILL couldn't read the
column? That doesn't make any sense to me.

What is this part of your SQL statment?

ErreursDansEnregistrementRecu NOT LIKE '' " & " "

I can't make heads or tails out it. :^)

Greg
 
Try testing it by column-name:
DataReader.GetString(DataReader.GetOrdinal("FieldName"))
As it might help you determine what goes wrong.
 
Greg, basically the sql statement says give me the six aliased fields, doing
an inner join on the two tables, where the ftp sessionid is the integer
shown and the ntextfield not like '' (tw3o single quotes, to select those
that contain some text only) have to do it this way because you can't
compare an ntext or picture field in a select statement except using Like.
Don't worry about the SQL statement buddy, it works fine in query analyzer.
The critical point is that even though it works perfecetly in query
analyzer, when it is being used to fill in the datareader in vb net code it
does not do so correctly.
The strange thing also is that if I use the very same sql statement to fill
a dataset instead of a datareader, the dataset table in this case it would
be table indexed at 0 since there is only one, returns all of the fields
correctly.

So I'm really starting to think there's something wrong with the datareader
using OLEDB, I haven't had time to test the sql datareader, I gotta get this
project out, so I decided to use the dataset that works fne instead.

Thanks for your support and merry Cristmas,
Bob
 
I did that, same result, te problemis that although the sql statement is
correct and executes fine in query analyzer, when it is being used in Vb.net
code it does not fill in 6 fields that were requested but only brings back 5
fields.

Bob
 
Back
Top