What's Wrong With My UPDATE Statement? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Nathan Goulette -> What's Wrong With My UPDATE Statement? (1/24/2006 13:15:45)

I am trying to run an update using the DRW and it keeps telling me that the custome query contains errors.

Can someone take a look at this and see if something obvious stands out?

This is for those who prefer the one line of code: 
UPDATE tblREQUESTS SET reqSUBMITTEDBY='::reqSUBMITTEDBY::', reqSUBMITTEDGROUP='::reqSUBMITTEDGROUP::', reqSUBMITTEDDATE=#::reqSUBMITTEDDATE::#, reqSUBMITTEDTIME=#::reqSUBMITTEDTIME::#, reqSUBMITTEDFOR='::reqSUBMITTEDFOR::', reqDUEDATE=#::reqDUEDATE::#, reqTYPE_CONTENT='::reqTYPE_CONTENT::', reqTYPE_FIREWALL='::reqTYPE_FIREWALL::', reqTYPE_IP='::reqTYPE_IP::', reqTYPE_PORT='::reqTYPE_PORT::', reqTYPE_SSL='::reqTYPE_SSL::', reqTICKETCREATED='::reqTICKETCREATED::', reqTICKETNUMBER=::reqTICKETNUMBER::, reqTICKETASSIGNED='::reqTICKETASSIGNED::', reqTICKETOWNER='::reqTICKETOWNER::', reqRECIEVED='::reqRECIEVED::', reqFORMLINK='::reqFORMLINK::', reqSTATUS='::reqSTATUS::', reqWHEN=#::reqWHEN::# WHERE reqID=::reqID::

And this is for those that like to see it all at once:
quote:

UPDATE tblREQUESTS SET reqSUBMITTEDBY='::reqSUBMITTEDBY::', reqSUBMITTEDGROUP='::reqSUBMITTEDGROUP::', reqSUBMITTEDDATE=#::reqSUBMITTEDDATE::#, reqSUBMITTEDTIME=#::reqSUBMITTEDTIME::#, reqSUBMITTEDFOR='::reqSUBMITTEDFOR::', reqDUEDATE=#::reqDUEDATE::#, reqTYPE_CONTENT='::reqTYPE_CONTENT::', reqTYPE_FIREWALL='::reqTYPE_FIREWALL::', reqTYPE_IP='::reqTYPE_IP::', reqTYPE_PORT='::reqTYPE_PORT::', reqTYPE_SSL='::reqTYPE_SSL::', reqTICKETCREATED='::reqTICKETCREATED::', reqTICKETNUMBER=::reqTICKETNUMBER::, reqTICKETASSIGNED='::reqTICKETASSIGNED::', reqTICKETOWNER='::reqTICKETOWNER::', reqRECIEVED='::reqRECIEVED::', reqFORMLINK='::reqFORMLINK::', reqSTATUS='::reqSTATUS::', reqWHEN=#::reqWHEN::# WHERE reqID=::reqID::

...Both are the same code...

Thanks
Nate



BeTheBall -> RE: What's Wrong With My UPDATE Statement? (1/24/2006 13:59:41)

What's the full text of the error message returned by the validator?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/24/2006 14:03:25)

It just pops a window that says "The custom query contains errors."


[image]local://upfiles/6916/6676EF2523964A2F8A76C35B67FD2C3F.jpg[/image]



Spooky -> RE: What's Wrong With My UPDATE Statement? (1/24/2006 14:26:34)

Are all of the field names typed correctly (or the same as in the database?)

eg : reqRECIEVED

should be :
reqRECEIVED ?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/24/2006 14:35:18)

I updated those files that get jacked with FP2K3 AND put it on a diet and just pasted the code and it does go.

However, I am experiencing some logistic issues.

The page is a details page that gets populated based on the link from the previous page that contains the criteria.

This details page is a form with several text boxes, radio buttons and check boxes. For all the radio buttons and check boxes I use IF / THEN statements to make them selected based on the contents of that record in the db.

The issue is that there are 5 check boxes that have some (or all) of the boxes checked and some not. These may or may not get updated but the ones that are not selected kick back an error when trying to update with the 'One or more form fields were empty. You should provide default values for all form fields that are used in the query.' error.

On the initial add page, the same scenerio is there where some or all may be checked and that works fine.

So why am I unable to populate these boxes with the results of a query but am unable to submit my changes if I leave unchecked boxes?

Thoughts?

Thanks
Nate




Here is the page that is populated: 
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT COUNT(*) as howMANY FROM tblUPDATES WHERE updateREQID=::reqID::"
fp_sDefault="updateREQID="
fp_sNoRecords="<tr><td colspan=1 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="connNWRDATA"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&howMANY=3&updateBY=202&updateWHEN=135&updateMEMO=203&updateREQID=3&updateID=3&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<%theCOUNT=FP_FieldVal(fp_rs,"howMANY")%>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_LCID = 1033 %>
<meta http-equiv="Content-Language" content="en-us">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Network Request Tracking</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body>
<div align="center">
	<table border="0" id="table4">
		<tr>
			<td><b><font face="Verdana" size="2" color="#000080">Network Request Tracking</font></b></td>
		</tr>
	</table>
</div>


<!--webbot bot="DatabaseRegionStart" s-columnnames="reqID,reqSUBMITTEDBY,reqSUBMITTEDGROUP,reqSUBMITTEDDATE,reqSUBMITTEDTIME,reqSUBMITTEDFOR,reqDUEDATE,reqTYPE_CONTENT,reqTYPE_FIREWALL,reqTYPE_IP,reqTYPE_PORT,reqTYPE_SSL,reqTICKETCREATED,reqTICKETNUMBER,reqTICKETASSIGNED,reqTICKETOWNER,reqSUMMARY,reqRECIEVED,reqFORMLINK,reqSTATUS,reqWHEN" s-columntypes="3,202,3,135,135,202,135,11,11,11,11,11,202,3,202,202,203,202,202,202,135" s-dataconnection="connNWRDATA" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice="reqID" s-menuvalue="reqID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="7" b-makeform="FALSE" s-recordsource="tblREQUESTS" s-displaycolumns="reqID,reqSUBMITTEDBY,reqSUBMITTEDGROUP,reqSUBMITTEDDATE,reqSUBMITTEDTIME,reqSUBMITTEDFOR,reqDUEDATE,reqTICKETCREATED,reqTICKETNUMBER,reqTICKETASSIGNED,reqTICKETOWNER,reqSUMMARY,reqRECIEVED,reqSTATUS,reqTYPE_CONTENT,reqTYPE_FIREWALL,reqTYPE_IP,reqTYPE_PORT,reqTYPE_SSL,reqFORMLINK" s-criteria="{reqID} EQ {reqID} +" s-order="[reqID] +" s-sql="SELECT * FROM tblREQUESTS WHERE (reqID =  ::reqID::) ORDER BY reqID ASC" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="reqID=" s-norecordsfound="There Are Currently No Network Requests" i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr></table>" startspan b-UseDotNET="FALSE" CurrentExt sa-InputTypes="3" b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes="3" b-WasTableFormat="FALSE" b-ReplaceDatabaseRegion="FALSE" --><!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM tblREQUESTS WHERE (reqID =  ::reqID::) ORDER BY reqID ASC"
fp_sDefault="reqID="
fp_sNoRecords="There Are Currently No Network Requests"
fp_sDataConn="connNWRDATA"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="reqID"
fp_sMenuValue="reqID"
fp_sColTypes="&reqID=3&reqSUBMITTEDBY=202&reqSUBMITTEDGROUP=3&reqSUBMITTEDDATE=135&reqSUBMITTEDTIME=135&reqSUBMITTEDFOR=202&reqDUEDATE=135&reqTYPE_CONTENT=11&reqTYPE_FIREWALL=11&reqTYPE_IP=11&reqTYPE_PORT=11&reqTYPE_SSL=11&reqTICKETCREATED=202&reqTICKETNUMBER=3&reqTICKETASSIGNED=202&reqTICKETOWNER=202&reqSUMMARY=203&reqRECIEVED=202&reqFORMLINK=202&reqSTATUS=202&reqWHEN=135&"
fp_iDisplayCols=20
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="20863" --><form METHOD="POST" action="reqDETAIL_updatedetail.asp?reqID=<%=FP_FieldURL(fp_rs,"reqID")%>">
	<!--webbot bot="PurpleText" preview="Set this form's properties so it submits user input to the appropriate page." --><div align="center">
		<table BORDER="0">
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			ID:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqID" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqID")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Submitted By:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqSUBMITTEDBY" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqSUBMITTEDBY")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Submitter's Group:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqSUBMITTEDGROUP" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqSUBMITTEDGROUP")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Submitted Date:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqSUBMITTEDDATE" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqSUBMITTEDDATE")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Submitted Time:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqSUBMITTEDTIME" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqSUBMITTEDTIME")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Submitted On Behalf Of:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqSUBMITTEDFOR" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqSUBMITTEDFOR")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Due Date:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqDUEDATE" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqDUEDATE")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Type:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
<table border="0" id="table5" cellspacing="0" cellpadding="0">
							<tr>
								<td>
								<font color="#000080" size="1" face="Verdana">
								<input type="checkbox" name="reqTYPE_CONTENT" value="True" <%If FP_Field(fp_rs,"reqTYPE_CONTENT") ="True" then response.write " checked"%>></font></td>
								<td width="15"> </td>
								<td>
								<font face="Verdana" size="1" color="#000080">Content</font></td>
								<td width="35"> </td>
								<td width="150"><i>
								<font face="Verdana" size="1" color="#808080">
								Content Requests: i.e. VIP</font></i></td>
							</tr>
							<tr>
								<td>
								<font color="#000080" size="1" face="Verdana">
								<input type="checkbox" name="reqTYPE_FIREWALL" value="True" <%If FP_Field(fp_rs,"reqTYPE_FIREWALL") ="True" then response.write " checked"%>></font></td>
								<td width="15"> </td>
								<td>
								<font face="Verdana" size="1" color="#000080">Firewall</font></td>
								<td width="35"> </td>
								<td width="150"><i>
								<font face="Verdana" size="1" color="#808080">Firewall Requests</font></i></td>
							</tr>
							<tr>
								<td>
								<font color="#000080" size="1" face="Verdana">
								<input type="checkbox" name="reqTYPE_IP" value="True" <%If FP_Field(fp_rs,"reqTYPE_IP") ="True" then response.write " checked"%>></font></td>
								<td width="15"> </td>
								<td>
								<font face="Verdana" size="1" color="#000080">IP</font></td>
								<td width="35"> </td>
								<td width="150"><i>
								<font face="Verdana" size="1" color="#808080">IP Requests</font></i></td>
							</tr>
							<tr>
								<td>
								<font color="#000080" size="1" face="Verdana">
								<input type="checkbox" name="reqTYPE_PORT" value="True" <%If FP_Field(fp_rs,"reqTYPE_PORT") ="True" then response.write " checked"%>></font></td>
								<td width="15"> </td>
								<td>
								<font face="Verdana" size="1" color="#000080">Port</font></td>
								<td width="35"> </td>
								<td width="150"><i>
								<font face="Verdana" size="1" color="#808080">Port Requests</font></i></td>
							</tr>
							<tr>
								<td>
								<font color="#000080" size="1" face="Verdana">
								<input type="checkbox" name="reqTYPE_SSL" value="True" <%If FP_Field(fp_rs,"reqTYPE_SSL") ="True" then response.write " checked"%>></font></td>
								<td width="15"> </td>
								<td>
								<font face="Verdana" size="1" color="#000080">SSL</font></td>
								<td width="35"> </td>
								<td width="150"><i>
								<font face="Verdana" size="1" color="#808080">SSL Requests</font></i></td>
							</tr>
						</table>
						</td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Ticket Created:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
<table border="0" id="table5" cellspacing="0" cellpadding="0">
				<tr>
					<td width="30" align="right">
					<font face="Verdana" size="1" color="#000080">Yes</font></td>
					<td width="30"><font color="#000080">
					<input type="radio" value="Yes" name="reqTICKETCREATED" <%If FP_Field(fp_rs,"reqTICKETCREATED") ="Yes" then response.write " checked"%>></font></td>
					<td width="30"> </td>
					<td width="30" align="right">
					<font face="Verdana" size="1" color="#000080">No</font></td>
					<td width="30"><font color="#000080">
					<input type="radio" value="No" name="reqTICKETCREATED" <%If FP_Field(fp_rs,"reqTICKETCREATED") ="No" then response.write " checked"%>></font></td>
				</tr>
			</table>
			</td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Ticket Number:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqTICKETNUMBER" SIZE="40" VALUE="
<%IF FP_FieldHTML(fp_rs,"reqTICKETNUMBER") ="0" THEN%>
<%Response.Write ""%>
<%END IF%>

<%IF FP_FieldHTML(fp_rs,"reqTICKETNUMBER") <>"0" THEN%>
<%=FP_FieldHTML(fp_rs,"reqTICKETNUMBER")%>
<%END IF%>

" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Ticket Assigned:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
<table border="0" id="table5" cellspacing="0" cellpadding="0">
				<tr>
					<td width="30" align="right">
					<font face="Verdana" size="1" color="#000080">Yes</font></td>
					<td width="30"><font color="#000080">
					<input type="radio" value="Yes" name="reqTICKETASSIGNED" <%If FP_Field(fp_rs,"reqTICKETASSIGNED") ="Yes" then response.write " checked"%>></font></td>
					<td width="30"> </td>
					<td width="30" align="right">
					<font face="Verdana" size="1" color="#000080">No</font></td>
					<td width="30"><font color="#000080">
					<input type="radio" value="No" name="reqTICKETASSIGNED" <%If FP_Field(fp_rs,"reqTICKETASSIGNED") ="No" then response.write " checked"%>></font></td>
				</tr>
			</table>
			</td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Ticket Owner:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<input TYPE="TEXT" NAME="reqTICKETOWNER" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"reqTICKETOWNER")%>" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Summary:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
			<textarea rows="11" name="reqSUMMARY" cols="39" style="font-family: Verdana; font-size: 8pt; color: #000080"><%=FP_FieldHTML(fp_rs,"reqSUMMARY")%></textarea></td>
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Request Form Received:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
<table border="0" id="table5" cellspacing="0" cellpadding="0">
				<tr>
					<td width="30" align="right">
					<font face="Verdana" size="1" color="#000080">Yes</font></td>
					<td width="30"><font color="#000080">
					<input type="radio" value="Yes" name="reqRECIEVED" <%If FP_Field(fp_rs,"reqRECIEVED") ="Yes" then response.write " checked"%>></font></td>
					<td width="30"> </td>
					<td width="30" align="right">
					<font face="Verdana" size="1" color="#000080">No</font></td>
					<td width="30"><font color="#000080">
					<input type="radio" value="No" name="reqRECIEVED" <%If FP_Field(fp_rs,"reqRECIEVED") ="No" then response.write " checked"%>></font></td>
				</tr>
			</table>


			</td>
		</tr>
		<tr>
			<!-- BEGIN HISTORY BUTTON -->

<% IF theCOUNT ="0" THEN %>

			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			View History:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
<table border="0" width="100%" id="table6" cellspacing="0" cellpadding="0">
	<tr>
		<td>
<img border="0" src="../images/noform.jpg"></td>
	</tr>
</table>
			</td>
		</tr>

<%END IF%>

<% IF theCOUNT <>"0" THEN %>

			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			View History:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
<INPUT TYPE=button VALUE="View History" onClick="window.open('../Pages/reqHISTORY.asp?updateREQID=<%=FP_FieldURL(fp_rs,"reqID")%>')"  style="font-family: Verdana; font-size: 8pt; color: #0000FF"></td>
		</tr>

<%END IF%>

<!-- END HISTORY BUTTON -->
		</tr>
		<tr>
			<td align="right"><b><font face="Verdana" size="1" color="#000080">
			Current Status:</font></b></td>
			<td align="right" width="15"> </td>
			<td>
<select size="1" name="reqSTATUS" style="font-family: Verdana; font-size: 8pt; color: #000080">
<option <%If FP_Field(fp_rs,"reqSTATUS") ="New" then response.write " selected"%>>New</option>
<option <%If FP_Field(fp_rs,"reqSTATUS") ="Open" then response.write " selected"%>>Open</option>
<option <%If FP_Field(fp_rs,"reqSTATUS") ="Pending" then response.write " selected"%>>Pending</option>
<option <%If FP_Field(fp_rs,"reqSTATUS") ="Verify" then response.write " selected"%>>Verify</option>
<option <%If FP_Field(fp_rs,"reqSTATUS") ="Closed" then response.write " selected"%>>Closed</option>
</select></td>
		</tr>
		<tr>
			<td COLSPAN="3"> </td>
		</tr>
		<tr>
			<td COLSPAN="3">
			<p align="center">
			<input type="submit" value="[ Update Request ]" name="B1" style="font-family: Verdana; font-size: 8pt; color: #000080"></td>
		</tr>
	</table>
	</div>
</form>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the end of a Database Results region.</font></td></tr></table>" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" -->


</body>

</html>

Here is the code of the update page: 
<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Network Request Tracking</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body>
<div align="center">
	<table border="0" id="table4">
		<tr>
			<td><b><font face="Verdana" size="2" color="#000080">Network Request Tracking</font></b></td>
		</tr>
	</table>
</div>


<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="UPDATE tblREQUESTS SET reqSUBMITTEDBY='::reqSUBMITTEDBY::', reqSUBMITTEDGROUP='::reqSUBMITTEDGROUP::', reqSUBMITTEDDATE=#::reqSUBMITTEDDATE::#, reqSUBMITTEDTIME=#::reqSUBMITTEDTIME::#, reqSUBMITTEDFOR='::reqSUBMITTEDFOR::', reqDUEDATE=#::reqDUEDATE::#, reqTYPE_CONTENT='::reqTYPE_CONTENT::', reqTYPE_FIREWALL='::reqTYPE_FIREWALL::', reqTYPE_IP='::reqTYPE_IP::', reqTYPE_PORT='::reqTYPE_PORT::', reqTYPE_SSL='::reqTYPE_SSL::', reqTICKETCREATED='::reqTICKETCREATED::', reqTICKETNUMBER=::reqTICKETNUMBER::, reqTICKETASSIGNED='::reqTICKETASSIGNED::', reqTICKETOWNER='::reqTICKETOWNER::', reqRECIEVED='::reqRECIEVED::', reqFORMLINK='::reqFORMLINK::', reqSTATUS='::reqSTATUS::', reqWHEN=#::reqWHEN::# WHERE reqID=::reqID::"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=1 align=""LEFT"" width=""100%"">There Are Currently No Network Requests</td></tr>"
fp_sDataConn="connNWRDATA"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&reqID=3&reqSUBMITTEDBY=202&reqSUBMITTEDGROUP=202&reqSUBMITTEDDATE=135&reqSUBMITTEDTIME=135&reqSUBMITTEDFOR=202&reqDUEDATE=135&reqTYPE_CONTENT=11&reqTYPE_FIREWALL=11&reqTYPE_IP=11&reqTYPE_PORT=11&reqTYPE_SSL=11&reqTICKETCREATED=202&reqTICKETNUMBER=3&reqTICKETASSIGNED=202&reqTICKETOWNER=202&reqSUMMARY=203&reqRECIEVED=202&reqFORMLINK=202&reqSTATUS=202&reqWHEN=135&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--#include file="../_fpclass/fpdbrgn2.inc"-->


</body>

</html>



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/24/2006 14:37:01)

quote:

ORIGINAL: Spooky

Are all of the field names typed correctly (or the same as in the database?)

eg : reqRECIEVED

should be :
reqRECEIVED ?
LOL, Unfortunetally I ALWAYS spell that one incorrectly. It is spelled the same way in the DB.



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/25/2006 14:20:11)

Any suggestions as to why this is expecting all of the check boxes to be checked before the update will actually occur?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/25/2006 19:27:32)

I put this into my update page:
<%
response.write fp_sQry
response.end
Conn.execute(fp_sQry)
%>

Here is the result: 
UPDATE tblREQUESTS SET reqSUBMITTEDBY='Ether Bunny', reqSUBMITTEDGROUP='Cotton Tail Posse', reqSUBMITTEDDATE=#1/24/2006#, reqSUBMITTEDTIME=#12:35:19 PM#, reqSUBMITTEDFOR='Santa Clause', reqDUEDATE=#1/25/2006#, reqTYPE_CONTENT='', reqTYPE_FIREWALL='True', reqTYPE_IP='', reqTYPE_PORT='', reqTYPE_SSL='', reqTICKETCREATED='Yes', reqTICKETNUMBER=E00001, reqTICKETASSIGNED='Yes', reqTICKETOWNER='Father Time', reqRECIEVED='Yes', reqFORMLINK='', reqSTATUS='Closed', reqWHEN=#1/24/2006 12:35:44 PM# WHERE reqID=5



Here is the section of the code that populates the check boxes as selected if the db field is True: 
<table border="0" id="table5" cellspacing="0" cellpadding="0">
<tr>
<td>
<font color="#000080" size="1" face="Verdana">
<input type="checkbox" name="reqTYPE_CONTENT" value="True" <%If FP_Field(fp_rs,"reqTYPE_CONTENT") ="True" then response.write " checked"%>></font></td>
<td width="15"> </td>
<td>
<font face="Verdana" size="1" color="#000080">Content</font></td>
<td width="35"> </td>
<td width="150"><i>
<font face="Verdana" size="1" color="#808080">
Content Requests: i.e. VIP</font></i></td>
</tr>
<tr>
<td>
<font color="#000080" size="1" face="Verdana">
<input type="checkbox" name="reqTYPE_FIREWALL" value="True" <%If FP_Field(fp_rs,"reqTYPE_FIREWALL") ="True" then response.write " checked"%>></font></td>
<td width="15"> </td>
<td>
<font face="Verdana" size="1" color="#000080">Firewall</font></td>
<td width="35"> </td>
<td width="150"><i>
<font face="Verdana" size="1" color="#808080">Firewall Requests</font></i></td>
</tr>
<tr>
<td>
<font color="#000080" size="1" face="Verdana">
<input type="checkbox" name="reqTYPE_IP" value="True" <%If FP_Field(fp_rs,"reqTYPE_IP") ="True" then response.write " checked"%>></font></td>
<td width="15"> </td>
<td>
<font face="Verdana" size="1" color="#000080">IP</font></td>
<td width="35"> </td>
<td width="150"><i>
<font face="Verdana" size="1" color="#808080">IP Requests</font></i></td>
</tr>
<tr>
<td>
<font color="#000080" size="1" face="Verdana">
<input type="checkbox" name="reqTYPE_PORT" value="True" <%If FP_Field(fp_rs,"reqTYPE_PORT") ="True" then response.write " checked"%>></font></td>
<td width="15"> </td>
<td>
<font face="Verdana" size="1" color="#000080">Port</font></td>
<td width="35"> </td>
<td width="150"><i>
<font face="Verdana" size="1" color="#808080">Port Requests</font></i></td>
</tr>
<tr>
<td>
<font color="#000080" size="1" face="Verdana">
<input type="checkbox" name="reqTYPE_SSL" value="True" <%If FP_Field(fp_rs,"reqTYPE_SSL") ="True" then response.write " checked"%>></font></td>
<td width="15"> </td>
<td>
<font face="Verdana" size="1" color="#000080">SSL</font></td>
<td width="35"> </td>
<td width="150"><i>
<font face="Verdana" size="1" color="#808080">SSL Requests</font></i></td>
</tr>
</table>


I guess that there are other areas that are blank too [In the above results: reqFORMLINK=''] that may not get updated and remain blank.

So with that said, how can I get the update code exclude those values that are null .

Is that even possible? If not, any other suggestions?



BeTheBall -> RE: What's Wrong With My UPDATE Statement? (1/25/2006 22:48:18)

It appears all the blank fields are text fields so go into Design View in Access and for all text fields, set, "Allow Zero Length" to yes.



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 11:42:31)

Thanks Duane. I had actually found a post last night that suggested that for another user and I did check that for the TEXT fields and they were already set to that as default.

However, the biggest issue is with the checkbox inputs. When the record loads the checkboxes are (or not) populated with this code for each one:

<input type="checkbox" name="reqTYPE_PORT" value="True" <%If FP_Field(fp_rs,"reqTYPE_PORT") ="True" then response.write " checked"%>

So by design, there may be some check boxes that will not be checked when the update is submitted and that is what is erroring out.

Not sure if this is of relevance but the field type for the check boxes is YES/NO and the format is TRUE/FALSE.

Any addition



rdouglass -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 12:14:15)

quote:

<%If FP_Field(fp_rs,"reqTYPE_PORT") ="True" then response.write " checked"%>


Yes/No fields in my experience you'll have better luck with this:

<%If FP_Field(fp_rs,"reqTYPE_PORT") = -1 then response.write " checked"%>



drogers -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 12:41:24)

Also, do you have a form handler that cleans up sloppy user input (trim leading and trailing spaces, replace single quotes, that sort of thing)? I often use the form handler to check each form field's input for a number of different things, including it being empty. This lets me side step a lot of errors that users can create.




rdouglass -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 13:18:45)

quote:

reqTYPE_FIREWALL='True',


The form handler suggestion is well taken but since we're dealing with a Yes/No field, remove those text delimiters so the UPDATE statement part for the checkbox looks like this:

reqTYPE_FIREWALL=True,

(No apostrophes.)



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 17:56:49)

quote:

Original: rdouglass

<%If FP_Field(fp_rs,"reqTYPE_PORT") = -1 then response.write " checked"%>


Why the -1? The results are only going to be True or False. If the db record has True, then it will be checked. If it is False, it is unchecked.

I read that in CHECKBOX input type, that if the box is checked it will pass the TRUE value and if it is not checked, it will pass the False value resulting in the MSACCESS field being check or not checked accordingly. On the original seubmitting form, that IS the case but on this form it is not.

quote:

Original: rdouglass

reqTYPE_FIREWALL=True,

I also tried this in my UPDATE statement and I got this: 
UPDATE tblREQUESTS SET reqSUBMITTEDBY='Ether Bunny', reqSUBMITTEDGROUP='Cotton Tail Posse', reqSUBMITTEDDATE=#1/24/2006#, reqSUBMITTEDTIME=#12:35:19 PM#, reqSUBMITTEDFOR='Santa Clause', reqDUEDATE=#1/25/2006#, reqTYPE_CONTENT=, reqTYPE_FIREWALL='True', reqTYPE_IP=, reqTYPE_PORT=, reqTYPE_SSL=, reqTICKETCREATED='Yes', reqTICKETNUMBER=E00001, reqTICKETASSIGNED='Yes', reqTICKETOWNER='Father Time', reqRECIEVED='Yes', reqFORMLINK='', reqSTATUS='Closed', reqWHEN=#1/24/2006 12:35:44 PM# WHERE reqID=5


Instead of this: 
UPDATE tblREQUESTS SET reqSUBMITTEDBY='Ether Bunny', reqSUBMITTEDGROUP='Cotton Tail Posse', reqSUBMITTEDDATE=#1/24/2006#, reqSUBMITTEDTIME=#12:35:19 PM#, reqSUBMITTEDFOR='Santa Clause', reqDUEDATE=#1/25/2006#, reqTYPE_CONTENT='', reqTYPE_FIREWALL='True', reqTYPE_IP='', reqTYPE_PORT='', reqTYPE_SSL='', reqTICKETCREATED='Yes', reqTICKETNUMBER=E00001, reqTICKETASSIGNED='Yes', reqTICKETOWNER='Father Time', reqRECIEVED='Yes', reqFORMLINK='', reqSTATUS='Closed', reqWHEN=#1/24/2006 12:35:44 PM# WHERE reqID=5




quote:

Original: drogers

Also, do you have a form handler that cleans up sloppy user input (trim leading and trailing spaces, replace single quotes, that sort of thing)? I often use the form handler to check each form field's input for a number of different things, including it being empty. This lets me side step a lot of errors that users can create.

I see where this would be helpful with more text fields and I appreciate your ehlpfulness but it really isnt the text fields I am having the issue with. I need to get the unchecked fields to not get passed to the UPDATE as empty fields.



BeTheBall -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 19:02:59)

True/False fields are actually boolean fields. -1 signifies true and 0 signifies false. Since boolean values are in reality numeric, you must, as Roger suggested, remove the single quotes from around any value being sent to a true/false field.

The root of your problem relates to the boolean fields. You cannot insert a blank value into a boolean field. It is not how you suggest above in that if you don't provide a "True", it defaults to "False". Therefore, you will want to create variables to use to insert into the boolean fields. For example, instead of having myBoolean=::myBoolean::. Create variables like this:

If Request.Form("yourCheckBoxField") = WhatEverYourValueIs Then
myBool = -1
Else
myBool = 0
End If

You would have to create similar code for each checkbox. Then, in your SQL, instead of myBoolean=::myBoolean::, you would have, myBoolean="& myBool &"

Hope that helps.



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 19:48:11)

Duane, YOU DA MAN!

Thank you very much for explaining that to me. I did not know that about the TRUE / FALSE and its Boolean nature.

That part works AWESOME.

Contrary to my previous statement, I am still having issues with some empty text fields. I do have the "ALLOW ZERO LENGTH" option set to yes but the update is still wanting ALL the fieds to be populated.

Any suggestions?




Thanks again for everyones input. It is this kind of process that helps me learn things. Knowing how to do something is one thing but understanding why it is that it does it the way that it does it (whew!) is totally value add for me.



BeTheBall -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 20:13:53)

Are the fields that are giving you trouble by any chance memo fields in the db?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 21:52:07)

No, it is a text field. There is a memo field but that isnt an issue as there will ALWAYS be something in that one.



BeTheBall -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 21:53:18)

Which field is the problematic one or can you tell for sure?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 22:50:38)

It would effect any field that wasnt filled in.

Basically there are a couple of radio buttons for things like "TICKET CREATED?" and if it is yes, then we put the ticket number in the next field. If it is no, then we leave it blank. The same for "TICKET ASSIGNED": if yes, we put who it is assigned to and if no it is blank.



BeTheBall -> RE: What's Wrong With My UPDATE Statement? (1/26/2006 23:39:39)

What is the current error message?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/27/2006 11:58:33)

Database Results Error
Description: Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for all form fields that are used in the query.



BeTheBall -> RE: What's Wrong With My UPDATE Statement? (1/27/2006 12:18:43)


quote:

ORIGINAL: Nathan Goulette

Database Results Error
Description: Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for all form fields that are used in the query.


Although it refers to the empty form fields, in my experience, that may not be the problem. Having fixed the checkbox issue, what is now your result when you response.write the SQL?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/27/2006 15:42:52) 

UPDATE tblREQUESTS SET reqSUBMITTEDBY='Ether Bunny', reqSUBMITTEDGROUP='Cotton Tail Posse', reqSUBMITTEDDATE=#1/24/2006#, reqSUBMITTEDTIME=#12:35:19 PM#, reqSUBMITTEDFOR='Santa Clause', reqDUEDATE=#1/25/2006#, reqTYPE_CONTENT=0 , reqTYPE_FIREWALL=-1, reqTYPE_IP=0, reqTYPE_PORT=0, reqTYPE_SSL=0, reqTICKETCREATED='Yes', reqTICKETNUMBER=, reqTICKETASSIGNED='Yes', reqTICKETOWNER='', reqRECIEVED='Yes', reqFORMLINK='', reqSTATUS='Closed', reqWHEN=#1/24/2006 12:35:44 PM# WHERE reqID=5


The following fields are empty in this example:
reqTICKETNUMBER
reqTICKETOWNER
reqFORMLINK



Spooky -> RE: What's Wrong With My UPDATE Statement? (1/27/2006 19:15:34)

Does it fail if you eliminate the bool fields from the update statement?



Nathan Goulette -> RE: What's Wrong With My UPDATE Statement? (1/30/2006 14:37:54)

Yes, it does still fail.

I have totally eliminated the BOOL fields and simply made them Text using YES and NO with RADIO FOrm Fields.

It doesn't seem like this is possible with conventional methods so I am going to resort to non-conventional methods

I am also going to populate the fields with NULL for the updates. And if the text NULL is present when the details are viewed, it will be replaced with nothing (ie. "")

Thanks for your help and if there are any other suggestions, I will be more than happy to give them a whirl.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.140625