need help with excel oleDB INSERT INTO Command

[lew26]

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".


P.S.
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








'===============================================
' Declaration
'===============================================

' 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)

' dataset
Friend WithEvents dsExcel As DataSet = New DataSet()

'==================================================
' Private Functions
'==================================================


Private Sub selectAddExcel()
Try
' open connection
ExcelConn.Open()



' 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
daExcel.ExecuteNonQuery()



' close connection
ExcelConn.Close()



Catch oleDBExceptionParameter As _
System.Data.OleDb.OleDbException

MessageBox.Show(oleDBExceptionParameter.Message, "OLEDB Error", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Try

End Sub

[pclement]

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