256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
Go Back  Xtreme Visual Basic Talk > > > 256 string length limitation when importing Excel Data into a Dataset


Reply
 
Thread Tools Display Modes
  #1  
Old 01-14-2005, 09:07 AM
barse barse is offline
Newcomer
 
Join Date: Jan 2005
Posts: 8
Default 256 string length limitation when importing Excel Data into a Dataset


Hi,
Does anyone know how to get around the 256 string length limitation when importing excel data into a dataset.

here's my code...(well the relevant bits)

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & filename & ";" & _
"Extended Properties=Excel 8.0;")

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from Sheet1$, MyConnection)

xlsDS = New System.Data.DataSet()
MyCommand.Fill(xlsDS)


For some reason all the datagrid cells are cut to 256 chars even though the excel cell is greater in length.
Reply With Quote
  #2  
Old 01-14-2005, 11:06 AM
ellebanna's Avatar
ellebanna ellebanna is offline
Centurion
 
Join Date: Apr 2003
Location: US, Pennsylvania
Posts: 137
Default

Quote:
Originally Posted by barse
Hi,
Does anyone know how to get around the 256 string length limitation when importing excel data into a dataset.

here's my code...(well the relevant bits)

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & filename & ";" & _
"Extended Properties=Excel 8.0;")

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from Sheet1$, MyConnection)

xlsDS = New System.Data.DataSet()
MyCommand.Fill(xlsDS)


For some reason all the datagrid cells are cut to 256 chars even though the excel cell is greater in length.
What Database are you exporting it to? I am assuming it is an Access database because of the text field limitation. Access Text Type datatype is limited to 255 Characters only. IF you will be inserting more than 255 characters, i suggest you use a memo field instead of a text field.
Reply With Quote
  #3  
Old 01-14-2005, 04:57 PM
MKoslof's Avatar
MKoslof256 string length limitation when importing Excel Data into a Dataset MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

If you are reading in an Excel spreadsheet and you are using a DataSet and DataGrid, I believe you can edit the TableStyles() of the DataGrid object to change the field length and type. I don't recall off hand if the datagrid in .Net has a length restriction. I believe the problem is that the DataGrid is cutting off the value, the DataSet itself should allow this sized string with no problem. Let me do some testing on my end with a datagrid and long string values.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #4  
Old 01-17-2005, 04:10 AM
barse barse is offline
Newcomer
 
Join Date: Jan 2005
Posts: 8
Default

I've checked the dataset and the strings are not being read-in correctly at this stage -.i.e. - they're cut. This is before I attached it to the datatable.
I think it's something to do with the connection to the xls itself - maybe the select statement???
Reply With Quote
  #5  
Old 01-17-2005, 04:11 AM
barse barse is offline
Newcomer
 
Join Date: Jan 2005
Posts: 8
Default

I'm filling the dataset from an xls. The xls has strings over 256 chars in length. These are cut in the dataset intself. Is it something to do with how I'm accessing the xls file??
Reply With Quote
  #6  
Old 01-17-2005, 08:56 AM
barse barse is offline
Newcomer
 
Join Date: Jan 2005
Posts: 8
Smile

I found a solution:-
You must change the 'TypeGuessRows' value in the SOFTWARE\Microsoft\jet\4.0\Engines\excel key from 8 to 0.

This is the number of rows that the oledb object withh scan in the excel file to determine the oledbtype of th excel columns.

Because my frist >255 string is in row 27 the oledb object pressumed all cells <255.

Setting 'TypeGuessRows' to 0 makes the object check EVERY cell...


Thanks for your help guys
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset 256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
 
256 string length limitation when importing Excel Data into a Dataset
256 string length limitation when importing Excel Data into a Dataset
 
-->