ASP Forum
Move to Next Record in db
jstarkweathe | Posted 10:09am 19. June 2001 Server Time |

I think I have coded myself into a corner.  :)

I have a page that displays info from an Access db and an image based on string variables. For instance:

http://www.harbuckcars.com/page.asp?p=folder&c=49

"49" is the Car_ID field (and primary key). What I want to implement is a function on the page that will allow the user to move to the next Car_ID in the database. And of course know where the RS starts and ends. Moving back one would be nice to.

I am kind of new at this db stuff. If anyone has any ideas I would appreciate it. I can include more of the current page code if helpful.

Thanks,
Jim
Bcooper | Posted 11:57am 19. June 2001 Server Time |

Here ya go.

-------------------
RS.MovePrevious
IF NOT RS.BOF THEN
   Response.Write "<A HREF='page.asp?p=" & folder & "&c=" & RS("Car_ID") & "'>Previous Image</A>"
END IF
RS.MoveNext
... 'other stuff on the page

RS.MoveNext
IF NOT RS.EOF THEN
   Response.Write "<A HREF='page.asp?p=" & folder & "&c=" & RS("Car_ID") & "'>Next Image</A>"
END IF
RS.MovePrevious
-------------------

Be sure you are using a scrollable cursor so the RS.MovePrevious statement will work.

Bob Cooper
IT Support Technician
jstarkweathe | Posted 2:32pm 19. June 2001 Server Time |

Bob,
Thanks for the help. I'm afraid I am rather clueless on scrollable cursors though. I did look it up, etc. Is the support for it server based? If you can give me any more info thanks.

Jim
jstarkweathe | Posted 4:44pm 19. June 2001 Server Time |

test
jstarkweathe | Posted 4:51pm 19. June 2001 Server Time |

Here is the folder.asp page (HTML hacked out) so you can see what's going on with the conn stuff. From what I can tell from poking around implementing scrollable cursor will require the correct config of the conn. Right?

<% Response.Buffer = True
'grab the ID field from the querystring (..folder.asp?p=fords&c=<%=rs("Car_ID")
Dim id
id = Request.QueryString("C")

Set MyConn = Server.CreateObject("ADODB.Connection")
Set rsCARS = Server.CreateObject("ADODB.RecordSet")

MyConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db/cars.mdb")

SQLString = "SELECT * FROM cars_data WHERE Car_ID = '"& id &"'"
rsCARS.Open "cars_data", MyConn, 0, 2, 2
rsCARS.Filter = "Car_ID = " & id
rsCARS.Update
%>

<%= carinfo%>Read more about this car.
<%= carpictures%>View more pictures on this car.
<!--Location of Back and Forward Element-->
  
  <!--Car Spec Info-->

<span class="bodymed">
<%
Response.Write "Make:<b> " & rsCARS("Make") %></b><br><%
Response.Write "Year:<b> " & rsCARS("Model_Year") %></b><br><%
Response.Write "Model:<b> " & rsCARS("Model") %></b><br><%
Response.Write "Body Type: " & rsCARS("Body_Type") %><br><%
Response.Write "Engine: " & rsCARS("Engine") %><br><%
Response.Write "VIN: " & rsCARS("VIN") %><br><br><%
Response.Write rsCARS("Short_Desc") %><br></span><%

rsCARS.Close
Set rsCARS = Nothing
MyConn.Close
Set MyConn = Nothing
%> ...end of page stuff
Bcooper | Posted 7:31am 20. June 2001 Server Time |

rsCARS.Open "cars_data", MyConn, 0, 2, 2

Right here.  This is the critical line:

open the recordset "rsCARS".  Open the table "cars_data".  Use the connection "MyConn".

The next two items are:
Cursor Type: 0 (adOpenForwardOnly) - Can only scroll forward
Lock Type: 2 (adLockPessimistic) - Can write to database

(don't remember what the last "2" means).

Change that line to:

rsCARS.Open "cars_data", MyConn, 3, 2, 2

This should allow you to do the suggested changes.

Bob Cooper
IT Support Technician
alan gregory | Posted 7:49am 22. June 2001 Server Time |

Just as a slight aside here if you include the adovbs.inc file in your asp file thus

<!--#include file="<path>\adovbs.inc"-->

Where path is the path to the location of this file, you can then use the constants associated with the cursor types and lock types to give you more readable code that helps in resolving issues with lock types and cursor types and the last parameter which is <%optional ado params%>, typically nothing for MS databases
Bcooper | Posted 8:10am 22. June 2001 Server Time |

A valid point...one which I was, alas, a bit too lazy to bring up in my reply.  In my code, I always make use of adovbs.inc for my database accesses, and it really is a good idea.  In fact, I would advise setting up a /Includes/ directory off the root.  Thus, all of your includes, such as adovbs.inc, can be accessed with:

<!--#INCLUDE VIRTUAL="/Includes/filename.inc"-->

(and no, you don't *have* to name include files .inc, it's just a convenient way to keep track of what they are and are for).

Bob Cooper
IT Support Technician
jstarkweathe | Posted 10:13pm 22. June 2001 Server Time |

Yes thanks I did realize I need the adovbs.inc file for that and had it included at top. The page is not giving me any errors with Bob's new config numbers but alas it is not response.writing anything to the page. The only thing I can think is that I am missing a piece of code here to indicate what record I am currently pulling data from (as that actually happens later down the page). I am going on vacation tomorrow so this will have to wait a week. If you have any good pointers let me know.

Thanks again for all the help. This is like my LAST big hurdle to overcome on this site.

Jim
WanderingKnights.com
jstarkweathe | Posted 10:28pm 22. June 2001 Server Time |

I created a couple of text version of page.asp and folder.asp. They are here:

http://www.harbuckcars.com/download/page.txt

http://www.harbuckcars.com/download/c.folder.inc.txt

Jim


Reply to Post Move to Next Record in db



Back to Forum Page