Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Record set Move Next fails for MS Access DB

Posted on 2010-09-09
16
Medium Priority
?
495 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 49

Expert Comment

by:Dale Fye
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 49

Expert Comment

by:Dale Fye
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 49

Accepted Solution

by:
Dale Fye earned 2000 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 49

Expert Comment

by:Dale Fye
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
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…
Suggested Courses

810 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