SQL data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Raz - 07 May 2007 17:50 GMT

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">
<form name="crf" method="post" action="bdiwk_action.asp" onsubmit="return
VerifyFields()">
<input name="Section" type="hidden" value="Example_AddNew">
<tr>
<th align="center">Date</th>
<th align="center">Week</th>
<th align="center">Visit</th>
<th align="center">Eval_ID</th>
<th align="center">SID</th>
<th align="center">Track_ID</th>
<th align="center">Status</th>
<th align="center">Rand_ID</th>
</tr>

<tr>
<td align="center"><INPUT TYPE="input" SIZE=8 NAME="Date1"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Week1"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="VisitNum"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Eval_ID"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="SID"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Track_ID"></td>
<td align="center"><INPUT TYPE="input" SIZE=8 NAME="Status""></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Rand_ID""></td>
<td colspan="9" align="right">
<input class="button" value="Add New Record" type="Submit"
onmouseover="window.status='Click here for menu.';return true"
onMouseOut="window.status=''"></td>
</tr>
</form>
</table>

<br>

<%
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")
%>

<table class="border" bgcolor="#C0C0C0">
<form name="crf" method="post" action="bdiwk_action.asp">
<input name="Section" type="hidden" value="Example_Edit" onsubmit="return
VerifyFields()">
<input name="BDIPCC_ID" type="hidden" value="<%=xx_BDIPCC_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
%>

<tr>
<td colspan="10" align="right">Mode: [<input name="Mode" type="radio"
value="Edit" checked>Edit | <input name="Mode" type="radio"
value="BDI">BDI]</td>
</tr>
<tr>
<th align="center">Date</th>
<th align="center">Week</th>
<th align="center">Visit</th>
<th align="center">Eval_ID</th>
<th align="center">SID</th>
<th align="center">Track_ID</th>
<th align="center">Status</th>
<th align="center">Rand_ID</th>
</tr>
<tr>
<td align="center"><INPUT TYPE="input" SIZE=8 NAME="Date1"
value="<%=xx_Date1%>"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Week1"
value="<%=xx_Week1%>"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="VisitNum"
value="<%=xx_VisitNum%>"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Eval_ID"
value="<%=xx_Eval_ID%>"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="SID"
value="<%=xx_SID%>"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Track_ID"
value="<%=xx_Track_ID%>"></td>
<td align="center"><INPUT TYPE="input" SIZE=8 NAME="Status"
value="<%=xx_Status%>"></td>
<td align="center"><INPUT TYPE="input" SIZE=2 NAME="Rand_ID"
value="<%=xx_Rand_ID%>"></td>
<td><input class="button" value="Edit" type="Submit"
onmouseover="window.status='Click here for menu.';return true"
onMouseOut="window.status=''"></td>
<td><input class="button" type="submit" value="BDI"
onmouseover="window.status='Click here for menu.';return true"
onMouseOut="window.status=''"></td>
<td width="76"></td>
</tr>
</form>
</table>

<%
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:

<table width="500" class="border" bgcolor="#C0C0C0">
<form name="crf" method="post">
<tr>
<td><div align="center">Screen ID: <INPUT TYPE="int" SIZE=4 NAME="SID"
value="<%=zz_SID%>">    
Initials: <INPUT TYPE="text" SIZE=8 NAME="Initials"
value="<%=zz_Initials%>">    
Rand ID: <INPUT TYPE="int" SIZE=4 NAME="Rand_ID"
value="<%=zz_Rand_ID%>"></div><p>

<table width="500" align="left">

<%
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")
%>

<input name="Section" type="hidden" value="Example_Edit" onsubmit="return
VerifyFields()">
<input name="BDIPCC_ID" type="hidden" value="<%=xx_BDIPCC_ID%>">
<tr>
<td colspan="7"><hr color="blue"></td>
</tr>
<tr>
<th align="right">Rand ID  </th>
<td align="left"><INPUT TYPE="int" SIZE=2 NAME="Eval_ID"
value="<%=xx_Rand_ID%>"></td>
</tr>
<tr>
<th align="right">Screen ID  </th>
<td align="left"><%=xx_SID%></td>
</tr>
<tr>
<th align="right">Week  </th>
<td align="left"><INPUT TYPE="int" SIZE=2 NAME="Week1"
value="<%=xx_Week1%>"></td>
</tr>
<tr>
<th align="right">Visit  </th>
<td align="left"><INPUT TYPE="int" SIZE=2 NAME="VisitNum"
value="<%=xx_VisitNum%>"></td>
</tr>
<tr>
<th align="right">Date  </th>
<td align="left"><INPUT TYPE="date" SIZE=8 NAME="Date1"
value="<%=xx_Date1%>"></td>
</tr>
<tr>
<th align="right">Evaluator ID  </th>
<td align="left"><INPUT TYPE="int" SIZE=2 NAME="Eval_ID"
value="<%=xx_Eval_ID%>"></td>
</tr>
<tr>
<th align="right">Status  </th>
<td align="left"><INPUT TYPE="text" SIZE=8 NAME="Status"
value="<%=xx_Status%>"></td>
</tr>
<tr>
<th align="right">Track ID  </th>
<td align="left"><%=xx_Track_ID%></td>
</tr>

<%

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

%>

</table>
</td>
</tr>
</table>

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

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

<p>

<table>
<tr>
<td><input class="button" type="submit" value="BDI"
onmouseover="window.status='Click here for menu.';return true"
onMouseOut="window.status=''"
onclick="this.form.action='bdiform.asp';"></td>
</tr>
</table>
<%response.write xx_BDIPCC_ID
response.end%>

Reply to this Message

Bob Barrows [MVP] - 07 May 2007 18:32 GMT

> Hello,
>
[quoted text clipped - 3 lines]
> 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:

<snip>

> <%
> 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.asp.general/msg/5d3c9
d4409dc1701?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<BR>"
else
response.write "arData contains zero rows<BR>"
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 <tr> element when processing each record of the recordset,
and not simply overwriting the data in the initially created <tr> element.

Signature

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"

Reply to this Message

Webmaster Forum / ASP / Database Access / May 2007

SQL data