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

ct976 · #1 (**permalink**) 10-21-04, 12:16

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??

rokslide · #2 (**permalink**) 10-21-04, 19:09

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech