Inserting,updating,deleting data from and to excel files using oled or odbc connection

Question

I have tried everything.but im not able to insert updat and delete the excel file..im able to connect the excel sheet.the connection also seems to be opened...here s my connection string:

 Dim sConnectionString As String
        Const kunal = "C:\"
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & kunal & _
            "login.xls;Extended Properties=Excel 8.0;"
        Dim con As New OleDbConnection(sConnectionString)
        con.Open()
        'MsgBox("hi")

the msgbox comes.so the connection is alrite..but now i just wanna add to details into the excel sheet but there comes the error.. heres my code for inserting:

  Dim cmd As OleDbCommand
        cmd = New OleDbCommand("insert into [Sheet1$] (FirstName,LastName) values('admin','kunal')", con)
        cmd.ExecuteNonQuery()
        con.Close()

This is the error which i get on pageload.. The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. i have provided him the coorect path..spelling is also correct. please help me out.

Answer 1

Maybe look at Linq to Excel

Answer 2

A little off topic but have you looked at http://www.spreadsheetgear.com/?

Answer 3

Sheet1 (without the $) has to be the Worksheet name that you are trying to write to, which also needs column headings for the columns you are writing in the first row. Also make sure you don't have the spreadsheet open in Excel, that would lock it. I tried your syntax and it works fine.

Answer 4

It could still be an issue with the connection, even though it doesn't look like it.

http://support.microsoft.com/kb/316809

If what you are seeing is this bug, it is a misleading exception.