-
Jul 6th, 2016, 03:57 AM
#1
Thread Starter
Member
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 ...
-
Jul 6th, 2016, 05:20 AM
#2
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!
-
Jul 6th, 2016, 05:28 AM
#3
Thread Starter
Member
Re: Writing to Excel using ADO OLE DB in vb.NET
Originally Posted by TysonLPrice
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.
-
Jul 6th, 2016, 05:33 AM
#4
Thread Starter
Member
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.
-
Jul 6th, 2016, 09:19 AM
#5
Re: Writing to Excel using ADO OLE DB in vb.NET
Welcome to VBForums
Originally Posted by Montgomery
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)
-
Jul 6th, 2016, 11:35 AM
#6
Thread Starter
Member
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.
-
Jul 6th, 2016, 02:05 PM
#7
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.
-
Jul 6th, 2016, 06:32 PM
#8
Thread Starter
Member
Re: Writing to Excel using ADO OLE DB in vb.NET
I have VS 2010 ... how do I get access to openxml ?
-
Jul 6th, 2016, 08:25 PM
#9
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.
-
Jul 7th, 2016, 07:39 AM
#10
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.
-
Jul 7th, 2016, 08:00 PM
#11
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|