akohl — Sat, 20 Apr 2002 22:14:24 +0000

I'm still working on this one.

Now I've got some more information that might help someone come up with a suggestion.

Here's the background:

I am trying to write a script that will enter data from a tab delimated text file into an access file.
That part went ok once I made data entry conditioned on the line in the text file haveing the correct number of tab delimated fields.
Interestingly, the text data file I was given had two lines, out of over a thousand, which did not conform to the format of 23 fields.

What I'm trying to do now is to enable integration of future update data files of the same format. It will have to check each line to see that the title of the item has not been entered into the database previously before entering the data of that line.
The problem is using the movenext method of the recordset object inside the loop that reads the lines from the text file.

There seems to be some kind of conflict between the movenext method of the recordset object and the readline method of the textStream object.
Should there be such a conflict? And if so can someone explain why? And what might I try to get around this problem? Should I throw the data into two dimesional array, close the recordset, and then check if there is data match against the array instead of the recordset?

Here's all the commented code along with the error message.

Technical Information (for support personnel)

Error Type:
(0x80020009)
Exception occurred.
/akohlcom/m-pomeranz/asp/manager/rstest.asp, line 65

<% @ language= vbscript %>
<% option explicit
%>
dim con1
sub connection()
	'**connection and recordset objects for local site***
	 set con1=server.CreateObject("adodb.connection")
	Con1.open "provider=microsoft.jet.oledb.4.0;data source=c:\db\m_pomeranz01c.mdb"


end sub
<!--#include virtual="/includes/adovbs.inc"-->
<html>
<head>
<title>
record set test page
</title>
</head>
<body>
hi
<%
function removeQuotes(str)
	str=mid(str,2,len(str)-2)
	removeQuotes=str
end function
			call connection()
			dim rs1, sqlStr
			set rs1 = server.CreateObject ("adodb.recordset")'books
			sqlStr="SELECT books.book_id, books.title, books.subtitle, books.short_description, books.long_description,books.publisher_id"
			sqlStr=sqlStr&" FROM books;"
			rs1.open sqlStr,con1,3,3
			const forReading=1
			const forWriting=2
			const forAppending=8
			const tristateUseDefault=-2
			const tristateTrue=-1
			const TristateFalse=0
			dim objFs, objTextS, page, file
			''this script enters the pk of a particular publisher
			''on every line. the number 1 sets it to artscroll.
			dim pubId
			pubId=1
			page="items.txt"
			file="c:\webtexts\"&page
			set objFs=server.CreateObject("scripting.filesystemObject")
			if objFs.fileExists(file)=true then
					set ObjTextS=objFs.openTextFile(file, forReading,tristateFalse)
					dim strArray, strLine, out ,tempNewTitle, tempOldTitle
					dim i
					i=0
					objTextS.skipline
					do while objTextS.atEndOfStream<>true
							strLine= objTextS.readLine
							strArray=split(strLine,vbTab)
							if ubound(strArray)<>23 then
								out=out&"<h1>the following data was not entered</h1>"
								out=out&"<p style='color:green;font-family:zachary;'>It had an abberant number of tab delimated fields</p>"
								for i=0 to ubound(strArray)
										out=out&strArray(i)&"<br>"
								next'i
								out=out&"<p> the line number is" &	objTextS.line&"</p>"
								out=out&"<p> the third substring, index is 2, is "&strArray(2)&"</p>"
								out=out&"<p style='color:green;font-family:zachary;'> the last index of the array is "&ubound(strArray)&" and that's why it wasn't entered.</p>"
							else'if the line does have expected number of tab delimated fields 
								tempNewTitle=removeQuotes(strArray(1))
									if tempNewTitle<>tempOldTitle then
										response.write("here is new title from text file "&tempNewTitle&"<br>")
										tempOldTitle=tempNewTitle
'******when placed here there is and error from the rs1.movenext line****************
										response.write("<p>title field from db before text obj is closed inside the text obj loop"&rs1.fields("title")&"</p>")									
										rs1.movenext 
										response.write("<p>title field from db before text obj is closed inside the text obj loop"&rs1.fields("title")&"</p>")												
'**************************************************************************

								end if'the current line's title was or was not read.
							end if 'there is or is not expected number of fields in text data file
					loop'till end of file
					response.write out		
'******works here****************
'			response.write("<p>title field from db"&rs1.fields("title")&"</p>")									
'			rs1.movenext 
'			response.write("<p>title field from db"&rs1.fields("title")&"</p>")									
'**************************			
			
			end if' the file exists
			set objTextS=nothing
			set objFs=nothing
			response.write("<p>filesystem object is closed</p>")
'******works here****************
'			response.write("<p>title field from db"&rs1.fields("title")&"</p>")									
'			rs1.movenext 
'			response.write("<p>title field from db"&rs1.fields("title")&"</p>")									
'**************************			
					rs1.Close
					set rs1=nothing
			%>
			</body>
			</html>
			<%
			con1.close 
			set con1=nothing
%>

Peter J. Boettcher — Wed, 17 Apr 2002 02:26:38 +0000

That looks ok. Did you experiment with different locktypes/cursors? Maybe try putting your whole find part in a variable, eg:

strFind = "title='" & tempNewTitle & "' "
rs1.find strFind

Not sure what else to try.

akohl — Tue, 16 Apr 2002 19:21:51 +0000

And here are some more details that might be relevant

 set con1=server.CreateObject("adodb.connection")
Con1.open "provider=microsoft.jet.oledb.4.0;data source=c:\db\1c.mdb"



set rs1 = server.CreateObject ("adodb.recordset")'books
set rs2 = server.CreateObject ("adodb.recordset")'issue details
set rs3 = server.CreateObject ("adodb.recordset")'authors
set rs4 = server.CreateObject ("adodb.recordset")'authors/books

sqlStr="SELECT books.book_id, books.title, books.subtitle, books.short_description, books.long_description,"
sqlStr=sqlStr&"  books.publisher_id"
sqlStr=sqlStr&" FROM books;"
rs1.open sqlStr,con1,3,3