Its gotta be a bug or I'm crazy!

[RD]

I am retrieving 6 fields from an SQL server 2000 table with following sql
statement.

SQL = "select Field1, field2, field3, field4, field5, field6 from table1"

Last field ( field 6) is defined in table as ntext.
I put them in datareader named dr.
Dim dc1 As New OleDbCommand(Sql, conn)
Dim dr1 As OleDbDataReader =
dc.ExecuteReader(CommandBehavior.SequentialAccess)
Code works fine to there.
Then I want read the contents of the datareader and write the contens to
a streamwriter called sw1
But when I hit the line below Retval = dr1.GetBytes(5, StartIndex, outbyte,
0, bufferSize)
or if I try dr1.getvalue(5) or any other command that references the 6th
field in my datarow object I get
an error "Index was outside the bounds of the array", yet it is a six item
zero based array, so the value 5 I use is correct
and when I run the SQL statement in the server 2000 query analyzer I get te
expected results.

Can anyone explain what I'm doing wrong if this is not a bug?

Thanks for your help and Mery christmas and happy new year and sorry if
that's not politically correct ;-)
bob

Do While dr1.Read()

If Not IsDBNull(dr1.GetValue(0)) Then
sw1.WriteLine(Tab & Tab & "<Inconsistencies>")
sw1.WriteLine(Tab & Tab & Tab & "<Field1>" &
dr1.GetDateTime(0).ToString & "</Field1>")
sw1.WriteLine(Tab & Tab & Tab & "<Field2>" &
dr1.GetString(1) & "</Field2>")
sw1.WriteLine(Tab & Tab & Tab & "<Field3>" &
dr1.GetValue(2).ToString & "</Field3>")
sw1.WriteLine(Tab & Tab & Tab & "<Field4>" &
dr1.GetString(3) & "</Field4>")
sw1.WriteLine(Tab & Tab & Tab & "<Field5>" &
dr1.GetString(4) & "</Field5>")
Retval = dr1.GetBytes(5, StartIndex, outbyte, 0,
bufferSize)
Do While Retval = bufferSize
sw1.Write(outbyte)
sw1.Flush()

' Reposition the start index to the end of
the last buffer and fill the buffer.
StartIndex += bufferSize
Retval = dr1.GetBytes(5, StartIndex,
outbyte, 0, bufferSize)
Loop

' Write the remaining buffer.
''sw1.Write(outbyte, 0, Retval - 1)
''bw.Flush()
sw1.Write(CrLf)





'sw1.WriteLine(Tab & Tab & Tab &
"<Inconsistency>" & dr1.Item(5) & "</Inconsistency>")
sw1.WriteLine(Tab & Tab & "</Inconsistencies>")
End If
Loop

Then I read eac

 

[Guest]

You're supposed to use GetString() or GetChars()

Refer to:
http://msdn.microsoft.com/library/d...aproviderdatatypestonetframeworkdatatypes.asp

Good luck

 

[Noozer]

For what?

 

[Greg Burns]

He is saying you must use the GetString() or GetChars() method of the
datareader for SQL ntext datatypes.

You appear to be using GetBytes, which is for is for the image SQL datatype

BTW: Great link Rulin!

Greg

 

[RD]

Thanks to those who replied,

It does make any difference what method I use to try to access that column.
I still get the same error message.
If I use dr1.getstring(5) or any other method thta references field index 5
, I get index was outside the bounds of the array on that line, yet as I
said, I DO have 6 fields (indexes 0 to 5) in the sql satement that is used
to get the data from the table. I can read fields 0 to 4 OK but not field 5
and only difference is that its an ntext type.

What follows is the sql statement as its cocatenated in code and used to
populate the datareader. There are 6 fields in the select before the from
clause and this returns the expected data in query analyzer.

Sql = "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 = " & CurrentFtpSession & " " & _
"AND ErreursDansEnregistrementRecu NOT
LIKE '' " & " " & _
"Order by
ClientIdDuFournisseur,NumeroReservoirDuFournisseur"

I read the docs but can't figure out what's wrong here.

Bob

 

[Greg Burns]

After looking into this a bit more, it seems I wasn't very helpful.

Here a couple think I gathered while searching:

ntext columns should always be last in select list (which you are doing)

you "may" want to open the reader with CommandBehavior.SequentialAccess
(which you are doing)

GetBytes seems to be what everyone else is using...

have you taken a look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;316887

I didn't find the answer you are after, but you will have better luck
posting (and searching)

microsoft.public.dotnet.framework.adonet

http://groups-beta.google.com/group/microsoft.public.dotnet.framework.adonet

Greg

 

[Greg Burns]

I realize now that Noozer was not the OP to this thread.

Greg

 

[RD]

Thank you very much greg I really appreciate your effort.
Merry christmas and happy new year
Bob

 

[Guest]

RD,
Theoretically, it's OK to use OleDbCommand, OleDbDataReader. But I suggest
you try SqlCommand, SqlDataReader. I'll appreciate you post your result of
trying.

Good Luck.

 

[RD]

I'm gonna try,
I'll keep the group informed
bob

 

[RD]

I tried all that Greg I still get same results, I checked my sql statement
in query analyzer, I made sure it returns all the expected data on 6 columns
Its Ok.
But when I do a fieldcount in my vb.net code on the datareader after
positioning on the first record I get 5 as an answer. It should be 6 (the
fieldcount is not zero based, its the number of columns in the current
record) so it looks very much like the datareader is not returning the
correct number of columns requested and does not give any error messages.
I can access all the data in the columns 0 to 4 but if I try to access
column 5 I get index out of range, obviously now since the datareader did
not fill the 6th column.

Initially my last column was defined as an Ntext, but for testing I changed
it to Nvarchar (255). Same results.

It looks like there's a bug in the datareader.

When I retrieve my data using a datadapter and a dataset using the same sql
statement I get the correct number of columns and I can write the data to an
XML file and see all the data needed. This is really a weird one!


Bob