Highlight missing field 1

[apex82]

First of all not sure if this is in the correct forum as I think it could qualify as ASP or SQL.

I’m displaying records on a web page that I get from an SQL database. I want to highlight a record that has a field missing.

So for example if I have five columns and any one of the columns has a missing value I want to somehow highlight this possibly using a different text colour or having a small image next to it.

I display the records like so:

<select id="Unit">
<%
	' Unit
	SQL = "SELECT DISTINCT([Unit code]) AS [Unit code] FROM Unit ORDER BY [Unit code] ASC;"
	Set obj_RS1 = obj_CN.Execute(str_sql, adBoolean)
		
    ' Build list
	While Not obj_RS1.EOF
		
		str_Unit = obj_RS1("Unit code")
        str_Option = "<option value=""" & str_Unit & """#Selected#>" & str_Unit & "</option>"
        Response.Write vbtab & str_Option & vbcrlf
	    obj_RS1.MoveNext
	Wend
%>

Is this possible?

I’ve searched almost endlessly for examples but can’t come across anything.

Thanks.

 

[foxbox]

Your SQL is retrieving the unique values of [Unit code] table. Then your WHILE loop is creating all values as an OPTION for the SELECT...


So there is a [Unit code] missing in the table?
You want that code be a part of the pulldown list?

I presume there is a table with all possible codes, so why do the distinct and not simply query that code table?

Please make more clear what the problem is

 

[apex82]

Basically I will eventually have a page that will allow a user to fill in part of the information held in the record.

The reason being all the information might not be available at the time.

So, with that in mind say if a record is incomplete I'd like to somehow show that on the page.

For example, something like this ("BB" being the incomplete record):

AA, BB, CC, DD, EE

 

[foxbox]

Oke. that has nothing to do with a SELECT?

It now sounds like conditional formatting.
There is 1 record on screen with 5 fields, and one field is "empty"? To highlight that field:

if oRS("bb") <> "" then
 response.write  "<input type=text class=normal name=fBB value=" & oRS("bb") & ">"
else
 response.write  "<input type=text class=red name=fBB>"
end if

but somehow i think you want something else?

 

[apex82]

I display records from a joined table:

<%
	sql = "SELECT * FROM Business INNER JOIN System ON Business.Business_Id = System.BusinessID AND [ModuleID] = '1' ORDER BY [System Code] ASC;"

	Set rs = obj_CN.Execute(sql, adBoolean)
		
	While Not rs.EOF
		str_Code = rs("System Code")
        str_List = "<a href=""records.asp"">" & str_Code & "</a>"
        Response.Write vbtab & str_List & vbcrlf
	    rs.MoveNext
	Wend
%>

Basically when the record is added it can potentially have a field missing that the user did not have because the information was not available at the time.

So in the table it would look like this:

System_ID | System Code | System Description | BusinessID | CoverID
1 | AA | Test | 3 | 4
2 | BB | Test | 3 | NULL
3 | CC | Test | 3 | 4

If for example the above record has a the CoverID missing on system code BB - I’d want to be able to somehow display this like so:

AA, BB, CC

Is it possible to adapt the code above?

Thanks.

 

[foxbox]

First of all: if a field is mandatory you must prevent entering NULL into the database ("not null") and do better input checking when adding and editing records.

IMHO you have to check each field seperately, maybe like this: (in your CSS you have a .red {color=red} or whatever)

sql = "SELECT * FROM Business INNER JOIN System ON Business.Business_Id = System.BusinessID AND [ModuleID] = '1' ORDER BY [System Code] ASC;"    
Set rs = obj_CN.Execute(sql, adBoolean)
While Not rs.EOF        

 Response.Write "<a href=""records.asp"""
 ' ?id= missing??

 if trim(rs("System Code")) = "" or _
    trim(rs"("System Description")) = "" or _
    isnull(rs("BusinessID")) or _
    isnull(rs("CoverID") then
  response.write " class=red "
 end if

 response.write ">" & rs("System Code") & "</a>" & vbTab
 rs.MoveNext    
Wend

 

[apex82]

Great, thanks very much!

I'll have a play with that.