Showing current flag in loop

[jarv]

when I go to Edit a Report on my website I would like to show the currently selected flag and for the user to change it if they wish, at the moment, I am showing the currently selected flag on it's own and then looping through 3 flags if the user submits the form it would probably error saying they haven't selected a flag?!

Can someone please help?


FlagID = rs1("FlagID")
set rs3 = objconn.execute("SELECT * FROM flags WHERE FlagID ="&FlagID)

Current Flag:<img src="<%=rs3("FlagImage")%>" border="0" alt="flag colour" /><br />
<%Do While Not rs2.EOF %>
<img src="<%=rs2("FlagImage")%>" border="0" alt="Flag Colour" /><input name="FlagID" type="radio" value="<%=rs2("FlagID")%>" /><br />
<%rs2.MoveNext
Loop%>

[Old Pedant]

Well, it's only going to give an error if you coded it to give an error.

Why now just say "if no flag is selected, use the one already in the DB?"

But you know, if you'd show your Database Schema (that table names and relevant fields) I could probably suggest a better solution.

You are using THREE recordsets there, and ideally you'd want to use only one or, at most, two. Clearly never a reason for three.

But can't help without knowing what the DB looks like.

[jarv]

Well, it's only going to give an error if you coded it to give an error.

Why now just say "if no flag is selected, use the one already in the DB?"

But you know, if you'd show your Database Schema (that table names and relevant fields) I could probably suggest a better solution.

You are using THREE recordsets there, and ideally you'd want to use only one or, at most, two. Clearly never a reason for three.

But can't help without knowing what the DB looks like.

Database is as follows

Reports table
ReportID
FlagID
ReportDate
ReportConsultant
ReportBy


Flags table
FlagID
FlagColour
FlagImage

[Old Pedant]

Good! What I expected.

So here's the sneaky trick:


SQL = "SELECT F.FlagID, F.FlagColour, F.FlagImage, " _
& " IIF(R.FlagID IS NULL,'','CHECKED') AS Sel " _
& " FROM Flags AS F LEFT JOIN Reports AS R " _
& " ON ( F.FlagID = R.FlagID AND R.ReportID = " & reportid & " ) " _
& " ORDER BY F.FlagID " ' -- or whatever order
Set flagsRS = yourConnection.Execute( SQL )

Do Until flagsRS
fid = flagsRS("FlagID")
fimg = flagsRS("FlagImage")
fcolour = flagsRS("FlagColour")
fchk = flagsRS("chk")
%>
<input type=radio name="flagID" id="flagID<%=fid%>" value="<%=fid%>" <%=fchk%> />
<label for="flagID<%=fid%>">
<img src="<%=fimg%>" border="0" alt="Flag Colour <%=fcolour%>" />
</label>
<%
flagsRS.MoveNext
Loop
%>


Now all the flags will appear and the radio button indicating the previously chosen flag (if any!) will be pre-checked. The user can click on any radio button (or, thanks to the <label>, on any flag!) to choose a different flag.

That query is for Access. For MySQL, just change the IIF to IF. For SQL Server, use this:

SQL = "SELECT F.FlagID, F.FlagColour, F.FlagImage, " _
& "(CASE WHEN R.FlagID IS NULL THEN '' ELSE 'CHECKED' END) AS Sel " _
& " FROM Flags AS F LEFT JOIN Reports AS R " _
& " ON ( F.FlagID = R.FlagID AND R.ReportID = " & reportid & " ) " _
& " ORDER BY F.FlagID " ' -- or whatever order


All of the top of my head, but I've used this technique dozens of times in the past.

It all works because of the LEFT JOIN. Naturally the variable reportid is the current report. Change the variable name to whatever matches your code.

[jarv]

see post below

[jarv]

ok so now I have:


<%SQL = "SELECT * FROM flags INNER JOIN reports ON flags.FlagID = reports.FlagID WHERE flags.FlagID = reports.FlagID" ' -- or whatever order
Set flagsRS = objconn.Execute( SQL )

While Not flagsRS.EOF
fid = flagsRS("flags.FlagID")
fimg = flagsRS("FlagImage")
fcolour = flagsRS("FlagColour")
fchk = "checked"
%>
<input type=radio name="flagID" id="flagID<%=fid%>" value="<%=fid%>" <%=fchk%> />
<label for="flagID<%=fid%>">
<img src="<%=fimg%>" border="0" alt="Flag Colour <%=fcolour%>" />
</label>
<%
flagsRS.MoveNext
Wend
%>

All flags in the reports table are shown and the last one is checked?!

Please help?!

[Old Pedant]

*SIGH* I did *NOT* use an INNER JOIN.

And I did *NOT* join to *ALL* Reports in the reports table.

And I perhaps foolishly assumed that you wanted a separate flag chosen for each report, which is why I used reportID in my query.

I gather you didn't like my solution, but you don't say in what way it was unsuitable, so I have no clue what to answer next.