Working with Excel Files

[mike55]

Hi all

Am messing about with vb.net and Excel files in that i am trying to pull data from the excel file and insert data into the file. now i have managed to select the data in the .xls file, but i am running into some problems when i try to insert data into the file. Here is the code i use the insert data

 Dim DS As System.Data.DataSet
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim MyConnection As System.Data.OleDb.OleDbConnection

    MyConnection = New System.Data.OleDb.OleDbConnection( _
      "provider=Microsoft.Jet.OLEDB.4.0; " & _
      "data source=C:\Documents and settings\modonnell.itec\my Documents\Excel_Trial.XLS; " & _
      "Extended Properties=Excel 8.0;")
     Select the data from Sheet1 of the workbook.
    MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
      "select * from [Sheet1$C4:E14]", MyConnection)

    DS = New System.Data.DataSet
    MyCommand.Fill(DS)
    MyConnection.Close()
    Return DS
    End Function

While for the insert statement i am doing the following:

    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim MyConnection As System.Data.OleDb.OleDbConnection

    MyConnection = New System.Data.OleDb.OleDbConnection( _
      "provider=Microsoft.Jet.OLEDB.4.0; " & _
      "data source=C:\Documents and settings\modonnell.itec\my Documents\Excel_Trial.XLS; " & _
      "Extended Properties=Excel 8.0;")
     Select the data from Sheet1 of the workbook.
    MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
      "insert into [Sheet1$B15:E15] values (1, Mike, RR55, WORKING ", MyConnection)

    MyConnection.Close()
    End Function

Any suggestions welcomed, not even sure if you can do an insert statement. Am working with a web project not a windows project.

Mike55

 

[bri189a]

I really, really, suggest not using an Excel document as a datasource. As far as what your doing wrong...and Im going out on a limb... try using the command builder, or the data adapter wizard... it may have something that will code that all out for you; finding the answer to your question will be difficult because people just dont use Excel documents for databases... thats why they have Access or real databases... I know from your other post that this isnt your call... but remember golden rule #1; its okay to turn down a project... And if explaining to them the reason they should use a real database (Access is probably all theyll want to use) and the fact that you have a lot more benifits and data integerity options that dont exist in Access, they still want to go Excel...run for the hills brother! Also, if your building a web app, they shouldnt be accessing the database (whether Excel, Access, MySql, MSDE, or whatever) directly (which is why I think they want the Excel), they should be going through the web like everyone else which would take the need for Excel (or Access) out of the picture and they could use MSDE (which is free).