Record set Move Next fails for MS Access DB

Experts,

I am calling a javascript function from a click event of a button (HTML). I can get the record count (which happens to be 9). The database used is MS Access. But itfails every time it gets to the "MoveNext" of the recordset. Below is the code:


Thanks for all the help!
function FetchNextData() {
      var cn;
      var rs;
      var sql;
      var conn;
      var sql; 
      var i;
	  

	  
	  i=0;
      rs = new ActiveXObject("ADODB.Recordset");
      conn = new ActiveXObject("ADODB.Connection");
      val ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\QTPDb.accdb;Persist Security Info=False"; 	
      conn.open (val);	 
	  rs.CursorType = 3;

	  sql ="select CauseOfLoss1 from IMContEquip1";	  
	  rs.open (sql,conn);
	 //rs.open (sql,conn,1,1);
	 //alert ("Record Count  " + rs.recordcount);
	  while (!rs.EOF)
	  { 	
		//document.forms['frm'].elements[4].value = rs(i);
		alert(rs(i));
		rs.movenext();      
		i=i+1;
	  }	
	  rs.close();	 
	  conn.close (); 

}

Open in new window

DoofuSAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Dale FyeConnect With a Mentor Commented:
I'm not entirely sure what this line is doing:

//document.forms['frm'].elements[4].value = rs(i);

Are you trying to fill some form of array, list, or combo box?

The way to refer to records 0 through 8 (9 records total) is to iterate through them in a loop, which you have

        while (!rs.EOF)
        {       
            //document.forms['frm'].elements[4].value = rs(i);
            alert(rs(i));
            rs.movenext();      
            i=i+1;
        }      

I assume the brackets {} define the loop (in Access this would be While NOT rs.Eof and would have to end with a WEND statement.  You should be able to change this to:

        while (!rs.EOF)
        {       
            //document.forms['frm'].elements[4].value = rs(0);
            alert(rs(0));
            rs.movenext;
        }      

The MoveNext method will get you to the next record in the recordset.  If you change all of your references from rs(i) to rs(0), and run your code, what happens?

0
 
Dale FyeCommented:
I don't know anything about JavaScript, but have your tried:

    rs.MoveNext

Also, in most applications, rs(i) would refer to the i-th field of the record.  Since you only have one field in your recordset, I think that should be rs(0).  Again, I know nothing about JavaScript.
0
 
David Christal CISSPCommented:
does rs.movefirst(); work?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
käµfm³d 👽Commented:
How does it fail? Are you receiving an error message?
0
 
DoofuSAuthor Commented:
fyed: I did try rs.movenext. No luck!

The below is hte error message I get:

"item cannot be found in the collection corresponding to the requested or ordinal."


Please mind that the alert prints the count (which is 9) correctly.
0
 
David Christal CISSPCommented:
If you are positioned at the last record, rs.EOF is false but rs.movenext will fail.  I'm not sure, but as I recall, doing a count will position you at the last record, therefore, doing an rs.movefirst  before attempting to enter the loop should remedy the problem.
0
 
käµfm³d 👽Commented:
>>  If you are positioned at the last record, rs.EOF is false but rs.movenext will fail.

I'm pretty sure when you're at the last record, EOF is true, not false.

>>  "item cannot be found in the collection corresponding to the requested or ordinal."

This sounds like you are trying to index the collection outside of its bounds. Why are you incrementing i each iteration of the loop? What you are in effect doing is:
col1  col2  col3  col4  col5  col6

i=0
      i=1
            i=2
                  i=3
                        i=4
                              i=5

etc.

Open in new window

0
 
käµfm³d 👽Commented:
In case my previous comment is unclear, you are accessing a different column each time you move to a new record. It sounds as if you are, using my previous example, trying to index col7, but col7 does not exist (i.e. you only selected 6 columns).
0
 
David Christal CISSPCommented:
>>I'm pretty sure when you're at the last record, EOF is true, not false.

According to Microsoft:

The EOF property returns True if the current record position is after the last record and False if the current record position is on or before the last record.

http://msdn.microsoft.com/en-us/library/ms675787(VS.85).aspx

I, too, am wondering about the column references.  
Might do well to leave "alert(rs(i));"  out.
0
 
Dale FyeCommented:
As I mentioned in my first post, and kaufmen and dchristal have also mentioned.

Look at the syntax of your recordset references.  Your SQL statement only has one field, but rs(i) will attempt to point to the field0, field1, field2, ..., field8.  You need to refer to rs(0).
0
 
DoofuSAuthor Commented:
fyed,

how else would I get the rows of that record set. When I run the query in MS Access, I am getting the result fine.

Here is how the result looks like

Fields
------
x1
x2
x3
x4
x5
x6

How would I access the items of the record set in Javascript?
0
 
DoofuSAuthor Commented:
Kaufmed,

I know there is a problem in the script, because I did test this:

alert(rs(1));

and that still spits out an error. Anything other than alert(rs(0)) is erroring out.


0
 
DoofuSAuthor Commented:
okay...this totally did the trick. Instead of doing rs(i), I did rs(0) and was able to access the recordset items. Thanks Fyed!!
0
 
Dale FyeCommented:
Glad I could help.

never did get an answer on what this line is doing.

            //document.forms['frm'].elements[4].value = rs(0);
0
 
DoofuSAuthor Commented:
oh ...that! The idea is to fetch data from the table and populate the text box (which happens to be element #4 on the web page). If I could help you picture this.... a button called "next" will let the user traverse thru all of the values for a particular field in the database.
0
 
DoofuSAuthor Commented:
right now...that line is not working, meaning, I can't navigate thru the values by clicking on the button. Maybe that is an upcoming question :) ... keep an eye out for it ;)
0
All Courses

From novice to tech pro — start learning today.