Solved

Record set Move Next fails for MS Access DB

Posted on 2010-09-09
16
477 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:DoofuS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 33640777
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
 
LVL 5

Expert Comment

by:David Christal CISSP
ID: 33640783
does rs.movefirst(); work?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33640801
How does it fail? Are you receiving an error message?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:DoofuS
ID: 33640850
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
 
LVL 5

Expert Comment

by:David Christal CISSP
ID: 33641039
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33641556
>>  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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33641571
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
 
LVL 5

Expert Comment

by:David Christal CISSP
ID: 33641827
>>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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 33642253
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
 

Author Comment

by:DoofuS
ID: 33642791
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
 

Author Comment

by:DoofuS
ID: 33642803
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
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 33642846
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
 

Author Comment

by:DoofuS
ID: 33643167
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 33643251
Glad I could help.

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

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

Author Comment

by:DoofuS
ID: 33644881
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
 

Author Comment

by:DoofuS
ID: 33644893
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question