ASP / SQL 2000

[ScottTN]

I am creating an intranet site that allows our users to check status of applications online.

If multiple records are found, I get mutliple asp generated forms. I am trying to add a count function to run prior to running my main select statement. If the count is more that 1 I want to generate a different kind of asp page.

With that said, does anyone have and ideas. Below is a code snippet.

Thanks!

Scott

-----------------------------------------------------------

<%
Dim Start
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;WAM&quot;,&quot;sa&quot;,&quot;&quot;
Set Session(&quot;WAM_conn&quot = conn
%>
<%
sql = &quot;SELECT *&quot;
sql = sql & &quot; FROM tblDocumentData&quot; ' From Statement
sql = sql & &quot; WHERE lastname =&quot; & &quot;'&quot; & Request(&quot;lname&quot & &quot;'&quot; ' Where Statement
sql = sql & &quot; AND SSN =&quot; & &quot;'&quot; & Request(&quot;ssn&quot & &quot;'&quot; ' Where Statement
sql = sql & &quot; AND STATUS <> 'Other'&quot;
sql = sql & &quot; ORDER BY CreateDate&quot; ' Order By Statement

sql1 = &quot;SELECT COUNT&quot; ' SELECT STATEMENT
sql1 = sql1 & &quot; FROM tblDocumentData&quot; ' From Statement
sql1 = sql1 & &quot; WHERE lastname =&quot; & &quot;'&quot; & Request(&quot;lname&quot & &quot;'&quot; ' Where Statement
sql1 = sql1 & &quot; AND SSN =&quot; & &quot;'&quot; & Request(&quot;ssn&quot & &quot;'&quot; ' Where Statement
sql1 = sql1 & &quot; AND STATUS <> 'Other'&quot;

'RESPONSE.WRITE SQL1

' This function is used when the submit button is clicked
dim fs,fname,filename,strfilename

strfilename = &quot;scott&quot;
strfilename = strfilename & Request(&quot;firstname&quot
'response.write strfilename

set fs=Server.CreateObject(&quot;Scripting.FileSystemObject&quot
set fname=fs.CreateTextFile(&quot;c:\inetpub\crm\temp_files\&quot; & strfilename & &quot;.txt&quot;,true)
fname.WriteLine sql
fname.WriteLine sql1
fname.Close
set fname=nothing
set fs=nothing

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql, conn, 3, 3

If rs.eof Then
response.write &quot;No records found.&quot;

Else
Set Session(&quot;Query1_rs&quot = rs
%>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>

 

[BG12424]

Since you are running SQL Server, I am assuming you have capabilities of writing stored procedures.

First, create a stored procedure that has both of those sql statements in it; however, assign the select count sql to an output parameter, and the other will be your result set.

Next, write your ASP logic using the command object so you can return output parameters.

Then get the value of the output parameter and check if it is greater than 1, if so then write formA if not, then write formB.

I think this is the most efficient way to go about it, since you don't have to open 2 recordset objects to do it.

I Hope this helps.
_______________________________
regards,
Brian

AOL IM: FreelanceGaines

 

[BG12424]

Oh, one other thing caught my eye. You shouldn't store your recordset object in a session variable, since it can consume a large part of the memory your application uses to run. _______________________________
regards,
Brian

AOL IM: FreelanceGaines

 

[tonylnng]

In order to check the recordset have record or not you should use

If rs.eof and rs.bof Then

if both are true that means really no record found. Your case may not be handle 1 record found.