need help with excel oleDB INSERT INTO Command
hi, I am working on a project and what i am trying to accomplish is i want this particular code to save the values from mutiple text boxes into a excel file. When i run the code I get a exception error mesaage that states, "Syntax error in INSERT INTO statement".
Also I was wondering once i solve this problem, i have another problem i would like to solve that is, excel will hold a maximum of 256 sheets, and 65536 rows and i was wonder what i could do so taht when it reach the end of the sheet, I want to be able to create another sheet, until there are no more sheets, left then create new excel workbook, and start over from there with a new sheet.
Any help would be greatly appreciated
' get the currnet directory
Private getDir As String = Directory.GetCurrentDirectory
' Connection location
Private connDataExcel As String = "\database\resumes_2006_01_20_23-38.xls"
' excel database connection
Private m_sConnExcel As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & getDir & connDataExcel & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
' connection string
Friend WithEvents ExcelConn As New System.Data.OleDb.OleDbConnection(m_sConnExcel)
' data adapter
Friend WithEvents daExcel As New OleDbDataAdapter("Select * FROM [sheet1$]", m_sConnExcel)
Friend WithEvents dsExcel As DataSet = New DataSet()
' Private Functions
Private Sub selectAddExcel()
' open connection
' create the SQL Query to insert a row
' into the excel database
Dim daExcel As New System.Data.OleDb.OleDbCommand()
daExcel.Connection = ExcelConn
Dim checkBoxStat As String = String.Empty
If Me.chkCallBack.Checked = True Then checkBoxStat = "yes"
If Me.chkCallBack.Checked <> True Then checkBoxStat = "no"
daExcel.CommandText = ( _
"INSERT INTO [sheet1](prefix, firstName, " & _
"middleName, lastName, suffix, phone, " & _
"email, emailDate, callBack, date, " & _
"time) VALUES('" & Me.cmbPrefix.Text & "' , " & _
"'" & Me.txtFirstName.Text & "' , " & _
"'" & Me.txtMiddle.Text & "' , " & _
"'" & Me.txtLastName.Text & "' , " & _
"'" & Me.cmbSuffix.Text & "' , " & _
"'" & Me.txtPhone.Text & "' , " & _
"'" & Me.txtEmail.Text & "' , " & _
"'" & checkBoxStat & "' , " & _
"'" & Me.cmbDate.Text & "' , " & _
"'" & Me.txtTime.Text & "')")
' execute query
' close connection
Catch oleDBExceptionParameter As _
MessageBox.Show(oleDBExceptionParameter.Message, "OLEDB Error", _
Last edited by lew26; 01-25-2006 at 05:47 PM.
First try enclosing reserved words, such as Date and Time, within brackets. You can also change the column names so you don't have to worry about this. Then try running your code again.
List of Microsoft Jet 4.0 reserved words
List of reserved words in Access 2002 and Access 2003
Microsoft MVP (Visual Basic)
By lew26 in forum Database
Last Post: 01-23-2006, 03:27 PM
By bigbastard4 in forum Database
Last Post: 05-16-2001, 06:24 PM
By Bob Hines in forum Database
Last Post: 04-27-2000, 11:14 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL