Member Avatar for aadi_capri

hello friends,
i have a windows project in vb.net, in which i have to connect my project with an excel database i have microsoft office 2007 installed on my lappy..
please help me with the code.
thanks in advance..

  • 4 Contributors
  • 7 Replies
  • 239 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Quick, practical diagnosis and fixes for reading Excel from a VB.NET WinForms app (ties to , , and ).

The symptom in ’s posts — cells that contain plain text appearing as NULL in the DataSet/DataGrid — is most often caused by the OLE DB Excel driver deciding a column’s data type from the first few rows. When a later cell’s type doesn’t match the guessed type the provider returns NULL for that cell. The driver’s behavior is controlled by registry settings (TypeGuessRows / ImportMixedTypes) and by import options supplied to the connection; changing those is the usual way to fix mixed-type columns. (learn.microsoft.com)

Practical checklist (fast triage):

  • Confirm the file format. Older .xls and newer .xlsx use different engines; .xlsx normally requires the ACE engine (Access Database Engine) rather than Jet. Missing or wrong provider / bitness mismatch is a very common cause of failures. Installing the matching Access Database Engine or switching to the proper provider resolves that class of errors. (jeffpar.github.io)
  • If staying with an OLE DB read, influence column typing with the “header / import mode” options and IMEX-like import mode, or set the registry TypeGuessRows/ImportMixedTypes so mixed columns are treated as text — note this may impact performance and is machine-specific. (learn.microsoft.com)
  • Remove unnecessary calls (for example running ExecuteNonQuery for a SELECT is not needed) — use a DataAdapter to Fill a DataSet and inspect the DataTable schema before binding, as suggested.

Alternatives and caveats:

  • Avoid Excel automation (Interop) for unattended or server-side code — Microsoft does not recommend Office Automation on servers. Prefer file-level libraries or drivers. (support.microsoft.com)
  • For robust, driver-free reading consider libraries that parse files directly: NPOI (Apache-2) or EPPlus. Note EPPlus changed its license model (newer major versions have noncommercial/commercial licensing), so pick the version and license that fit the project. (github.com)

If the problem persists after the above checks, inspect the DataTable column types immediately after Fill and try a minimal test workbook (single column with known text values) to isolate driver vs. data issues.

Recommended Answers

All 7 Replies

Member Avatar for aadi_capri

and how can we retrive a string from an excel database in the dataset?

Member Avatar for Pgmer 
Try
            Dim fBrowse As New OpenFileDialog

            With fBrowse
                .Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
                .FilterIndex = 1
                .Title = "Import data from Excel file"
            End With
            If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Dim fname As String
                fname = fBrowse.FileName
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim DtSet As System.Data.DataSet
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                MyConnection = New System.Data.OleDb.OleDbConnection _
                ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fname & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
                MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from[Sheet1$]", MyConnection)
               


                MyCommand.TableMappings.Add("Table", "TestTable")
                DtSet = New System.Data.DataSet
'' Here is dataset with all the data from Excel file.
                MyCommand.Fill(DtSet)
                MyConnection.Close()
            End If


        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
Edited by Pgmer because: n/a
Member Avatar for aadi_capri

dbprovider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 5.0;"
dbsource = "Data Source = C:\Documents and Settings\aditya\My Documents\user2.XLS"

con.ConnectionString = dbprovider & dbsource
con.Open()
SQL = "SELECT * FROM [products$]"
Cmd = New OleDb.OleDbCommand(SQL, con)
objCmd = New OleDb.OleDbCommand(SQL, con)
objCmd.ExecuteNonQuery()
da = New OleDb.OleDbDataAdapter(SQL, con)
'MsgBox("hello connection is opened")
da.Fill(ds, "AddressBook")
con.Close()
DataGrid1.DataSource = ds
DataGrid1.SetDataBinding(ds, "addressbook")

i have used this code and its working good so far but its not able to retrive a string into the datagrid, its giving a value as null, where the text,"hello" is present in the excel sheet.

Member Avatar for Pgmer

In which column the text is? And can you provide the sample excel file? It should read whaterver is there in excel file.

Member Avatar for adam_k

Why do you have these:

objCmd = New OleDb.OleDbCommand(SQL, con)
objCmd.ExecuteNonQuery()

SQL var does contain a query.

And I don't think you are going to need this: Cmd = New OleDb.OleDbCommand(SQL, con) either.

Edited by adam_k because: n/a
Member Avatar for Reverend Jim

Is it an actual database (regularly definied rows and columns) or is it a spreadsheet with varying fields depending on the row? In one case you CAN access the data using ADO (or whatever MS calls it now) and recordsets, etc. If not then you can access the rown and columns using an Excel object that can be created within VB. If it's the second case we can also give you sample code.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.