Here is a new one as I found nothing in the forum on it.
I have an edit/add page that has several database lookups for drop downs. Some of them work and others don't even show up on the page. Here is the code for the page....
*****
<%@ Language=VBScript %>
<html>
<link REL="stylesheet" href="include/style.css" type="text/css">
<!--#include file="include/validate.htm"-->
<!--#include file="include/PlateUsage_dbconnection.asp"-->
<!--#include file="include/PlateUsage_variables.asp"-->
<!--#include file="include/PlateUsage_aspfunctions.asp"-->
<body bgcolor=white onLoad=" define('Initials', 'IsRequired', '<%=Label("Initials")%>');define('Job Code', 'IsRequired', '<%=Label("Job Code")%>');define('Job Number', 'IsRequired', '<%=Label("Job Number")%>')">
<script language="JavaScript" src="include/ts_picker.js">
</script><form method="POST" action="PlateUsagelist.asp" name="frmAdmin">
<input type=hidden id="TargetPageNumber" name="TargetPageNumber" value="<%=Request.Form("TargetPageNumber")%>" >
<input type=hidden id=SQL name=SQL value="<%=Request.Form("SQL")%>">
<input type=hidden id="sourceID" name="sourceID" value="<%=sourceID%>" >
<input type=hidden id="cmdGotoPage" name="cmdGotoPage">
<script language="JavaScript" src="include/jsfunctions.js">
</script>
<script language="JavaScript" >
function GotoPage(nPageNumber)
{
document.forms.frmAdmin.cmdGotoPage.value = 'GotoPage';
document.forms.frmAdmin.TargetPageNumber.value = nPageNumber;
document.forms.frmAdmin.submit();
}
</script>
</form>
<%
On Error Resume Next
if InStr(1, strConnection, "Microsoft Access Driver") > 0 then
set oCat = server.CreateObject("ADOX.Catalog")
if Err.number=0 then
ind = InStr(1, strConnection, "DBQ=")
if ind>0 then
ind2=InStr(ind+1, strConnection, ";")
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=" & Mid(strConnection, ind+Len("DBQ="), ind2-ind-Len("DBQ="))
if Left(strTableName,1)="[" then
strADOXTableName = Mid(strTableName,2,len(strTableName)-2)
else
strADOXTableName = strTableName
end if
end if
end if
Err.Clear
end if
message=""
mypage=Request.Form("targetpagenumber")
if mypage="" then
mypage=1
myaction=Request.Form("action")
if myaction="view" then
myaction="edit"
if myaction="add" then
myaction="added"
' open database connection
Set rs = server.CreateObject ("ADODB.Recordset")
set dbConnection = server.CreateObject ("ADODB.Connection")
dbConnection.ConnectionString = strConnection
dbConnection.Open
Call ReportError
if (Request.Form("NeedQuotes"&BuildFieldName(strKeyField)))="True" then
strQuote="'"
else
strQuote=""
end if
if (Request.Form("NeedQuotes"&BuildFieldName(strKeyField2)))="True" then
strQuote2="'"
else
strQuote2=""
end if
if (Request.Form("NeedQuotes"&BuildFieldName(strKeyField3)))="True" then
strQuote3="'"
else
strQuote3=""
end if
' insert new record if we have to
if Request.Form("action")="added" then
strSQL = "insert into " & strTableName & " "
strFields = "("
strValues = "("
' fields list
For i = 1 To Request.Form("FieldName").Count
' skip key fields
do while Request.Form("Updatable")(i)<>"True"
i=i+1
if i>=Request.Form("FieldName").Count then exit do
loop
if i>Request.Form("FieldName").Count then exit for
strFields=strFields & AddWrappers(Request.Form("FieldName")(i)) & ", "
nType = Request.Form("FieldType")(i)
' build list of values
' boolean
if Request.Form("FieldType")(i)="11" then
if Request.Form(Request.Form("FieldName")(i))="on" then
strValue="1"
else
strValue="0"
end if
else ' not boolean
' Date or GUID field with no value
if Request.Form(Request.Form("FieldName")(i))="" and ( nType=72 or nType=133 or nType=134 or nType=135 ) then
strValue = "NULL"
else
if (Request.Form("NeedQuotes")(i))="True" then
strValue= "'" & Replace( Request.Form(Request.Form("FieldName")(i)) , "'", "''") & "'"
else
if Request.Form(Request.Form("FieldName")(i))="" then
strValue="0"
else
strValue=Request.Form(Request.Form("FieldName")(i))
end if
end if
end if
end if
strValues = strValues & strValue & ", "
Next
' adjust strFields and strValues
if Right(strFields,2)=", " then strFields = Left(strFields, Len(strFields)-2)
if Right(strValues,2)=", " then strValues = Left(strValues, Len(strValues)-2)
strFields = strFields & ")"
strValues = strValues & ")"
strSQL = strSQL & strFields & " values " & strValues
LogInfo(strSQL)
dbConnection.Execute strSQL
Call ReportError
message="<div class=message><<< Record was added >>></div>"
end if
' delete image
if Request.Form("saveid")<>"" and Request.Form("action")="deleteimage" then
strSQL="update " & strTableName & " set " & Request.Form("imagefield") & " = NULL "
strSQL = strSQL & " where " & AddWrappers(strKeyField) & "=" & strQuote & Replace(Request.Form("saveid"),"'","''") & strQuote
if strKeyField2<>"" then
strSQL = strSQL & " and " & AddWrappers(strKeyField2) & "=" & strQuote2 & Replace(Request.Form("saveid2"),"'","''") & strQuote2
if strKeyField3<>"" then
strSQL = strSQL & " and " & AddWrappers(strKeyField3) & "=" & strQuote3 & Replace(Request.Form("saveid3"),"'","''") & strQuote3
LogInfo(strSQL)
dbConnection.Execute strSQL
Call ReportError
message="<div class=message><<< Record was saved >>></div>"
end if
' save changes
if (Request.Form("saveid")<>"" and Request.Form("action")="edit") then
strSQL="update " & strTableName & " set "
For i =1 to Request.Form("FieldName").Count
do while Request.Form("Updatable")(i)<>"True"
i=i+1
if i>=Request.Form("FieldName").Count then exit do
loop
if i>Request.Form("FieldName").Count then exit for
nType = Request.Form("FieldType")(i)
strSQL=strSQL & AddWrappers(Request.Form("FieldName")(i)) & "="
' boolean
if nType="11" then
if Request.Form(Request.Form("FieldName")(i))="on" then
strValue="1"
else
strValue="0"
end if
else ' not boolean
if Request.Form(Request.Form("FieldName")(i))="" and ( nType=72 or nType=133 or nType=134 or nType=135 ) then
strValue = "NULL"
else
if (Request.Form("NeedQuotes")(i))="True" then
strValue= "'" & Replace( Request.Form(Request.Form("FieldName")(i)) , "'", "''") & "'"
else
if Request.Form(Request.Form("FieldName")(i))="" then
strValue="0"
else
strValue=Request.Form(Request.Form("FieldName")(i))
end if
end if
end if
end if
strSQL = strSQL & strValue
if i<>Request.Form("FieldName").Count then
strSQL = strSQL & ", "
Next
if Right(strSQL,2)= ", " then strSQL = Left(strSQL, Len(strSQL)-2) '1
strSQL = strSQL & " where " & AddWrappers(strKeyField) & "=" & strQuote & Replace(Request.Form("saveid"),"'","''") & strQuote
if strKeyField2<>"" then
strSQL = strSQL & " and " & AddWrappers(strKeyField2) & "=" & strQuote2 & Replace(Request.Form("saveid2"),"'","''") & strQuote2
if strKeyField3<>"" then _
strSQL = strSQL & " and " & AddWrappers(strKeyField3) & "=" & strQuote3 & Replace(Request.Form("saveid3"),"'","''") & strQuote3
LogInfo(strSQL)
dbConnection.Execute strSQL
Call ReportError
message="<div class=message><<< Record was saved >>></div>"
end if
if Request.form("action")="add" or Request.form("action")="added" then
sMode = "Add"
strSQL="select " & AddWrappers("Initials") & ", " & AddWrappers("Job Code") & ", " & AddWrappers("Job Number") & ", " & AddWrappers("Section") & ", " & AddWrappers("Job Description") & ", " & AddWrappers("Publication Date") & ", " & AddWrappers("Last Page") & ", " & AddWrappers("Last Page Time") & ", " & AddWrappers("Last Plate") & ", " & AddWrappers("Last Plate Time") & ", " & AddWrappers("Good") & ", " & AddWrappers("Test") & ", " & AddWrappers("Kills") & ", " & AddWrappers("Post scripts") & ", " & AddWrappers("Remakes (Waste)") & ", " & AddWrappers("Press Run") & ", " & AddWrappers("Remake Description") & ", " & AddWrappers("Remake Explanation (if applicable)") & ", " & AddWrappers("Date/Time Stamp") & ", " & AddWrappers("Date Stamp") & ", " & AddWrappers("CTP Total") & ", " & AddWrappers("Month") & ", " & AddWrappers("Year") & ", " & AddWrappers("CDT Lookup Calc") & ", " & AddWrappers("Job Number") & ", " & AddWrappers("Current Period") & ", " & AddWrappers("Trans Type") & ", " & AddWrappers("Correctable / non- correctable") & ", "
if InStr(1, LCase(strSQL), LCase(AddWrappers(strKeyField)))<1 then strSQL = "select " & AddWrappers(strKeyField) & ", " & Mid(strSQL, 8) '1
if strKeyField2<>"" and InStr(1, LCase(strSQL), LCase(AddWrappers(strKeyField2)))<1 then strSQL = "select " & AddWrappers(strKeyField2) & ", " & Mid(strSQL, 8)
if strKeyField3<>"" and InStr(1, LCase(strSQL), LCase(AddWrappers(strKeyField3)))<1 then strSQL = "select " & AddWrappers(strKeyField3) & ", " & Mid(strSQL, 8)
if Right(strSQL,2)= ", " then strSQL = Left(strSQL, Len(strSQL)-2) '1
strSQL = strSQL & " from " & strTableName & " where 1<0"
Response.Write "<h1>Add new record</h1>"
else
sMode = "Edit"
strSQL="select " & AddWrappers("Initials") & ", " & AddWrappers("Job Code") & ", " & AddWrappers("Job Number") & ", " & AddWrappers("Section") & ", " & AddWrappers("Job Description") & ", " & AddWrappers("Publication Date") & ", " & AddWrappers("Last Page") & ", " & AddWrappers("Last Page Time") & ", " & AddWrappers("Last Plate") & ", " & AddWrappers("Last Plate Time") & ", " & AddWrappers("Good") & ", " & AddWrappers("Test") & ", " & AddWrappers("Kills") & ", " & AddWrappers("Post scripts") & ", " & AddWrappers("Remakes (Waste)") & ", " & AddWrappers("Press Run") & ", " & AddWrappers("Remake Description") & ", " & AddWrappers("Remake Explanation (if applicable)") & ", " & AddWrappers("Date/Time Stamp") & ", " & AddWrappers("Date Stamp") & ", " & AddWrappers("CTP Total") & ", " & AddWrappers("Month") & ", " & AddWrappers("Year") & ", " & AddWrappers("CDT Lookup Calc") & ", " & AddWrappers("Job Number") & ", " & AddWrappers("Current Period") & ", " & AddWrappers("Trans Type") & ", " & AddWrappers("Correctable / non- correctable") & ", "
if InStr(1, LCase(strSQL), LCase(AddWrappers(strKeyField)))<1 then strSQL = "select " & AddWrappers(strKeyField) & ", " & Mid(strSQL, 8) '1
if strKeyField2<>"" and InStr(1, LCase(strSQL), LCase(AddWrappers(strKeyField2)))<1 then strSQL = "select " & AddWrappers(strKeyField2) & ", " & Mid(strSQL, 8)
if strKeyField3<>"" and InStr(1, LCase(strSQL), LCase(AddWrappers(strKeyField3)))<1 then strSQL = "select " & AddWrappers(strKeyField3) & ", " & Mid(strSQL, 8)
if Right(strSQL,2)= ", " then strSQL = Left(strSQL, Len(strSQL)-2) '1
strSQL = strSQL & " from " & strTableName
strSQL=strSQL & " where " & AddWrappers(strKeyField) & "=" & strQuote & Replace(request.form("editid"),"'","''") & strQuote
if strKeyField2<>"" then
strSQL=strSQL & " and " & AddWrappers(strKeyField2) & "=" & strQuote2 & Replace(request.form("editid2"),"'","''") & strQuote2
if strKeyField3<>"" then
strSQL=strSQL & " and " & AddWrappers(strKeyField3) & "=" & strQuote3 & Replace(request.form("editid3"),"'","''") & strQuote3
Response.Write "<h1>Edit record [" & strKeyField & ": " & request.form("editid") & "]</h1>"
end if
response.write message
response.write "<div align=left><hr width=300 noshade size=1></div>"
LogInfo(strSQL)
rs.open strSQL, dbConnection
Call ReportError
Response.write " <a href=PlateUsage_list.asp onClick=""GotoPage( " & mypage & "); return false;"">Back to list</a>"
Response.Write "<table cellpadding=2>"
%>
<form name=editform method=post action=PlateUsageedit.asp onSubmit="return validate();">
<input type=hidden id="TargetPageNumber" name="TargetPageNumber" value="<%=Request.Form("TargetPageNumber")%>" >
<input type=hidden id=SQL name=SQL value="<%=Request.Form("SQL")%>">
<input type=hidden name=saveid value="<%=HTMLEncode(request.form("editid"))%>">
<input type=hidden name=saveid2 value="<%=HTMLEncode(request.form("editid2"))%>">
<input type=hidden name=saveid3 value="<%=HTMLEncode(request.form("editid3"))%>">
<input type=hidden name=editid value="<%=HTMLEncode(request.form("editid"))%>">
<input type=hidden name=editid2 value="<%=HTMLEncode(request.form("editid2"))%>">
<input type=hidden name=editid3 value="<%=HTMLEncode(request.form("editid3"))%>">
<input type=hidden id=NeedQuotes<%=BuildFieldName(strKeyField)%> name=NeedQuotes<%=BuildFieldName(strKeyField)%>
value="<%=Request.Form("NeedQuotes" & BuildFieldName(strKeyField))%>">
<% if strKeyField2<>"" then %>
<input type=hidden id=NeedQuotes<%=BuildFieldName(strKeyField2)%> name=NeedQuotes<%=BuildFieldName(strKeyField2)%>
value="<%=Request.Form("NeedQuotes" & BuildFieldName(strKeyField2))%>">
<% end if %>
<% if strKeyField3<>"" then %>
<input type=hidden id=NeedQuotes<%=BuildFieldName(strKeyField3)%> name=NeedQuotes<%=BuildFieldName(strKeyField3)%>
value="<%=Request.Form("NeedQuotes" & BuildFieldName(strKeyField3))%>">
<% end if %>
<%
for i=0 to rs.Fields.Count-1
for j=0 to i-1
if rs.Fields.Item(i).Name=rs.Fields.Item(j).Name then
i=i+1
j=i+1
end if
next
sFieldName = rs.Fields.Item(i).Name
bSkip = false
if sFieldName = strKeyField and not bKeyFieldEditable1 then
bSkip = true
if sFieldName = strKeyField2 and not bKeyFieldEditable2 then
bSkip = true
if sFieldName = strKeyField3 and not bKeyFieldEditable3 then
bSkip = true
if i<=rs.Fields.Count-1 and not bSkip then
nType = rs.Fields.Item(i).Type
' is field updatable?
if rs.Fields.Item(i).Attributes and 4 or rs.Fields.Item(i).Attributes and 8 then
bUpdatable=true
else
bUpdatable=false
end if
if rs.EOF and rs.BOF then
strValue=""
else
strValue = GetData(rs.Fields.Item(i), FORMAT_NONE )
end if
if GetEditFormat(sFieldName) <> EDIT_FORMATHIDDEN then
Response.Write vbCRLF & "<tr><td class=shade>"
Response.Write Label(sFieldName) & " "
Response.Write "</td><td>"
end if
Response.Write "<input type=hidden name=FieldName value=""" & sFieldName & """>"
Response.Write "<input type=hidden name=FieldType value=" & CStr(nType) & ">"
if nType = 203 or nType = 8 or nType = 129 or
nType = 130 or nType = 7 or nType = 133 or
nType = 134 or nType = 135 or nType = 201 or
nType = 205 or nType = 200 or nType = 202 or _
nType = 72 then
strNeedQuotes="True"
else
strNeedQuotes="False"
end if
if bUpdatable then
strUpdatable="True"
else
strUpdatable="False"
end if
' long binary data
if (rs.Fields.Item(i).Attributes and 128) and ( nType = 204 or nType=205 ) then
strUpdatable="False"
end if
Response.Write "<input type=hidden name=NeedQuotes value=" & strNeedQuotes & ">"
Response.Write "<input type=hidden name=Updatable value=" & strUpdatable & ">"
if GetEditFormat(sFieldName) <> EDIT_FORMAT_HIDDEN then
strEncoded = HTMLEncode(strValue)
if bUpdatable then
Response.Write BuildEditControl(rs.Fields.Item(i), strValue, GetEditFormat(sFieldName), sMode)
else
Response.Write strEncoded
end if
' add icons if required
Response.Write GetLegendIcon(sFieldName, nType, i)
Response.Write "</td></tr>"
else
Response.Write BuildEditControl(rs.Fields.Item(i), strValue, GetEditFormat(sFieldName), sMode)
end if
' Response.Write rs.Fields.Item(i).Name & " " & CStr(nType) & " " & rs.Fields.Item(i).Attributes & "
"
' for j=1 to rs.Fields.Item(i).Attributes.Count
' Response.Write rs.Fields.Item(i).Properties.Item(j).Name & " = " & rs.Fields.Item(i).Properties.Item(j).Value & "
"
' next
end if
next
if strADOXTableName<>"" then set oCat=Nothing
Function GetLegendIcon(sFieldName, nType, i)
if sFieldName = strKeyField or sFieldName =strKeyField2 or sFieldName = strKeyField3 then
GetLegendIcon = GetLegendIcon & " <img src=images/iconrequired.gif>"
else
if strADOXTableName<>"" then
bCat = (oCat.Tables(strADOXTableName).Columns(rs.Fields.Item(i).Name).Properties("Jet OLEDB:Allow Zero Length") = False)
else
bCat = False
end if
if (rs.Fields.Item(i).Attributes and 96)=0 or IsRequired(sFieldName) or
((nType=202 or nType=203) and bCat) then
GetLegendIcon = GetLegendIcon & " <img src=images/icon_required.gif>"
end if
end if
End Function
%>
<!-- buttons -->
<tr height=50><td colspan=2 align=center>
<input class=button type=submit value="Save" id=submit1 name=submit1>
<input class=button type=reset value="Reset">
<input type=hidden name=action value=<%=myaction%>>
<input type=hidden name=imagefield value="">
</td></tr>
<!-- legend -->
<tr height=50><td colspan=2 align=left>
<hr width=300 noshade size=1>
<!--<img src=images/icon_key.gif> - key field -->
<img src=images/icon_required.gif> - required field
</td></tr>
</table>
</form>
<p> </p>
</body>
</html>
*****
The fields that don't show up are called.....
Customer Number and Job Number
In the display it is the 3rd field and the the 20th field. Everything writes to one table with no external links. I am only using the other tables to give me the lists of values.
If you can help I would totally be in your debt! Begging but still loving the program...
SteveD
