field truncates after 255 chars

[Guest]

i am accessing a string value from an access db query that is longer than 255
chars.

the problem:

the field in the datareader only shows 255 chars.

when i run the query in the access query window i get the entire string.

the code is pretty simple:

....
Dim cmd As New OleDbCommand( "sp_invoiceText", con )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "@traID", value ) ' value is an Integer
Dim dr As OleDbDataReader
Dim strInvoice As String
Try
cmd.Connection.Open
dr = cmd.ExecuteReader
dr.Read
strInvoice = dr(0)
....

can someone PLEASE tell me how to fix this?

 

[Cor Ligthert]

Sbhmf

I think this can be an error in your SP however that we cannot see.

Cor

 

[Paul Clement]

¤ i am accessing a string value from an access db query that is longer than 255
¤ chars.
¤
¤ the problem:
¤
¤ the field in the datareader only shows 255 chars.
¤
¤ when i run the query in the access query window i get the entire string.
¤
¤ the code is pretty simple:
¤
¤ ...
¤ Dim cmd As New OleDbCommand( "sp_invoiceText", con )
¤ cmd.CommandType = CommandType.StoredProcedure
¤ cmd.Parameters.Add( "@traID", value ) ' value is an Integer
¤ Dim dr As OleDbDataReader
¤ Dim strInvoice As String
¤ Try
¤ cmd.Connection.Open
¤ dr = cmd.ExecuteReader
¤ dr.Read
¤ strInvoice = dr(0)
¤ ...
¤
¤ can someone PLEASE tell me how to fix this?

If the data type is Memo then you have to use GetChars to read the full contents of the field. Here
is an example:

Sub ReadMemoFromAccess()

Dim retVal As Long
Dim FieldLen As Int32

Dim MemoCol As Integer = 1 ' the column # of the Memo field in the query
Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("SELECT [record ID], MemoField FROM Table1 WHERE
[record id] = 1", AccessConnection)
AccessConnection.Open()
Dim dr As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
dr.Read()
FieldLen = dr.Item(MemoCol).Length
Dim MemoBuffer(FieldLen - 1) As Char
Dim startIndex As Integer = 0
retVal = dr.GetChars(1, startIndex, MemoBuffer, 0, MemoBuffer.Length)
Console.WriteLine(MemoBuffer)
dr.Close()
AccessConnection.Close()

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)