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

Threaded View

  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 05:47 PM.

Similar Threads

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

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