List boxes 2

[Sunil606]

Hi
I have a form which has a dynamic list box containing data from a db. I have written a SQL query against a recordset and filled the recordset object with the results of the SQL query. Here is the query:

Dim objRS
Dim strSQL
Set objRS = Server.CreateObject("ADODB.Recordset&quot
strSQL = "SELECT FaultID FROM Faults WHERE Status= 'UnAssigned'"
objRS.Open strSQL, strConnect

If the SQL query has recordsets to return then this should be displayed:


<FORM ACTION=&quot;ProcessJob.asp&quot; METHOD=&quot;POST&quot;>
<P><B>Select FaultID       </B><SELECT NAME=&quot;FAULTID&quot; SIZE=&quot;1&quot;>'
<%



Do While NOT objRS.EOF
Response.Write &quot;<OPTION VALUE='&quot; & objRS(&quot;FaultID&quot & &quot;'>&quot;
Response.Write objRS(&quot;FaultID&quot & &quot;</OPTION>&quot;
objRS.MoveNext
Loop
objRS.Close
Set objRS = Nothing
%>
</SELECT></P><BR>


If the recordset is empty then I would like to display a message saying that all jobs have been assigned and not to display the form/list box. Is there any way of doing this with ASP? If anyone has an idea could they please help.

Thanks in advance.

 

[WartookMan]

Not sure whether this helps, but can you get the &quot;number&quot; of results that the query will return first? Or alternatively, set up a loop to count the results returned BEFORE you attempt to write them out.

Move your SELECT tag within an IF statement.

If the number of results is zero, then use a Response.write to output the required message. Otherwise, output the results:

<%
	If (numresults = 0) Then
		Response.write(&quot;Everything assigned!&quot;);
	Else %>
<FORM ACTION=&quot;ProcessJob.asp&quot; METHOD=&quot;POST&quot;>
<P><B>Select FaultID</B><SELECT NAME=&quot;FAULTID&quot; SIZE=&quot;1&quot;>
<%
	    Do While NOT objRS.EOF
			Response.Write &quot;<OPTION VALUE='&quot; & objRS(&quot;FaultID&quot;) & &quot;'>&quot;
			Response.Write objRS(&quot;FaultID&quot;) & &quot;</OPTION>&quot;
			objRS.MoveNext
		Loop
		objRS.Close
		Set objRS = Nothing
%>
</SELECT></P><BR><%
	End If
%>

This hasn't been tested...

Pete.


Lotus Notes Web Developer / Aptrix (LWWCM) Consultant
w:

http://www.lclimited.co.uk/

e: Pete.Raleigh(at)lclimited.co.uk

 

[woja]

Try this:

<%
   Dim objRS
   Dim strSQL
   Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)
   strSQL = &quot;SELECT FaultID FROM Faults WHERE Status= 'UnAssigned'&quot;
   objRS.Open strSQL, strConnect
%>
<%
   If (objRS.EOF) Then
%>
<P><B>No Faults found</B></P>
<%
   Else
<%
   <FORM ACTION=&quot;ProcessJob.asp&quot; METHOD=&quot;POST&quot;>
      <P>
         <B>Select FaultID</B>
         <SELECT NAME=&quot;FAULTID&quot; SIZE=&quot;1&quot;>'
<%
       Do While NOT objRS.EOF
           Response.Write &quot;<OPTION VALUE='&quot; & objRS(&quot;FaultID&quot;) & &quot;'>&quot;
           Response.Write objRS(&quot;FaultID&quot;) & &quot;</OPTION>&quot;
           objRS.MoveNext
       Loop
       objRS.Close
       Set objRS = Nothing
%>
         </SELECT>
      </P>
   </FORM>
<%
   End If
%>

Good luck.


________________________________________
Roger J Coult; Grimsby, UK
In the game of life the dice have an odd number of sides.