?
Solved

VB 2005 SQL query 255 character limit.

Posted on 2007-08-06
15
Medium Priority
?
721 Views
Last Modified: 2012-05-05
Hey guys, looking for some help with a VB.net app. My problem is I cant get more than 255 characters out of any of my fields.  I have the following connection for a CSV connection.
Cn1.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "DefaultDir=" & dirPath
Cn1.Open()

Some stuff happens. Too much code to paste.

Dim RS As New ADODB.Recordset
If fld = "" Then
            Exit Function
        End If
        cmb.Items.Clear()
        ' select distinct value for view in combo
        RS = Cn1.Execute("select distinct([" & fld & "]) from [" & filePath & "]")
        While RS.EOF = False
            cmb.Items.Add(RS(0).Value)
            RS.MoveNext()
            MsgBox(RS(0).Value)
            MsgBox(RS(0).GetChunk(4000).ToString.Length)
        End While

Both MsgBox's will only return 255 characters. Can you tell me how to allow more than 255 characters?

0
Comment
Question by:kanus
  • 6
  • 4
  • 2
  • +3
15 Comments
 
LVL 6

Assisted Solution

by:hatem72
hatem72 earned 200 total points
ID: 19642213

Hi,

u can use quikwatch if you are try to trace your code ... or textbox .
0
 

Author Comment

by:kanus
ID: 19642338
Hello, thanks for your response. The code works, but I'm not getting results over 255 characters. I dont know how your suggestion can help me with this problem. Can you elaborate?
0
 
LVL 11

Assisted Solution

by:AkisC
AkisC earned 200 total points
ID: 19642578
What is the length of RS(0).Value?

Dim aStr as string=ctype(RS(0).Value,string)
Dim x as integer=aStr.Length
msgbox(x)
if x=255 then then RS(0).Value is 255 chars long, so the msgbox will display 255

The maximum length of Prompt in a msgbox is approximately 1024 characters, depending on the width of the characters used
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:kanus
ID: 19642612
Maybe I'm not explaining what I need well....

I have a CSV file. RS(0).Value does = 255 characters. However if I open the CSV file, the text I am refering to in RS(0).Value is more like 1000 characters long. So I need to do something to allow RS(0).Value show more than 255 characters.
0
 
LVL 39

Assisted Solution

by:appari
appari earned 800 total points
ID: 19642685
try like this,


Dim strData As String    
Dim data As String    
Dim NumBlocks As Integer
Dim LeftOver As Long
dim BlockSize As Long, ColSize As Long
BlockSize = 1024
ColSize= 4000
data=""

NumBlocks = ColSize \ BlockSize
LeftOver = ColSize Mod BlockSize


While RS.EOF = False
            cmb.Items.Add(RS(0).Value)
            RS.MoveNext()
For i = 1 To NumBlocks
      strData = String(BlockSize, 32)
        strData = RS(0).GetChunk(BlockSize)
      data = data & strdata.trim()
Next i
msgbox( data)
'            MsgBox(RS(0).Value)
'            MsgBox(RS(0).GetChunk(4000).ToString.Length)
End While
0
 

Author Comment

by:kanus
ID: 19642727
Thanks for the response, VB is telling me this is incorrect. "Class cannot be used as an expression"

strData = String(BlockSize, 32)
0
 
LVL 39

Assisted Solution

by:appari
appari earned 800 total points
ID: 19642780
you are using vb.net?

seeing getchunk and ado recordset i thought you were using vb6.0

try changing
strData = String(BlockSize, 32)
to
strData = new String(" ",BlockSize)
0
 

Author Comment

by:kanus
ID: 19642953
Thanks, still only getting the first 255 though.
0
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 600 total points
ID: 19643674
Are you using vb.net or vb6 ?
0
 
LVL 14

Assisted Solution

by:wsh2
wsh2 earned 200 total points
ID: 19643840
Remove the DISTINCT clause in your CSV select.. and you'll get all your data.
0
 
LVL 39

Assisted Solution

by:appari
appari earned 800 total points
ID: 19643950
try to create schema.ini file with following contents and save the ini file to the same folder where you have your csv files copied. change [csvFileName.csv] with the csv file name you want to read and fldName with the field name. see if you can read data larger than 255 chars or not.

[csvFileName.csv]
ColNameHeader=TRUE
Format=FixedLength
CharacterSet=ANSI
Col1=fldName text width 2000
0
 
LVL 39

Assisted Solution

by:appari
appari earned 800 total points
ID: 19643960
0
 

Author Comment

by:kanus
ID: 19650179
Thank you all for your help.

Yes its VB.NET 2005

Removing the "Distinct" clause gives me a result from each record but I'm still limited to 255 characters on the result.

I got the schema.ini working, now what happens is it gives me an error stating the result is too long for the text driver. So realistically the only way I'm gonna get this to give me all the characters is if I change the connection?
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 600 total points
ID: 19651687
I usually use this procedure to load a csv-file and I don't have any problem with fields with strings longer than 255 chars...
(there can be a little error on it, because i translated it from c# into vb.net)

''' <summary>
''' Function to load a CSV-file into a datatable
''' </summary>
''' <param name="strFilename">FileName we want to load into a datatable</param>
''' <param name="bHasHeader">Bool to say if there are column-names</param>
''' <returns>Datatable containing the contents of the csv-file</returns>
Public Shared Function LoadCSVFile(ByVal strFilename As String, ByVal bHasHeader As Boolean) As DataTable
Try
      If System.IO.File.Exists(strFilename) = False Then
            Throw New Exception("ERROR: File does not exist (or not enough rights) on the given location!")
      End If

      Dim path As String = System.IO.Path.GetDirectoryName(strFilename)
      Dim file As String = System.IO.Path.GetFileName(strFilename)
      Dim strConn As String = ""
      If bHasHeader Then
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=""text;HDR=Yes;IMEX=1;FMT=Delimited"""
      Else
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=""text;HDR=No;IMEX=1;FMT=Delimited"""
      End If

      Dim dt As New DataTable()
      Using con As New OleDbConnection(strConn)
            Using com As New OleDbCommand("select * from [" + file + "]", con)
                  Using da As New OleDbDataAdapter(com)
                        Try
                              con.Open()
                              da.Fill(dt)
                              con.Close()
                              com.Dispose()
                              con.Dispose()
                        Catch ex As Exception
                              Throw ex
                        Finally
                  If con.State = ConnectionState.Open Then
                        con.Close()
                  End If
            End Try
      End Using
      End Using
      End Using
      Return dt
Catch ex As Exception
      Throw ex
End Try
End Function
0
 

Author Comment

by:kanus
ID: 19667371
Thank you for the help guys, I figured out what the problem was. It was the type of CSV file I was using, for some reason when I export my CSV file from the ticketing application using CSV format and comma seperated values option it doesnt give me the full text, when I choose tab deliminated it works fine.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question