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
  • 222 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Reverend Jim
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.