Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    26

    Unanswered: Microsoft OLE DB Provider for ODBC Drivers error '80040e4e' Operation Cancelled ???

    hi, i'm trying to import some excel data from an external file into SQL server 2000 database.

    it gives me this error when i submit the file:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e4e'

    Operation was canceled.

    /dl/DLbackup/admin/npanxxBulkUpdate.asp, line 76


    This is the section of the code:

    line 76 is:
    cnnExcel.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DriverId=790;" & _
    "Dbq=" & fileName


    Code:
    Set cnnExcel = Server.CreateObject("ADODB.Connection")
    cnnExcel.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
               "DriverId=790;" & _
               "Dbq=" & fileName
    
    
    	Set rsXL = Server.CreateObject("ADODB.Recordset")
    	rsXL.Open "SELECT npa_nxx,ocn,exchange_abbr FROM [Sheet1$]", cnnExcel
    
    
    
    	Do While not rsXL.EOF
         	conn.Execute "INSERT INTO npa_nxx VALUES('" & rsXL("npa_nxx") & "', '" & rsXL("ocn") & "', '" & rsXL("exchange_abbr") & "' )"
         	rsXl.MoveNext
    	Loop
    how do i fix this??
    Last edited by ct976; 10-21-04 at 14:23.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I don't know much about connecting to Excel but I thought the syntax for the connection string was more like...
    Code:
    oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
               "DriverId=790;" & _
               "Dbq=c:\somepath\mySpreadsheet.xls;" & _
               "DefaultDir=c:\somepath"
    eg. you need the defaultdir (for the locking file I think). you might also want to check out how your filename is being structure/passed. You might need to do a MapPath on the filename to get yourself into the right directory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •