Error when writing to excel worksheet using vb.net

I am getting an error message when I am attempting to write to an excel worksheet using VB.net 2005

Operation must use an updateable query

I have 开发者_JAVA百科gone thru the S/O search results

https://stackoverflow.com/search?q=Operation+must+use+an+updateable+query,

but no response resolve my issue.

My code is:

Dim strXLScn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlsFilePath.Text & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

        Dim XLConn As New OleDb.OleDbConnection(strXLSconn)
        Dim XLcmd As New System.Data.OleDb.OleDbCommand
        Dim sSQLWriteToExcel As String

        strExcel = "INSERT INTO [Sheet1$A1:A1] Values ('" & sCity & "')"

        Dim oleCMD As New OleDb.OleDbCommand(strExcel, XLConn)
        Dim oleDA As New OleDb.OleDbDataAdapter(oleCMD)

        XLConn.Open()
        XLcmd.Connection = XLConn

        XLcmd.CommandText = strExcel
        XLcmd.ExecuteNonQuery()
        XLConn.Close()

Any ideas?

First things first, make sure you have Option Explicit ON, this will help with variables that you are trying to use that you never declared. Second, remove the IMEX=1 from the end of your connection string. Third, make sure your excel document has a header row. This code worked for me:

     Dim strXLScn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\Temp\test.xls"";Extended Properties=""Excel 8.0;HDR=Yes"""

    Dim XLConn As New OleDb.OleDbConnection(strXLScn)
    Dim XLcmd As New System.Data.OleDb.OleDbCommand
    Dim strExcel As String
    Dim sCity As String = "Sydney"
    strExcel = "INSERT INTO [Sheet1$] Values ('" & sCity & "')"

    Dim oleCMD As New OleDb.OleDbCommand(strExcel, XLConn)
    Dim oleDA As New OleDb.OleDbDataAdapter(oleCMD)

    XLConn.Open()
    XLcmd.Connection = XLConn

    XLcmd.CommandText = strExcel
    XLcmd.ExecuteNonQuery()
    XLConn.Close()

(Appoligies this isn't in VB, rather C#. But I think it's simple and short enough one should be able to convert easily into VB.)

I found this post useful along with a few others. Here's what I came up with. It works in VS2010.

Please note the Excel worksheet has headers. Therefore, "HDR=Yes" in the connect string.

Here is how I would format the string in C#. It uses a String variable (agency) and a structure (address):

"INSERT INTO [List of Agencies$] ([Agency], [Street1], [Street2], [City], [Postal Code], [Country Code]) VALUES('" + agency + "', '" + Address.Street1.Replace("'", "''") + "', '" + Address.Street2.Replace("'", "''") + "', '" + Address.City.Replace("'", "''") + "', '" + Address.PostalCode.Replace("'", "''") + "', '" + Address.Country.Replace("'", "''") + "')"

Note that the sheet is called out ("List of Agencies"), each field/header (e.g. "[Agency]") is bracketed, and the String.Replace method is used to handle embedded single quote (tick) marks.

Cheers!

public void InsertExcelData(String fileName, String sqlString)
{
    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;\";";

    OleDbConnection connection = new OleDbConnection(connectionString);

    OleDbCommand command = new OleDbCommand(connectionString, connection);

    command.CommandText = sqlString;

    // Connect to the worksheet
    connection.Open();

    // Do the Insert
    command.ExecuteNonQuery();

    // Clean up
    connection.Close();
    connection.Dispose();
}