Hi,

I'm trying to create an update record page where I have a couple Dynamic
List/Menus and Text Input fields. The Dynamic Menus only show the current
value of the record and not the lookup values available to that field from
the Joined table. I've tried changing the SQL statement, but then I either
get every instance of all records, or a busted page.

I have the table info, as wellas the whole page below it.

I've searched and found topics talking about using GROUP BY, or ORDER BY, but I have two dropdown menu and I haven't been able to get either of these to work as well.

Thank you!

Jose

Table info

<table width="100%" border="1">
<tr>
<td width="46%" height="23"><div align="right">Image ID
</div></td>
<td width="54%"><div
align="left"><%=(rsImage.Fields.Item("ImageID".Value)%></div></td>
</tr>
<tr>
<td height="23"><div align="right">Client</div></td>
<td> <select name="select2">
<%
While (NOT rsImage.EOF)
%>
<option value="<%=(rsImage.Fields.Item("ClientID".Value)%>"
<%If (Not isNull((rsImage.Fields.Item("ClientID".Value))) Then If
(CStr(rsImage.Fields.Item("ClientID".Value) =
CStr((rsImage.Fields.Item("ClientID".Value))) Then
Response.Write("SELECTED" : Response.Write(""%>
><%=(rsImage.Fields.Item("ClientName".Value)%></option>
<%
rsImage.MoveNext()
Wend
If (rsImage.CursorType > 0) Then
rsImage.MoveFirst
Else
rsImage.Requery
End If
%>
</select></td>
</tr>
<tr>
<td height="23"><div align="right">Project</div></td>
<td> <select name="select">
<%
While (NOT rsImage.EOF)
%>
<option
value="<%=(rsImage.Fields.Item("ProjectID".Value)%>" <%If (Not
isNull((rsImage.Fields.Item("ProjectName".Value))) Then If
(CStr(rsImage.Fields.Item("ProjectID".Value) =
CStr((rsImage.Fields.Item("ProjectName".Value))) Then
Response.Write("SELECTED" : Response.Write(""%>
><%=(rsImage.Fields.Item("ProjectName".Value)%></option>
<%
rsImage.MoveNext()
Wend
If (rsImage.CursorType > 0) Then
rsImage.MoveFirst
Else
rsImage.Requery
End If
%>
</select></td>
</tr>
<tr>
<td height="23"><div align="right">Image File Name</div></td>
<td><input name="textfield2" type="text"
value="<%=((rsImage.Fields.Item("ImageFileName".Value))%>"></td>
</tr>
<tr>
<td height="23"><div align="right">Thumb Up</div></td>
<td> <input
value="<%=((rsImage.Fields.Item("ThumbUpFileName".Value))%>" type="text"
name="textfield"></td>
</tr>
<tr>
<td height="23"> <div align="right">Thumb Down</div></td>
<td> <input
value="<%=((rsImage.Fields.Item("ThumbDownFileName".Value))%>" type="text"
name="textfield3"></td>
</tr>
<tr>
<td height="23" colspan="2"> <div align="center">
<input type="submit" name="Submit" value="Submit">
<input name="Reset" type="reset" id="Reset2" value="Reset">
</div></td>
</tr>
</table>

The whole page


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/rory.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME")
If (Request.QueryString <> "" Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Update Record: set variables

If (CStr(Request("MM_update") = "form1" And CStr(Request("MM_recordId") <>
"" Then

MM_editConnection = MM_rory_STRING
MM_editTable = "Images"
MM_editColumn = "ImageID"
MM_recordId = "" + Request.Form("MM_recordId" + ""
MM_editRedirectUrl = "Image.asp"
MM_fieldsStr =
"select2|value|select|value|textfield2|value|textfield|value|textfield3|valu
e"
MM_columnsStr =
"ClientID|none,none,NULL|ProjectID|none,none,NULL|ImageFileName|',none,''|Th
umbUpFileName|',none,''|ThumbDownFileName|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "" Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>
<%
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update") <> "" And CStr(Request("MM_recordId") <> ""
Then

' create the sql update statement
MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),","
MM_delim = MM_typeArray(0)
If (MM_delim = "none" Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none" Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none" Then MM_emptyVal = ""
If (MM_formVal = "" Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "" Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'" Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''" & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " &
MM_recordId

If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim rsImage__MMColParam
rsImage__MMColParam = "1"
If (Request.Querystring("ImageID" <> "" Then
rsImage__MMColParam = Request.Querystring("ImageID"
End If
%>
<%
Dim rsImage
Dim rsImage_numRows

Set rsImage = Server.CreateObject("ADODB.Recordset"
rsImage.ActiveConnection = MM_rory_STRING
rsImage.Source = "SELECT Category.CategoryName, Client.ClientName,
Project.ProjectName, Images.ImageFileName, Images.ThumbUpFileName,
Images.ThumbDownFileName, Category.CategoryID, Client.ClientID,
Project.ProjectID, Images.ImageID FROM (Category INNER JOIN (Client INNER
JOIN Images ON Client.ClientID = Images.ClientID) ON Category.CategoryID =
Images.CategoryID) INNER JOIN Project ON Images.ProjectID =
Project.ProjectID WHERE ImageID = " + Replace(rsImage__MMColParam, "'",
"''" + ""
rsImage.CursorType = 0
rsImage.CursorLocation = 2
rsImage.LockType = 1
rsImage.Open()

rsImage_numRows = 0
%>

<html>
<head>
<title>rory earnshaw : project editor</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="640" border="0" align="center">
<tr>
<td width="48"> </td>
<td width="539"> </td>
<td width="31"> </td>
</tr>
<tr>
<td height="93"> </td>
<td> <form name="form1" method="POST" action="<%=MM_editAction%>">
<table width="100%" border="1">
<tr>
<td width="46%" height="23"><div align="right">Image ID
</div></td>
<td width="54%"><div
align="left"><%=(rsImage.Fields.Item("ImageID".Value)%></div></td>
</tr>
<tr>
<td height="23"><div align="right">Client</div></td>
<td> <select name="select2">
<%
While (NOT rsImage.EOF)
%>
<option value="<%=(rsImage.Fields.Item("ClientID".Value)%>"
<%If (Not isNull((rsImage.Fields.Item("ClientID".Value))) Then If
(CStr(rsImage.Fields.Item("ClientID".Value) =
CStr((rsImage.Fields.Item("ClientID".Value))) Then
Response.Write("SELECTED" : Response.Write(""%>
><%=(rsImage.Fields.Item("ClientName".Value)%></option>
<%
rsImage.MoveNext()
Wend
If (rsImage.CursorType > 0) Then
rsImage.MoveFirst
Else
rsImage.Requery
End If
%>
</select></td>
</tr>
<tr>
<td height="23"><div align="right">Project</div></td>
<td> <select name="select">
<%
While (NOT rsImage.EOF)
%>
<option
value="<%=(rsImage.Fields.Item("ProjectID".Value)%>" <%If (Not
isNull((rsImage.Fields.Item("ProjectName".Value))) Then If
(CStr(rsImage.Fields.Item("ProjectID".Value) =
CStr((rsImage.Fields.Item("ProjectName".Value))) Then
Response.Write("SELECTED" : Response.Write(""%>
><%=(rsImage.Fields.Item("ProjectName".Value)%></option>
<%
rsImage.MoveNext()
Wend
If (rsImage.CursorType > 0) Then
rsImage.MoveFirst
Else
rsImage.Requery
End If
%>
</select></td>
</tr>
<tr>
<td height="23"><div align="right">Image File Name</div></td>
<td><input name="textfield2" type="text"
value="<%=((rsImage.Fields.Item("ImageFileName".Value))%>"></td>
</tr>
<tr>
<td height="23"><div align="right">Thumb Up</div></td>
<td> <input
value="<%=((rsImage.Fields.Item("ThumbUpFileName".Value))%>" type="text"
name="textfield"></td>
</tr>
<tr>
<td height="23"> <div align="right">Thumb Down</div></td>
<td> <input
value="<%=((rsImage.Fields.Item("ThumbDownFileName".Value))%>" type="text"
name="textfield3"></td>
</tr>
<tr>
<td height="23" colspan="2"> <div align="center">
<input type="submit" name="Submit" value="Submit">
<input name="Reset" type="reset" id="Reset2" value="Reset">
</div></td>
</tr>
</table>
<input type="hidden" name="MM_update" value="form1">
<input type="hidden" name="MM_recordId" value="<%=
rsImage.Fields.Item("ImageID".Value %>">
</form>

</td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</body>
</html>
<%
rsImage.Close()
Set rsImage = Nothing
%>