cursor problem

akohl posted this at 19:17 — 16th April 2002.

Joined: Feb 2001

Its really frustrating since this seems so simple

I'm just trying to find out if a particular value taken from another data file and stored in the variable
tempNewTitle exists in a recordset.

I tested this by placeing a few matching record in the db.

When I use:

	
rs1.find "title='the actual string that I know is there'"

the program flows into the else of if rs1.eof

But when I use the variable key for the same value in the find string I get the below error message:

if tempNewTitle<>tempOldTitle then
	response.write(tempNewTitle&"<br>")
	tempOldTitle=tempNewTitle
	rs1.movefirst
	rs1.find "title='"&tempNewTitle&"'"   'this line refernced by error message
	if rs1.eof then
		response.write("this is a new listing")
	else
		response.write("<p>this listing already exists</p>")
	end if' the title from text data file does or does not exits in db 
end if'the current line's title value was or was not read on the previous line.

Error Type:
ADODB.Recordset (0x800A0BB9)

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/akohlcom/m-pomeranz/asp/manager/datafill.asp, line 115

I find that if I remove the rs1.movefirst I don't get the error.
But I only get one matching record even though there are three.

Andy Kohlenberg
Jerusalem, Israel

akohl posted this at 19:21 — 16th April 2002.

They have: 117 posts

Joined: Feb 2001

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

Andy Kohlenberg
Jerusalem, Israel

Peter J. Boettcher posted this at 02:26 — 17th April 2002.

They have: 812 posts

Joined: Feb 2000

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.

PJ | Are we there yet?
pjboettcher.com

akohl posted this at 22:14 — 20th April 2002.

They have: 117 posts

Joined: Feb 2001

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

Andy Kohlenberg
Jerusalem, Israel