|
 |
access_asp thread: One record at a time
Message #1 by "Lucas" <itadmin@p...> on Wed, 6 Mar 2002 16:34:59
|
|
Hi,
I have a form which searches a database through a query, then inserts the
data into input text areas. I have also 2 button for previous record and
next records they both work but only with the record immediately after the
first using the .movenext. It moves to the next record but when I click
again it does not do anything. Do I need an array of somesort to be able
to do this or??
waiting for help!!!!!!!!
Message #2 by "Ken Schaefer" <ken@a...> on Thu, 7 Mar 2002 16:50:05 +1100
|
|
You need to implement what is called "recordset paging"
There are a large number of tutorials available on the web about this.
Why it's not working in your case depends very much on the error that you
made in your code. Since you didn't provide any code, we can't really help
you very much...
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Lucas" <itadmin@p...>
Subject: [access_asp] One record at a time
: I have a form which searches a database through a query, then inserts the
: data into input text areas. I have also 2 button for previous record and
: next records they both work but only with the record immediately after the
: first using the .movenext. It moves to the next record but when I click
: again it does not do anything. Do I need an array of somesort to be able
: to do this or??
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by "Lucas" <itadmin@p...> on Thu, 7 Mar 2002 13:48:15
|
|
Thanks for the reply Ken here is my code:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<TITLE>OrderView</TITLE>
</HEAD>
<BODY background="../PATT%20Part%20Orders/pattbkglogo800x600.jpg">
<%
' diming of all variables to be used
dim avarfields
dim bookmark
dim varorder
varorder = request.form("ordernumber")
dim intrecords
dim introw
dim intcol
dim intlastrow
dim intlastcol
Dim count
'Database connection string
If IsObject(Session("pattinventory2k_conn")) Then
Set conn = Session("pattinventory2k_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "pattinventory2k","",""
Set Session("??pattinventory2k_conn") = conn
End If
%>
<%
' Get the data from the tables
sql = "SELECT DISTINCTROW Orders.OrderID, Orders.ShipDate, Orders.
[Promised-byDate], Orders.ShipName, Orders.ShipAddress1, Orders.ShipState,
Orders.ShipCountry, Orders.ShipCity, Orders.ShipZip, [Order
Details].QtyShip, [Order Details].ItemID, ItemMaster.ItemDesc FROM Orders
INNER JOIN (ItemMaster INNER JOIN [Order Details] ON ItemMaster.ItemID =
[Order Details].ItemID) ON Orders.OrderID = [Order Details].OrderID WHERE
((([Order Details].ItemID)=[ItemMaster].[ItemID])) ORDER BY
Orders.OrderID "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
%>
<%
Sub nextrecord()
if not rs.eof then
rs.movenext
end if
End sub
If Request.Form("next") <> "" then
if not rs.eof then
call nextrecord()
end if
End If
If Request.Form("previous") <> "" Then
if not count < 1 then
rs.moveprevious
End if
End if
%>
<h1><font color="#FFFF00">PATT Filtration Orders To Ship</font></h1>
<br>
<br>
<p> </p>
<form method="POST" action="Orders1.asp" webbot-action="--WEBBOT-
SELF--">
<p align="left"><font color="#FFFF00"><b>Order Identification
Number :
</b></font><input type="text" name="ordernumber" size="20"
value="<% = rs("OrderID")%>">
<p align="left"><font color="#FFFF00"><b>Date To be
Shipped : <input type="text" name="shipdate" size="20" value="<% = rs
("ShipDate")%>"></b></font></p>
<p align="left"><b><font color="#FFFF00" size="4">Shipping
Information</font></b></p>
<p align="left"><input type="text" name="shipname"
size="45" value="<% = rs("ShipName")%>"><br>
<input type="text" name="shipaddress" size="45" value="<% = rs
("ShipAddress1")%>"><br>
<input type="text" name="shipcity" size="20"value="<% = rs
("ShipCity")%>"><input type="text" name="shipstate" size="21" value="<% =
rs("ShipState")%>"><br>
<input type="text" name="shippostalcode" size="20" value="<% = rs
("ShipZip")%>"><input type="text" name="shipcountry" size="21" value="<% =
rs("ShipCountry")%>"></p>
<p align="left"><font color="#FFFF00" size="4"><b>Items To Be
Shipped:</b></font></p>
<p align="left"><font color="#FFFF00" size="4"><b> Item
Identification &
nbsp;
Item
Description &nbs
p;
Quantity to Ship</b></font></p>
<p align="left"><input type="text" name="itemid" size="29"
value="<% = rs("ItemID")%>">
<input type="text" name="itemdesc" size="32" value="<% = rs
("ItemDesc")%
>">
<input type="text" name="qtyship" size="20" value="<% = rs
("QtyShip")%>"></p>
<p> </p>
<p> </p>
<p><input type="submit" value="Previous Order"
name="previous" ><input type="submit" value="Next Order" name="next"></p>
</form>
<p> </p>
<p> </p>
<p> </p>
<p><a href="http://10.0.0.8/PATT%20Orders/"><font
size="2"><img border="0" src="../PATT%20Part%
20Orders/back3.gif"></font></a></p>
</BODY>
</HTML>
---------------------------------------------------------------------------
> You need to implement what is called "recordset paging"
> There are a large number of tutorials available on the web about this.
>
> Why it's not working in your case depends very much on the error that you
> made in your code. Since you didn't provide any code, we can't really
help
> you very much...
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Lucas" <itadmin@p...>
> Subject: [access_asp] One record at a time
>
>
> : I have a form which searches a database through a query, then inserts
the
> : data into input text areas. I have also 2 button for previous record
and
> : next records they both work but only with the record immediately after
the
> : first using the .movenext. It moves to the next record but when I click
> : again it does not do anything. Do I need an array of somesort to be
able
> : to do this or??
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
Message #4 by "Ken Schaefer" <ken@a...> on Fri, 8 Mar 2002 12:29:20 +1100
|
|
The problem is this:
When someone presses the next button, you call the routine: nextrecord()
This routine does the following:
<%
If not objRS.EOF then
objRS.movenext
End If
%>
The problem is that when you open a recordset, you are always at the first
record. Then, your routine is called, and the recordset is moved to the 2nd
record, and this is written to the browser. When you press the "Next" button
again, this process is repeated (recordset opened at the first record, and
the you move to the 2nd record), and the same record is written to the
screen (hence it looks like the page is just refreshing)
What you need to do is also pass across (eg via hidden form elements) the
record number that you are up to. Open the recordset, and then move to the
record you are up to (plus or minus 1 depending on which button was
pressed).
I do have one serious problem with your code though:
: If IsObject(Session("pattinventory2k_conn")) Then
: Set conn = Session("pattinventory2k_conn")
: Else
: Set conn = Server.CreateObject("ADODB.Connection")
: conn.open "pattinventory2k","",""
: Set Session("??pattinventory2k_conn") = conn
: End If
has two problems. The first is that the second session variable doesn't have
the same name as the first session variable.
The second, more serious problem, is that you are storing an ADO connection
object in a session variable. NEVER do this. It means that each user will
have their own connection to the database that will be held open regardless
of whether the user is using it or not. THis is bad, as it limits the number
of users that can use your application. Instead, you should open and close
your connection object on each page. This allows you to take advantage of
Connection Pooling:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
/pooling2.asp
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Lucas" <itadmin@p...>
To: "Access ASP" <access_asp@p...>
Sent: Thursday, March 07, 2002 1:48 PM
Subject: [access_asp] Re: One record at a time
: Thanks for the reply Ken here is my code:
:
: <HTML>
: <HEAD>
: <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
: <TITLE>OrderView</TITLE>
: </HEAD>
: <BODY background="../PATT%20Part%20Orders/pattbkglogo800x600.jpg">
: <%
: ' diming of all variables to be used
: dim avarfields
: dim bookmark
: dim varorder
: varorder = request.form("ordernumber")
: dim intrecords
: dim introw
: dim intcol
: dim intlastrow
: dim intlastcol
: Dim count
:
:
: 'Database connection string
: If IsObject(Session("pattinventory2k_conn")) Then
: Set conn = Session("pattinventory2k_conn")
: Else
: Set conn = Server.CreateObject("ADODB.Connection")
: conn.open "pattinventory2k","",""
: Set Session("??pattinventory2k_conn") = conn
: End If
: %>
: <%
: ' Get the data from the tables
: sql = "SELECT DISTINCTROW Orders.OrderID, Orders.ShipDate, Orders.
: [Promised-byDate], Orders.ShipName, Orders.ShipAddress1, Orders.ShipState,
: Orders.ShipCountry, Orders.ShipCity, Orders.ShipZip, [Order
: Details].QtyShip, [Order Details].ItemID, ItemMaster.ItemDesc FROM Orders
: INNER JOIN (ItemMaster INNER JOIN [Order Details] ON ItemMaster.ItemID
: [Order Details].ItemID) ON Orders.OrderID = [Order Details].OrderID WHERE
: ((([Order Details].ItemID)=[ItemMaster].[ItemID])) ORDER BY
: Orders.OrderID "
: Set rs = Server.CreateObject("ADODB.Recordset")
: rs.Open sql, conn, 3, 3
: %>
: <%
: Sub nextrecord()
: if not rs.eof then
: rs.movenext
: end if
: End sub
:
: If Request.Form("next") <> "" then
: if not rs.eof then
: call nextrecord()
: end if
: End If
:
: If Request.Form("previous") <> "" Then
: if not count < 1 then
: rs.moveprevious
: End if
: End if
: %>
: <h1><font color="#FFFF00">PATT Filtration Orders To Ship</font></h1>
: <br>
: <br>
:
Message #5 by "Lucas" <itadmin@p...> on Fri, 15 Mar 2002 20:08:59
|
|
> The problem is this:
>
> When someone presses the next button, you call the routine: nextrecord()
> This routine does the following:
> <%
> If not objRS.EOF then
> objRS.movenext
> End If
> %>
>
> The problem is that when you open a recordset, you are always at the
first
> record. Then, your routine is called, and the recordset is moved to the
2nd
> record, and this is written to the browser. When you press the "Next"
button
> again, this process is repeated (recordset opened at the first record,
and
> the you move to the 2nd record), and the same record is written to the
> screen (hence it looks like the page is just refreshing)
>
> What you need to do is also pass across (eg via hidden form elements) the
> record number that you are up to. Open the recordset, and then move to
the
> record you are up to (plus or minus 1 depending on which button was
> pressed).
>
> I do have one serious problem with your code though:
>
> : If IsObject(Session("pattinventory2k_conn")) Then
> : Set conn = Session("pattinventory2k_conn")
> : Else
> : Set conn = Server.CreateObject("ADODB.Connection")
> : conn.open "pattinventory2k","",""
> : Set Session("??pattinventory2k_conn") = conn
> : End If
>
> has two problems. The first is that the second session variable doesn't
have
> the same name as the first session variable.
>
> The second, more serious problem, is that you are storing an ADO
connection
> object in a session variable. NEVER do this. It means that each user will
> have their own connection to the database that will be held open
regardless
> of whether the user is using it or not. THis is bad, as it limits the
number
> of users that can use your application. Instead, you should open and
close
> your connection object on each page. This allows you to take advantage of
> Connection Pooling:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dnmdac/html
> /pooling2.asp
>
> Cheers
> Ken
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ----- Original Message -----
> From: "Lucas" <itadmin@p...>
> To: "Access ASP" <access_asp@p...>
> Sent: Thursday, March 07, 2002 1:48 PM
> Subject: [access_asp] Re: One record at a time
>
>
> : Thanks for the reply Ken here is my code:
> :
> : <HTML>
> : <HEAD>
> : <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-
1252">
> : <TITLE>OrderView</TITLE>
> : </HEAD>
> : <BODY background="../PATT%20Part%20Orders/pattbkglogo800x600.jpg">
> : <%
> : ' diming of all variables to be used
> : dim avarfields
> : dim bookmark
> : dim varorder
> : varorder = request.form("ordernumber")
> : dim intrecords
> : dim introw
> : dim intcol
> : dim intlastrow
> : dim intlastcol
> : Dim count
> :
> :
> : 'Database connection string
> : If IsObject(Session("pattinventory2k_conn")) Then
> : Set conn = Session("pattinventory2k_conn")
> : Else
> : Set conn = Server.CreateObject("ADODB.Connection")
> : conn.open "pattinventory2k","",""
> : Set Session("??pattinventory2k_conn") = conn
> : End If
> : %>
> : <%
> : ' Get the data from the tables
> : sql = "SELECT DISTINCTROW Orders.OrderID, Orders.ShipDate, Orders.
> : [Promised-byDate], Orders.ShipName, Orders.ShipAddress1,
Orders.ShipState,
> : Orders.ShipCountry, Orders.ShipCity, Orders.ShipZip, [Order
> : Details].QtyShip, [Order Details].ItemID, ItemMaster.ItemDesc FROM
Orders
> : INNER JOIN (ItemMaster INNER JOIN [Order Details] ON ItemMaster.ItemID
> : [Order Details].ItemID) ON Orders.OrderID = [Order Details].OrderID
WHERE
> : ((([Order Details].ItemID)=[ItemMaster].[ItemID])) ORDER BY
> : Orders.OrderID "
> : Set rs = Server.CreateObject("ADODB.Recordset")
> : rs.Open sql, conn, 3, 3
> : %>
> : <%
> : Sub nextrecord()
> : if not rs.eof then
> : rs.movenext
> : end if
> : End sub
> :
> : If Request.Form("next") <> "" then
> : if not rs.eof then
> : call nextrecord()
> : end if
> : End If
> :
> : If Request.Form("previous") <> "" Then
> : if not count < 1 then
> : rs.moveprevious
> : End if
> : End if
> : %>
> : <h1><font color="#FFFF00">PATT Filtration Orders To Ship</font></h1>
> : <br>
> : <br>
> :
>
>
Message #6 by "Lucas" <itadmin@p...> on Fri, 15 Mar 2002 20:10:01
|
|
I have tried to find examples of hidden variables and cannot find any,
could you try and give me an example I could work with?
Thanks,
Lucas
> The problem is this:
>
> When someone presses the next button, you call the routine: nextrecord()
> This routine does the following:
> <%
> If not objRS.EOF then
> objRS.movenext
> End If
> %>
>
> The problem is that when you open a recordset, you are always at the
first
> record. Then, your routine is called, and the recordset is moved to the
2nd
> record, and this is written to the browser. When you press the "Next"
button
> again, this process is repeated (recordset opened at the first record,
and
> the you move to the 2nd record), and the same record is written to the
> screen (hence it looks like the page is just refreshing)
>
> What you need to do is also pass across (eg via hidden form elements) the
> record number that you are up to. Open the recordset, and then move to
the
> record you are up to (plus or minus 1 depending on which button was
> pressed).
>
> I do have one serious problem with your code though:
>
> : If IsObject(Session("pattinventory2k_conn")) Then
> : Set conn = Session("pattinventory2k_conn")
> : Else
> : Set conn = Server.CreateObject("ADODB.Connection")
> : conn.open "pattinventory2k","",""
> : Set Session("??pattinventory2k_conn") = conn
> : End If
>
> has two problems. The first is that the second session variable doesn't
have
> the same name as the first session variable.
>
> The second, more serious problem, is that you are storing an ADO
connection
> object in a session variable. NEVER do this. It means that each user will
> have their own connection to the database that will be held open
regardless
> of whether the user is using it or not. THis is bad, as it limits the
number
> of users that can use your application. Instead, you should open and
close
> your connection object on each page. This allows you to take advantage of
> Connection Pooling:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dnmdac/html
> /pooling2.asp
>
> Cheers
> Ken
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ----- Original Message -----
> From: "Lucas" <itadmin@p...>
> To: "Access ASP" <access_asp@p...>
> Sent: Thursday, March 07, 2002 1:48 PM
> Subject: [access_asp] Re: One record at a time
>
>
> : Thanks for the reply Ken here is my code:
> :
> : <HTML>
> : <HEAD>
> : <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-
1252">
> : <TITLE>OrderView</TITLE>
> : </HEAD>
> : <BODY background="../PATT%20Part%20Orders/pattbkglogo800x600.jpg">
> : <%
> : ' diming of all variables to be used
> : dim avarfields
> : dim bookmark
> : dim varorder
> : varorder = request.form("ordernumber")
> : dim intrecords
> : dim introw
> : dim intcol
> : dim intlastrow
> : dim intlastcol
> : Dim count
> :
> :
> : 'Database connection string
> : If IsObject(Session("pattinventory2k_conn")) Then
> : Set conn = Session("pattinventory2k_conn")
> : Else
> : Set conn = Server.CreateObject("ADODB.Connection")
> : conn.open "pattinventory2k","",""
> : Set Session("??pattinventory2k_conn") = conn
> : End If
> : %>
> : <%
> : ' Get the data from the tables
> : sql = "SELECT DISTINCTROW Orders.OrderID, Orders.ShipDate, Orders.
> : [Promised-byDate], Orders.ShipName, Orders.ShipAddress1,
Orders.ShipState,
> : Orders.ShipCountry, Orders.ShipCity, Orders.ShipZip, [Order
> : Details].QtyShip, [Order Details].ItemID, ItemMaster.ItemDesc FROM
Orders
> : INNER JOIN (ItemMaster INNER JOIN [Order Details] ON ItemMaster.ItemID
> : [Order Details].ItemID) ON Orders.OrderID = [Order Details].OrderID
WHERE
> : ((([Order Details].ItemID)=[ItemMaster].[ItemID])) ORDER BY
> : Orders.OrderID "
> : Set rs = Server.CreateObject("ADODB.Recordset")
> : rs.Open sql, conn, 3, 3
> : %>
> : <%
> : Sub nextrecord()
> : if not rs.eof then
> : rs.movenext
> : end if
> : End sub
> :
> : If Request.Form("next") <> "" then
> : if not rs.eof then
> : call nextrecord()
> : end if
> : End If
> :
> : If Request.Form("previous") <> "" Then
> : if not count < 1 then
> : rs.moveprevious
> : End if
> : End if
> : %>
> : <h1><font color="#FFFF00">PATT Filtration Orders To Ship</font></h1>
> : <br>
> : <br>
> :
>
>
Message #7 by "Ken Schaefer" <ken@a...> on Sun, 17 Mar 2002 17:12:23 +1100
|
|
<%
intNextRecord = intCurrentRecord + 1
intPreviousRecord = intCurrentRecord - 1
%>
<form method="post">
<input type="hidden" name="txtNextRecord"
value="<%=intPreviousRecord%>">
<input type="submit" value="Previous">
</form>
<form method="post">
<input type="hidden" name="txtNextRecord" value="<%=intNextRecord%>">
<input type="submit" value="Next">
</form>
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Lucas" <itadmin@p...>
Subject: [access_asp] Re: One record at a time
: I have tried to find examples of hidden variables and cannot find any,
: could you try and give me an example I could work with?
:
: Thanks,
: Lucas
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
View Cart
