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();
}