?
Solved

VB.NET - OleDb write to Excel file works in XP but gives "must use updateable query" error in Windows 7

Posted on 2011-05-04
3
Medium Priority
?
568 Views
Last Modified: 2012-05-11
I'm writing a VB.NET application in VS2008 that uses Excel as a back-end to store some simple data. The entire application code is attached below. On Windows XP, this works exactly as intended, but on Windows 7, I get the following error:

System.Data.OleDb.OleDbException: Operation must use an updateable query.

Can anyone tell me how to update my code to make it Windows 7 compatible?
Imports Excel = Microsoft.Office.Interop.Excel


Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button1.Click
        Dim x As Integer = 0
        If Not (txtFirstName.Text = "" Or txtLastName.Text = "" Or txtCompany.Text = "" Or txtEmail.Text = "") Then

            Dim FirstName, LastName, Email, Company, Title, Phone As String

            FirstName = txtFirstName.Text
            LastName = txtLastName.Text
            Email = txtEmail.Text
            Company = txtCompany.Text
            Title = txtTitle.Text
            Phone = txtPhone.Text

            Try
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim MyCommand As New System.Data.OleDb.OleDbCommand
                MyConnection = New System.Data.OleDb.OleDbConnection _
                ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;""")

                MyConnection.Open()
                MyCommand.Connection = MyConnection
                MyCommand.CommandText = "INSERT INTO [Sheet1$] ([First Name],[Last Name],[Email],[Company],[Title],[Phone]) values ('" & FirstName & "','" & LastName & "','" & Email & "','" & Company & "','" & Title & "','" & Phone & "')"
                MyCommand.ExecuteNonQuery()
                MyConnection.Close()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            txtFirstName.Text = ""
            txtLastName.Text = ""
            txtEmail.Text = ""
            txtCompany.Text = ""
            txtTitle.Text = ""
            txtPhone.Text = ""

        Else
            x = MsgBox("Please fill out all required fields.", vbOKOnly, "Required fields empty")
        End If

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

   
End Class

Open in new window

0
Comment
Question by:LCMSdev
3 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35694441
Your Excel file is in C root. Windows 7 does not allow programs to access C root, windows folder, program files folder and many other places. Try moving it to another drive or within a folder such as desktop.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35694803
Follow the CodeCruiser instructions, or run your program with administrative privileges to make it work (I prefer the CodeCruiser advice).
0
 

Author Closing Comment

by:LCMSdev
ID: 35699120
Yup, that did it. Thanks!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question