Solved

Record set Move Next fails for MS Access DB

Posted on 2010-09-09
16
469 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
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 47

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 74

Expert Comment

by:käµfm³d 👽
ID: 33640801
How does it fail? Are you receiving an error message?
0
 

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 74

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 74

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 47

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 47

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 47

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now