Results 1 to 11 of 11

Thread: Writing to Excel using ADO OLE DB in vb.NET

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    Perth Australia
    Posts
    55

    Writing to Excel using ADO OLE DB in vb.NET

    I have found that manipulating Excel files using Excel Automation in vb.NET can be slow at times, particularly if you read/write data cell by cell. So I am now on an upward path trying to learn to use OLE DB and connection strings to write data to an Excel file. I have hit a bit of brick wall at present .... I have some code that works when you write known data values to a row in Excel. I am trying to write some data captured in textboxes and then passing this data to the Excel file via ADO OLE DB ... here is what I have :

    Private Sub cbWriteToCodesFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbWriteToCodesFile.Click

    'writes to the CodesList.xlsx file using OLEDB

    Dim FolderNamePath As String
    Dim CdStr As String
    Dim YrStr As String
    Dim NameStr As String
    Dim HseStr As String
    Dim PlStr As String
    Dim PtsStr As String

    FolderNamePath = My.Settings.HSICCFolder & "CodesList.xlsx"

    'read the text box values to write these into the Excel file
    CdStr = tbCode.Text
    YrStr = tbYr.Text
    NameStr = tbStudentName.Text
    HseStr = tbHse.Text
    PlStr = tbPlace.Text
    PtsStr = tbPts.Text

    Try
    Dim MyOLEDB As System.Data.OleDb.OleDbConnection
    Dim myCommand As New System.Data.OleDb.OleDbCommand
    Dim sql As String

    MyOLEDB = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FolderNamePath & ";Extended Properties='Excel 12.0;HDR=NO;';")
    MyOLEDB.Open()
    myCommand.Connection = MyOLEDB

    sql = "INSERT INTO [Codes$] (F1,F2,F3,F4,F5,F6) values ('9714','7','BNT','Sam JONES','12','209')" '<<<----- this WORKS !
    myCommand.CommandText = sql
    myCommand.ExecuteNonQuery()

    sql = "INSERT INTO [Codes$] (F1,F2,F3,F4,F5,F6) values (CdStr,YrStr,HseStr,NameStr,PlStr,PtsStr)" '<<<<-----this DOES NOT work !!!!
    myCommand.CommandText = sql
    myCommand.ExecuteNonQuery()

    MyOLEDB.Close()

    LbWriteMessage.Text = "The code " & " was added to the CodesList.xlsx file on " & DateTime.Now.ToLongDateString & " at " & TimeOfDay
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try

    End Sub

    Any ideas how I can achieve this ...

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Writing to Excel using ADO OLE DB in vb.NET

    What does "this DOES NOT work !!!!" mean? Is there a particular error message? Did you hover over the variables to look at the contents? Sometimes we assume a value when it may not be what we think.
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    Perth Australia
    Posts
    55

    Re: Writing to Excel using ADO OLE DB in vb.NET

    Quote Originally Posted by TysonLPrice View Post
    What does "this DOES NOT work !!!!" mean? Is there a particular error message? Did you hover over the variables to look at the contents? Sometimes we assume a value when it may not be what we think.
    Here is the error message : No value given for one or more required parameters. The line I indicated where it works ... this is written into the Excel file successfully, but the error is generated at the line I indicated.

    The syntax seemingly does not like the variables for the strings that are captured in the text boxes.

  4. #4

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    Perth Australia
    Posts
    55

    Re: Writing to Excel using ADO OLE DB in vb.NET

    The error message (generated by the line I indicated) is : "No value given for one or more required parameters".
    The first line DOES work since it puts the data in a row of the said Excel file.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Writing to Excel using ADO OLE DB in vb.NET

    Welcome to VBForums

    Quote Originally Posted by Montgomery View Post
    sql = "INSERT INTO [Codes$] (F1,F2,F3,F4,F5,F6) values ('9714','7','BNT','Sam JONES','12','209')" '<<<----- this WORKS !
    ...
    sql = "INSERT INTO [Codes$] (F1,F2,F3,F4,F5,F6) values (CdStr,YrStr,HseStr,NameStr,PlStr,PtsStr)" '<<<<-----this DOES NOT work !!!!
    The first one works because you are passing values to Excel (via ADO).

    The second one doesn't work because you are passing the names of variables that only exist inside your code, so Excel and ADO have no idea what you mean.


    The safe way to pass values from variables etc is to use Parameters, and assuming the data type of your variables etc is the same as the field you are putting the values into (which seems to be the case here), you can use .AddWithValue, eg:
    Code:
    sql = "INSERT INTO [Codes$] (F1,F2,F3,F4,F5,F6) values (@CdStr,@YrStr,@HseStr,@NameStr,@PlStr,@PtsStr)"
    myCommand.Parameters.AddWithValue("@CdStr", CdStr)
    myCommand.Parameters.AddWithValue("@YrStr", YrStr)
    myCommand.Parameters.AddWithValue("@HseStr", HseStr)
    myCommand.Parameters.AddWithValue("@NameStr", NameStr)
    myCommand.Parameters.AddWithValue("@PlStr", PlStr)
    myCommand.Parameters.AddWithValue("@PtsStr", PtsStr)
    myCommand.CommandText = sql
    myCommand.ExecuteNonQuery()
    (note that as you are using ACE, the order you add the parameters should be the same order as they appear in the SQL statement)

  6. #6

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    Perth Australia
    Posts
    55

    Re: Writing to Excel using ADO OLE DB in vb.NET

    Thanks ... I will try this and get back to you. Time for me to sleep now (12:35 am). It looks like it will work though.

  7. #7
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Re: Writing to Excel using ADO OLE DB in vb.NET

    If you want to write faster to an excel file try a third party library based on openxml. I had some extreme write operations that were cut down exponentially.

  8. #8

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    Perth Australia
    Posts
    55

    Re: Writing to Excel using ADO OLE DB in vb.NET

    I have VS 2010 ... how do I get access to openxml ?

  9. #9
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Re: Writing to Excel using ADO OLE DB in vb.NET

    The easiest way is to use an existing library. I use EP Plus (http://epplus.codeplex.com/). Do some timings and see if it helps you at all. The best part is, you only need to do minor modification to make it work with existing excel objects code.

  10. #10
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Writing to Excel using ADO OLE DB in vb.NET

    If you want some working samples to try out, see this MSDN code sample. Project 6 uses standard connection and command operations while project 8 uses DataAdapter methods. I did this in VS2013, if you download the solution it should load in VS2010 and if not simply go to "Browse code" tab and look at the code. Unless you are performing many inserts at once there is no need to use Open Office xml sdk.

  11. #11

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    Perth Australia
    Posts
    55

    Re: Writing to Excel using ADO OLE DB in vb.NET

    Many thanks for this advice .... it is greatly appreciated that so many here provide advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width