return string of more than 255 chars

  • Thread starter Thread starter Igor
  • Start date Start date
I

Igor

How to get more than 255 characters from a database using
any of ADO.Net numerous possibilities?

I created a field "Big" of type String in a table "Huge"
(Microsoft Access) with the size of 250. I've put a text
string "AAA...ZZZ" of length 250 into it.
The sql statement I want to use is: "SELECT '7777777777' &
Big FROM Huge". I want to get back the
string "7777777777AAA...ZZZ" which should contain now 260
characters. But instead I am getting the truncated string
of 255 characters.

I tried different approaches utilizing (*) OleDBDataSet,
(**) OleDBDataReader, (***) OleDBAdapter which fills a
DataTable with a field "Big" of DataType = Object. All
ways bring the same result - just 255 characters.

The correct result can be reached by using DAO recordsets.
Does anybody know how to solve the problem using ADO.Net?

Thanks,

Igor
 
¤ How to get more than 255 characters from a database using
¤ any of ADO.Net numerous possibilities?
¤
¤ I created a field "Big" of type String in a table "Huge"
¤ (Microsoft Access) with the size of 250. I've put a text
¤ string "AAA...ZZZ" of length 250 into it.
¤ The sql statement I want to use is: "SELECT '7777777777' &
¤ Big FROM Huge". I want to get back the
¤ string "7777777777AAA...ZZZ" which should contain now 260
¤ characters. But instead I am getting the truncated string
¤ of 255 characters.
¤
¤ I tried different approaches utilizing (*) OleDBDataSet,
¤ (**) OleDBDataReader, (***) OleDBAdapter which fills a
¤ DataTable with a field "Big" of DataType = Object. All
¤ ways bring the same result - just 255 characters.
¤
¤ The correct result can be reached by using DAO recordsets.
¤ Does anybody know how to solve the problem using ADO.Net?
¤

The following uses as an OleDbDataReader:

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] = 10", 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)
 
Paul,

thank you for the prompt reply.

You solution works for the field of type "Memo"
into which more than 255 characters have been placed
at once.

But it does not work for the case presented
in my question: the field is of type "Text" (String),
and we concatenate additional characters right within
the sql statement. Could you please try your solution
for the case described in my original question - 250
characters within the field of type "Text" concatenated
with additional 10 characters within sql statement?

It just happened that we've got a number of queries where
we concatenate a few fields (of "Text" type), and the
databases are deployed at numerous machines at client
sites. So, I cannot change the design of database. All I
want is to write the .Net code to get the data.

Thanks,

Igor
-----Original Message-----
¤ How to get more than 255 characters from a database using
¤ any of ADO.Net numerous possibilities?
¤
¤ I created a field "Big" of type String in a table "Huge"
¤ (Microsoft Access) with the size of 250. I've put a text
¤ string "AAA...ZZZ" of length 250 into it.
¤ The sql statement I want to use is: "SELECT '7777777777' &
¤ Big FROM Huge". I want to get back the
¤ string "7777777777AAA...ZZZ" which should contain now 260
¤ characters. But instead I am getting the truncated string
¤ of 255 characters.
¤
¤ I tried different approaches utilizing (*) OleDBDataSet,
¤ (**) OleDBDataReader, (***) OleDBAdapter which fills a
¤ DataTable with a field "Big" of DataType = Object. All
¤ ways bring the same result - just 255 characters.
¤
¤ The correct result can be reached by using DAO recordsets.
¤ Does anybody know how to solve the problem using ADO.Net?
¤

The following uses as an OleDbDataReader:

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
Click to expand...
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("SELECT
Click to expand...
[record ID], MemoField FROM Table1 WHERE
[record id] = 10", 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)
.
Click to expand...
Click to expand...
 
¤ Paul,
¤
¤ thank you for the prompt reply.
¤
¤ You solution works for the field of type "Memo"
¤ into which more than 255 characters have been placed
¤ at once.
¤
¤ But it does not work for the case presented
¤ in my question: the field is of type "Text" (String),
¤ and we concatenate additional characters right within
¤ the sql statement. Could you please try your solution
¤ for the case described in my original question - 250
¤ characters within the field of type "Text" concatenated
¤ with additional 10 characters within sql statement?
¤
¤ It just happened that we've got a number of queries where
¤ we concatenate a few fields (of "Text" type), and the
¤ databases are deployed at numerous machines at client
¤ sites. So, I cannot change the design of database. All I
¤ want is to write the .Net code to get the data.

You can't perform the concatenation in the SQL statement for a Text field if the number of
characters exceeds 255 without the result being truncated. This is a limitation of the Access Text
database type.

If you need to perform the concatenation it will have to take place outside of the SQL query and be
assigned to a String variable type.


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