SQL data

am 07.05.2007 18:50:04 von Raz

Hello,

I have setup a session id that pulls up the current record that is selected
from the asp page, but somehow the last record is always being pulled up.
Any ideas why? Code is below. ASP Page1 is the page where user clicks to
retrieve data from SQL and ASP Page2 displays the data. Thanks.

SP:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Select_BDIPCC_spec]
@SID int
AS
SET NOCOUNT ON
SELECT * FROM BDIPCC WHERE SID = @SID ORDER BY SID

ASP Page1:

table width="600" bgcolor="#C0C0C0">

<%
Dim rsSelect_BDIPCC_spec
Set rsSelect_BDIPCC_spec=dbconn.Execute ("exec usp_Select_BDIPCC_spec " &
Session("SID"))
If Not rsSelect_BDIPCC_spec.eof Then
Do While Not rsSelect_BDIPCC_spec.eof

DIM xx_BDIPCC_ID
DIM xx_Date1
DIM xx_Week1
DIM xx_Eval_ID
DIM xx_SID
DIM xx_Track_ID
DIM xx_Staus
DIM xx_Rand_ID
xx_BDIPCC_ID = rsSelect_BDIPCC_spec("BDIPCC_ID")
xx_Date1 = rsSelect_BDIPCC_spec("Date1")
xx_Week1 = rsSelect_BDIPCC_spec("Week1")
xx_VisitNum = rsSelect_BDIPCC_spec("VisitNum")
xx_Eval_ID = rsSelect_BDIPCC_spec("Eval_ID")
xx_SID = rsSelect_BDIPCC_spec("SID")
xx_Track_ID = rsSelect_BDIPCC_spec("Track_ID")
xx_Status = rsSelect_BDIPCC_spec("Status")
xx_Rand_ID = rsSelect_BDIPCC_spec("Rand_ID")
%>

<%
'Set the main Session SID variable here so it can be easily used
throughout all of the other sub pages.
Session("BDIPCC_ID") = xx_BDIPCC_ID
%>

Date	Week	Visit	Eval_ID	SID	Track_ID	Status	Rand_ID
Mode: [ value="Edit" checked>Edit \| value="BDI">BDI]
value="<%=xx_Date1%>">	value="<%=xx_Week1%>">	value="<%=xx_VisitNum%>">	value="<%=xx_Eval_ID%>">	value="<%=xx_SID%>">	value="<%=xx_Track_ID%>">	value="<%=xx_Status%>">	value="<%=xx_Rand_ID%>">	onmouseover="window.status='Click here for menu.';return true" onMouseOut="window.status=''">	onmouseover="window.status='Click here for menu.';return true" onMouseOut="window.status=''">

<%
rsSelect_BDIPCC_spec.MoveNext
Loop

'Close and release the rs
rsSelect_BDIPCC_spec.Close : Set rsSelect_BDIPCC_spec=Nothing

End If

'Close and release the connection
dbconn.Close : Set dbconn = Nothing
%>

ASP Page2:

Screen ID: value="<%=zz_SID%>">
Initials: value="<%=zz_Initials%>">
Rand ID: value="<%=zz_Rand_ID%>">

<%
Dim rsSelect_BDIPCC
Set rsSelect_BDIPCC=dbconn.Execute ("exec usp_Select_BDIPCC " &
Session("BDIPCC_ID"))

DIM xx_BDIPCC_ID
DIM xx_Date1
DIM xx_Week1
DIM xx_Eval_ID
DIM xx_SID
DIM xx_Track_ID
DIM xx_Staus
DIM xx_Rand_ID
xx_BDIPCC_ID = rsSelect_BDIPCC("BDIPCC_ID")
xx_Date1 = rsSelect_BDIPCC("Date1")
xx_Week1 = rsSelect_BDIPCC("Week1")
xx_Eval_ID = rsSelect_BDIPCC("Eval_ID")
xx_SID = rsSelect_BDIPCC("SID")
xx_Track_ID = rsSelect_BDIPCC("Track_ID")
xx_Status = rsSelect_BDIPCC("Status")
xx_Rand_ID = rsSelect_BDIPCC("Rand_ID")
%>

<%

'Close and release the rs
rsSelect_BDIPCC.Close : Set rsSelect_BDIPCC=Nothing

%>


Rand ID	value="<%=xx_Rand_ID%>">
Screen ID	<%=xx_SID%>
Week	value="<%=xx_Week1%>">
Visit	value="<%=xx_VisitNum%>">
Date	value="<%=xx_Date1%>">
Evaluator ID	value="<%=xx_Eval_ID%>">
Status	value="<%=xx_Status%>">
Track ID	<%=xx_Track_ID%>

<%
'Close and release the rs
rsSelect_CRF_specific.Close : Set rsSelect_CRF_specific=Nothing

'Close and release the connection
dbconn.Close : Set dbconn = Nothing
%>

onmouseover="window.status='Click here for menu.';return true"
onMouseOut="window.status=''"
onclick="this.form.action='bdiform.asp';">

<%response.write xx_BDIPCC_ID
response.end%>

Re: SQL data

am 07.05.2007 19:32:40 von reb01501

Raz wrote:
> Hello,
>
> I have setup a session id that pulls up the current record that is
> selected from the asp page, but somehow the last record is always
> being pulled up. Any ideas why? Code is below. ASP Page1 is the
> page where user clicks to retrieve data from SQL and ASP Page2
> displays the data. Thanks.

First step:
Run the stored procedure in Query Analyzer using the SID you are passing
from ASP (you should Response.Write Session("SID") to verify that it
contains what you expect) and verify that the procedure returns the expected
records. If it does, then it's time to look at your asp code.

As for the code, please snip out irrelevant html.

> ASP Page1:
>

> <%
> Dim rsSelect_BDIPCC_spec
> Set rsSelect_BDIPCC_spec=dbconn.Execute ("exec
> usp_Select_BDIPCC_spec " & Session("SID"))

I would have done it this way (here's why:
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/5d3c9d4409dc1701?hl=en):

set rsSelect=CreateObject("adodb.recordset")
dbconn.usp_Select_BDIPCC_spec Session("SID"), _
rsSelect

Anyways, just to ferify whether your code is at fault, run the following:

dim arData
if not rsSelect.eof then
arData=rsSelect.getrows
end if
rsSelect.close: set rsSelect = nothing
dbConn.close: set dbConn = nothing
if isArray(arData) then
response.write "arData contains " & ubound(arData,2) & _
" rows
"
else
response.write "arData contains zero rows
"
end if
%>

If ardata contains the correct number of rows, then you need to peruse the
code used to generate the table html and verify that it is correctly
creating a new element when processing each record of the recordset,
and not simply overwriting the data in the initially created element.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"