2

i was copying my data from Excel to datagridview by using SELECT statements. But it wasn't show well..

The data show like this

enter image description here

but my excel data

enter image description here

my code :

Dim CON As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""D:\OTDR_DATA\" & TextBox1.Text & "\PRODUCT_CAREER.XLS"";Extended Properties=""Excel 8.0;HDR=YES"";")
Dim DA As New OleDbDataAdapter("SELECT * FROM [PRODUCT_CAREER$]", CON)
DA.Fill(DS)
DataGridView1.DataSource = DS.Tables(0)

Does anyone know why this can be? i have search on the google, but not a such question like this..

Kasnady
  • 2,249
  • 3
  • 25
  • 34
  • How many cells in "Parent Parts" column have numbers only before that numeric code is encountered? – Yuriy Galanter May 16 '13 at 02:02
  • The numberic column first, all have 2070 rows in the Parent, I also have tried to change the 73502 became A735032 in the first row. But result still be same – Kasnady May 16 '13 at 02:04
  • Show 'transfering' code .. – matzone May 16 '13 at 02:05
  • what is the transferring code? That is the code to show the data from Excel to my datagridview.. Just only that already work – Kasnady May 16 '13 at 02:07
  • Try making the very first cell in column "Parent Parts" alphanumeric, e.g. Axxxxxx. Not the last cell before B8070HB, but the very first cell in the column – Yuriy Galanter May 16 '13 at 02:08
  • I have tried it. A73502. But, it became the first cell cannot be show up – Kasnady May 16 '13 at 02:08
  • Code that DS.tables(0).rows(x) that copied to datagrid .. – matzone May 16 '13 at 02:13
  • 1
    This question has your answer. It's because the Jet engine scans the rows to determine type. http://stackoverflow.com/questions/298726/accessing-excel-spreadsheet-with-c-sharp-occasionally-returns-blank-value-for-so – Jesse May 16 '13 at 02:13
  • @matzone That's all the code i have to copy,, no more again.. – Kasnady May 16 '13 at 02:15
  • Please don't SHOUT your questions. Posting them IN ALL CAPS makes them harder to read, and it's annoying. (It's also considered rude to SHOUT.) You won't get an answer any faster by typing in CAPS. The Shift keys are on your keyboard for a reason; posting in proper case makes text easier to read and understand. Please use them. Thanks. – Ken White May 16 '13 at 02:15
  • Ok,, thanks for your advice. But i got reply soon even it's CAPS. LOL – Kasnady May 16 '13 at 02:17
  • @Jesse Hello, +1 for you suggestion sir.. Thanks, but that solution can't solve in my problem. Thanks for your time sir.. And thanks all for who had comment.. ^^ – Kasnady May 16 '13 at 02:38

1 Answers1

1

Excel driver determines type of data in the column by first 8 (by default) rows. I have a feeling, that you have a lot of numeric data in that column before alphanumeric is encountered.

To change that behavior you need to set Registry key TypeGuessRows to 0. The location of the key depends on which driver you use in your connection string (Jet, AccessConnectivityEngine etc.)

Here is detailed instruction on how to change the setting.

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136