Microsoft OLE DB Provider for ODBC Drivers error '80040e21'. Weird! 2

Sensibilium · Feb 25, 2002

I'm having a problem with a page I have that updates/deletes records to any number of tables (using the same code). The problem occurs during updating the third record of my products table:

[tt]Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. [/tt]

Now it doesn't seem to be the connection, as the categories table is correctly updated just before the products table, and it can't be the recordset as the it's the same code as used for the prior table.

I have been to M$ and read the applicable documents, and checked the registery entry for the ODBC OLE DB, and everything is in place as it should be (on client & server). This leads me to beleive that the problem lies with the data, but I cannot see anything wrong with it (and it has been imported before (successfully)).

I would post the code, but it's big. Any idea's as what could be causing this?

Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

cmmrfrds · Feb 26, 2002

You don't mention what database you are using, but maybe there are constraints on the product table. For instance, to satisfy referential integrity the product id may be a foreign key in the order table. The database may be set up to prevent this in a delete operation.

Sensibilium · Feb 27, 2002

There are no integrity issues, as each table is dealt with in order of integrity rules. tblProductTypes, then tblCategories, and finally tblProducts.

Okay, I'll post the code to help out a bit.

Also, the database I'm using is an Access 2000 db, using an ADO connection.

CODE:

Code:

<!DOCTYPE html PUBLIC &quot;-//W3C//DTD HTML 4.0 Transitional//EN&quot;
&quot;[URL unfurl="true"]http://www.w3.org/TR/REC-html40/loose.dtd&quot;>[/URL]
<!-- METADATA TYPE=&quot;typelib&quot; FILE=&quot;C:\Program Files\Common Files\System\ado\msado15.dll&quot; -->
<%
'Set text file name for text streaming object
Const sFile = &quot;WebUpdates.txt&quot;

'Set for total number of tables in website db
Const iNoOfTables = 3

'Change MaxFields to reflect changes in fields imported (+1)
'IE: current number of fields (Nov 2001): tblProdTypes (5), tblCategories(9), tblProducts (15)
Const MaxFields = 15
%>
<!--#include file=&quot;getdatafile.asp&quot; -->
<HTML>
<HEAD>
<TITLE>Importing Website Data...</TITLE>
</HEAD>

<BODY>

	<!--#include file=&quot;openconn.asp&quot; -->

	<H1 class=&quot;center&quot;><BR />Importing Website Data...</H1>
	<%

	'Data table import order - tblProdTypes, tblCategories, tblProducts
	Call ImportData()

	'Close and reset database connection object
	oConn.Close
	Set oConn = Nothing
	%>
	<H2 class=&quot;center&quot;>IMPORT COMPLETE!<BR /><BR /></H2>

<%
Public Sub ImportData()
Const sDelimeter = &quot;,&quot;
Const StartReplace = 1
Const ReplaceCount = 1

Dim oRS, sRecord, sTable
Dim iTable, iArrayLen, iNoOfFields, iField
Dim sFindString, sTableFieldHeads
Dim bolModify

Dim sFieldName(), sFieldValue()
ReDim sFieldName(MaxFields), sFieldValue(MaxFields)

	Set oRS=Server.CreateObject(&quot;ADODB.Recordset&quot;)

	'Loop around for the total number of tables in website db
	For iTable = 1 to iNoOfTables

		Response.Write &quot;</TABLE><BR /><BR />&quot;
		If oTextStream.AtEndOfStream Then Exit For

		'If second time round main loop extract next table name and close old HTML table
		If sRecord & &quot;&quot; = &quot;&quot; Then 
			sTable = oTextStream.ReadLine()
		Else
			sTable = sRecord
		End If

		'Open recordset and populate with data
		oRS.Open sTable, oConn, adOpenStatic, adLockBatchOptimistic
		oRS.MoveLast
		oRS.MoveFirst

		'Read next line for table field name
		sRecord = oTextStream.ReadLine()
		'Last line in file is always empty
		If sRecord & &quot;&quot; = &quot;&quot; Then Exit For

		iArrayLen = MaxFields - 1
		iNoOfFields = MaxFields
		sTableFieldHeads = &quot;&quot;

		'Extract Field Names and output to HTML table-heads
		For iField = 0 To iArrayLen
			'Test for end of fields
			If Not sRecord & &quot;&quot; = &quot;&quot; Then
				sFieldName(iField) = ExtractFieldValue(sRecord, sDelimeter)
				sRecord = Replace(sRecord, sFieldName(iField) & sDelimeter, &quot;&quot;, StartReplace, ReplaceCount)
				sTableFieldHeads = sTableFieldHeads & &quot;<TH class=left>&quot; & sFieldName(iField) & &quot;</TH>&quot;
			Else
				'Total number of fields less than array length
				If iNoOfFields = MaxFields Then iNoOfFields = iField
			End If
		Next
		Response.Write &quot;<TABLE cols=&quot; & iNoOfFields+1 & &quot; cellspacing=0 cellpadding=3>&quot;
		Response.Write &quot;<TR><TH class=center colspan=&quot; & iNoOfFields+1 & &quot;>Modifying Table: &quot; & sTable & &quot;</TH></TR><TR>&quot;
		Response.Write sTableFieldHeads
		Response.Write &quot;<TH class=left>Action Result</TH>&quot;
		Response.Write &quot;</TR>&quot;


		'Loop through text file until end of file or new table name discovered
		Do Until oTextStream.AtEndOfStream

			'Get first line of table data
			sRecord = oTextStream.ReadLine()

			If Left(sRecord, 3) = &quot;tbl&quot; Then Exit Do

			Response.Write &quot;<TR>&quot;

			'Extract Field Values
			For iField = 0 To iNoOfFields - 1  'This stops the LEFT command falling over at end of record (in ExtractFieldValue Function)
				sFieldValue(iField) = ExtractFieldValue(sRecord, sDelimeter)
				sRecord = Replace(sRecord, sFieldValue(iField) & sDelimeter, &quot;&quot;, StartReplace, ReplaceCount)
				sFieldValue(iField) = Trim(sFieldValue(iField))
				Response.Write &quot;<TD class=left>&quot; & sFieldValue(iField) & &quot;</TD>&quot;
			Next

			'Build recordset find string
			If IsNumeric(sFieldValue(0)) Then
				sFindString = sFieldName(0) & &quot; = &quot; & sFieldValue(0)
			Else
				sFindString = sFieldName(0) & &quot; LIKE '&quot; & sFieldValue(0) & &quot;'&quot;
			End If
			Response.Write &quot;Find String: &quot; & sFindString & &quot;<BR />&quot;

			'Find record
			oRS.Find sFindString

			'Modify Table entries
			If oRS.Eof Then
				If sFieldValue(iNoOfFields - 1) = &quot;MOD&quot; Then
					'Add record
					bolModify = True
					oRS.AddNew
					oRS(sFieldName(0)) = sFieldValue(0)
					Response.Write &quot;<TD class=left>Added</TD>&quot;
				Else
					'Record doesn't exist - cannot delete
					bolModify = False
					Response.Write &quot;<TD class=left>No Record</TD>&quot;
				End If
			Else
				If sFieldValue(iNoOfFields - 1) = &quot;MOD&quot; Then
					'Modify record
					bolModify = True	
					Response.Write &quot;<TD class=left>Edited</TD>&quot;
				Else
					'Delete record
					bolModify = False
					oRS.Delete
					Response.Write &quot;<TD class=left>Deleted</TD>&quot;
				End If
			End If

			'Only run this if add or edit
			If bolModify Then
				'Set all other field values (add & edit)
				For iField = 1 To (iNoOfFields - 2)
		Response.Write sFieldName(iField) & &quot;: &quot; & sFieldValue(iField) & &quot;(&quot; & iField & &quot;)<BR />&quot;
					If sFieldValue(iField) = &quot;Null&quot; Then
						If IsNumeric(oRS(sFieldName(iField))) Then
							oRS(sFieldName(iField)) = Empty
						Else
							oRS(sFieldName(iField)) = &quot;&quot;
						End If
					Else
			Response.Write &quot;Modifying Field: &quot; & sFieldName(iField) & &quot; = &quot; & sFieldValue(iField) & &quot;<BR />&quot;
						oRS(sFieldName(iField)) = sFieldValue(iField)
					End If
				Next
				oRS.Update
			End If

			'Reset recordset pointer
Response.Write &quot;After Update<BR />&quot;
'			oRS.MoveLast
'			oRS.MoveFirst

			Response.Write &quot;</TR>&quot;

		Loop
		oRS.Close
	Next

	'Clear down recordset object
	Set oRS = Nothing

End Sub

Public Function ExtractFieldValue(sRecordData, sDelimeter)
Dim iDelimeterLoc

	iDelimeterLoc = CInt(Instr(sRecordData, sDelimeter))
'	Response.Write sRecordData & &quot;<BR />Delimeter: &quot; & sDelimeter & &quot; - Delimeter Location: &quot; & iDelimeterLoc
	ExtractFieldValue = Left(sRecordData, iDelimeterLoc - 1)
'	Response.Write ExtractFieldValue & &quot;<BR />&quot;

End Function
%>
</BODY>
</HTML>

Told you it was massive! Exapmle of CSV data file below:

[tt]tblCategories
DesignID,Design,ProdTypeID,Details,DishwasherSafe,MicrowaveSafe,OvenToTable,Discontinued,WebUploadAction,
11 ,Infinity, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,True,MOD,
13 ,Linton Gold, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,False,MOD,
17 ,Sapphire, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,True,MOD,
18 ,Sherwood, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,True,MOD,
27 ,Kensington Blue, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,True,MOD,
28 ,Kensington Green, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,True,MOD,
29 ,Kensington Red, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,True,MOD,
39 ,Silver Garland, 1 ,Fine china/porcelain. 20 piece sets contain: 4 Dinner Plates, 4 Tea Plates, 4 Soup/Cereal Bowls, 4 Tea Cups, and 4 Tea Saucers.,False,False,False,False,MOD,
40 ,Café, 2 ,Pure white, vitrified porcelain. Ideal for restaurants and café's. See <A href='prodlist.asp?id=65'>Arctic</A> for more items.,True,True,True,False,MOD,
tblProducts
ItemCode,DesignID,OurDescription,RetailPrice,RelItem1,RelItem2,RelItem3,RelItem4,RelItem5,RelItem6,RelItem7,RelItem8,RelItem9,RelItem10,WebUploadAction,
AC10ES, 100 ,Vase Mini - Blue & White - Trees (Singharaja), 20 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,
AC10L, 100 ,Vase Large - Blue & White - Trees (Singharaja), 40 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,
AC10M, 100 ,Vase Medium - Blue & White - Trees (Singharaja), 33 ,AC10ES,AC10S,AC10L,Null,Null,Null,Null,Null,Null,Null,MOD,
AC10S, 100 ,Vase Small - Blue & White - Trees (Singharaja), 27 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,
AC13ES, 100 ,Vase Mini - Light Green - Wheat (Hillside), 20 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,
AC13L, 100 ,Vase Large - Light Green - Wheat (Hillside), 40 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,
AC13M, 100 ,Vase Medium - Light Green - Wheat (Hillside), 33 ,AC13ES,AC13S,AC13L,Null,Null,Null,Null,Null,Null,Null,MOD,
AC13S, 100 ,Vase Small - Light Green - Wheat (Hillside), 27 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,
AC16A, 100 ,Kettle - Metallic Black - Model A (Dhanu), 30 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,
AC16B, 100 ,Kettle - Metallic Black - Model B (Dhanu), 30 ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,MOD,[/tt]

(tblProductTypes not shown - import.asp falls over at third product item from tblProducts) Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

Sensibilium · Feb 27, 2002

See below for full results after running 'import.asp' with above data (tblProductCategories updated correctly):

[tt]Find String: ItemCode LIKE 'AC10ES'
Edited DesignID: 100(1)
Modifying Field: DesignID = 100
OurDescription: Vase Mini - Blue & White - Trees (Singharaja)(2)
Modifying Field: OurDescription = Vase Mini - Blue & White - Trees (Singharaja)
RetailPrice: 20(3)
Modifying Field: RetailPrice = 20
RelItem1: Null(4)
RelItem2: Null(5)
RelItem3: Null(6)
RelItem4: Null(7)
RelItem5: Null(8)
RelItem6: Null(9)
RelItem7: Null(10)
RelItem8: Null(11)
RelItem9: Null(12)
RelItem10: Null(13)
After Update

AC10L 100 Vase Large - Blue & White - Trees (Singharaja) 40 Null Null Null Null Null Null Null Null Null Null MOD Find String: ItemCode LIKE 'AC10L'
Edited DesignID: 100(1)
Modifying Field: DesignID = 100
OurDescription: Vase Large - Blue & White - Trees (Singharaja)(2)
Modifying Field: OurDescription = Vase Large - Blue & White - Trees (Singharaja)
RetailPrice: 40(3)
Modifying Field: RetailPrice = 40
RelItem1: Null(4)
RelItem2: Null(5)
RelItem3: Null(6)
RelItem4: Null(7)
RelItem5: Null(8)
RelItem6: Null(9)
RelItem7: Null(10)
RelItem8: Null(11)
RelItem9: Null(12)
RelItem10: Null(13)
After Update

AC10M 100 Vase Medium - Blue & White - Trees (Singharaja) 33 AC10ES AC10S AC10L Null Null Null Null Null Null Null MOD Find String: ItemCode LIKE 'AC10M'
Edited DesignID: 100(1)
Modifying Field: DesignID = 100
OurDescription: Vase Medium - Blue & White - Trees (Singharaja)(2)
Modifying Field: OurDescription = Vase Medium - Blue & White - Trees (Singharaja)

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

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/admin/import.asp, line 181 [/tt]

Any idea's yet? This has been doing my head in for 3 days now (and the code used to work without a hitch - and I didn't change it either...) Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

Sensibilium · Feb 27, 2002

And here's my connection string (I'm sure this is not through ODBC even though the error is an OLE DB for ODBC error):

Code:

Dim oConn, strPhysicalPath, strConnect

Set oConn=Server.CreateObject(&quot;ADODB.connection&quot;)

strPhysicalPath = Request.ServerVariables(&quot;APPL_PHYSICAL_PATH&quot;)
strPhysicalPath = Replace(strPhysicalPath, &quot;\public&quot;, &quot;&quot;)

strConnect = &quot;Driver={Microsoft Access Driver (*.mdb)}; &quot;
strConnect = strConnect & &quot;DBQ=&quot; & strPhysicalPath
strConnect = strConnect & &quot;\database\fairprod.mdb; &quot;
strConnect = strConnect & &quot;SystemDB=&quot; & strPhysicalPath
strConnect = strConnect & &quot;\database\system.mdw; UID=AUser; Password=xxxxxxxxxxxxxxx&quot;

oConn.ConnectionString = strConnect
oConn.Open

Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

gsc1ugs · Feb 27, 2002

I got this just the other day when inputing wrong data types in text fields. Regards gsc1ugs
"Cant see wood for tree's...!"

Sensibilium · Feb 27, 2002

The last field it attempts to update is the OurDescription field, which is a text field for upto 50 characters. Unfortunately, there is no data-type conflict.

Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

cmmrfrds · Feb 28, 2002

Not to be picky, but it looks to me like you are going through the ODBC driver.

The OLEDB connection string should look something like.
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\bigtuna\Databases\Bank.mdb;" & _
"Persist Security Info=False"

If the provider is not a recognized OLEDB provider, it will be routed through the generic provider MSDASQL which in turn goes through ODBC. There are 2 layers, so in effect, ODBC is handing the error back to ADO. You could try a direct ADO connection bypassing ODBC and see if you get a better error message.

Then maybe you could iterate through the error collection.

After rs.open
if oconn.Errors.Count > 0 then
for each objError in oconn.Errors
response.write whatever
response.write etc
next
end if

Sensibilium · Mar 4, 2002

Thanks cmmrfrds. Unfortunately, I cannot find any information regarding the Workgroup Information File (system.mdw) and how to add it to the connections string for OLEDB.

Here's what I have so far (it's wrong):

Code:

strPhysicalPath = Replace(Request.ServerVariables(&quot;APPL_PHYSICAL_PATH&quot;), &quot;\public&quot;, &quot;&quot;)
strConnect = &quot;Provider=Microsoft.Jet.OLEDB.4.0; &quot; & _
             &quot;Data Source=&quot; & strPhysicalPath & &quot;\database\products.mdb; &quot; & _
             &quot;System Database=&quot; & strPhysicalPath & &quot;\database\System.mdw; &quot; & _
             &quot;User ID=User; Password=x; Persist Security Info=False&quot;

And I get the following error:
[tt]Microsoft JET Database Engine error '80040e4d'

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.[/tt]

The workgroup file exists and is not opened exclusively by another user, so the 'System Database' bit must be incorrect. But what is the correct 'command'?

Thanks again. =)
Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

cmmrfrds · Mar 4, 2002

Here is a reference to the mdw, but you shouldn't need it in the connection string.

Jet OLEDB:System Database=C:\program files\microsoft access\office\system.mdw

To check out the connection string defaults go through the udl wizard and you will be able to see the syntax. The easiest way to get the connection string correct is let the wizard build the string and then copy and paste. Heres how.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft access provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

Sensibilium · Mar 5, 2002

Excellent stuff cmmr! Where on earth did you find that out?! Amazingly handy the udl trick. Thanks. =)

Okay, so I've managed to get a working non-ODBC connection, and it appears that the real error behind this problem is data-length (gsc1ugs was close):

[tt]Microsoft JET Database Engine error '80040e21'

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.[/tt]

Although the description field in my local database is a max of 50 characters (as is the description field in the website db), the last bit of data is listed as having a length of 51 (even with Trim)!

Thankfully, I've sussed the problem. Any special characters, such as the ampersand, are translated into HTML code before adding them to the web database. I now know this makes little sense, thanks to this issue, I will have to translate to HTML code on the fly instead I suppose.

Thanks again for all your help cmmrfrds. =)

Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

cmmrfrds · Mar 5, 2002

Glad to be of help. What was the most helpful going through the ADO errors collection or using a direct ADO connection.

I found the information on udl, user data links, in a couple of books I purchased. It was described in beginning active server pages. WROX press. Also, in Serious ADO by Rob Macdonald. By the way, I recommend the book Serious ADO for ADO coding since it opens up the many features of ADO and is very readable with good examples.

Sensibilium · Mar 6, 2002

What was the most helpful? It was using the direct ADO connection. Thanks

Strange, I have Beginning Active Server Pages on Wrox Press, but I've never spotted it... I shall have to look again. Ahdkaw
"Get that ST away from me!!!"

http://www.sensibilium.com

Where the sensible gather... or so they say.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'. Weird! 2

Sensibilium

Programmer

cmmrfrds

Programmer

Sensibilium

Programmer

Sensibilium

Programmer

Sensibilium

Programmer

gsc1ugs

Programmer

Sensibilium

Programmer

cmmrfrds

Programmer

Sensibilium

Programmer

cmmrfrds

Programmer

Sensibilium

Programmer

cmmrfrds

Programmer

Sensibilium

Programmer

Similar threads

Part and Inventory Search

Sponsor