need help with excel oleDB INSERT INTO Command


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: need help with excel oleDB INSERT INTO Command

  1. #1
    Join Date
    Jul 2005
    Posts
    24

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


    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
    Attached Files Attached Files
    Last edited by lew26; 01-25-2006 at 06:47 PM.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Replies: 8
    Last Post: 01-23-2006, 04:27 PM
  2. SQL Tutorial (Answer Q's & post your reply)
    By bigbastard4 in forum Database
    Replies: 2
    Last Post: 05-16-2001, 07:24 PM
  3. Multi-row calculations
    By Bob Hines in forum Database
    Replies: 7
    Last Post: 04-27-2000, 12:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center