excel .net 2.0

[fmardani]

Hi,
Trying to read the data from an excel spreadsheet in C# 2005, but there is an error as follows:
The Microsoft Jet database engine could not find the object 'Cities$'. Make sure the object exists and that you spell its name and the path name correctly.

The excel speadsheet is placed in the same place as Program.cs and the sheet name is indeed cities.

This is the code:

Private Sub btnExcel_Click(sender As Object, e As EventArgs)
Dim connectionString As String = ""
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")

Dim connection As DbConnection = factory.CreateConnection()
Try
connection.ConnectionString = connectionString

Dim command As DbCommand = connection.CreateCommand()
Try
' Cities$ comes from the name of the worksheet
command.CommandText = "SELECT ID,City,State FROM [Cities$]"

connection.Open()

Dim dr As DbDataReader = command.ExecuteReader()
Try 'fails here...
While dr.Read()
Console.WriteLine(dr("ID").ToString())
End While
Finally
dr.Dispose()
End Try
Finally
command.Dispose()
End Try
Finally
connection.Dispose()
End Try
End Sub 'btnExcel_Click

[stanav]

You need to supply the connection string to your connection.
'You had
Dim connectionString As String = ""
'Then later you set
connection.ConnectionString = connectionString
'So the connection string of your connection is empty.

[RobDog888]

Moved to the C# forum.


You should use this connectionstring.

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";