help with an UPDATE ASP page

jaberwocky · #1 (**permalink**) 02-25-04, 18:08

I am trying to run an update.asp page, but keep getting "Syntax error in UPDATE statement.." which is my SQL line strSQL = "UPDATE AttendanceTbl SET " & fieldData

here is some of my code. I am updating textboxes, memo fields and a single checkbox. I am not sure if the [myArray]=split... line is the best way to handle a single checkbox, but i am adapting it from another update.asp page i have. Should i just move the "justified" checkbox into my fieldData= ... line. Could this be whats causing my syntax error?

Thanks

Code:

<%@ Language=VBScript %>
<% 
Dim adoCon, rstemp, myDSN 
Dim strSQL, strSQL1, fieldData 
Dim lngRecordNo 
lngRecordNo = CLng(Request.Form("ID"))
fieldData = "FullName = " & Request.Form("FullName") & ", newdate = #" & Request.Form("newdate") & "#, reason = " & Request.Form("reason") & "  WHERE ID=" & lngRecordNo 

myArray = split("justified",",") 
FOR i = 0 to uBound(myArray) 
fieldData = fieldData & ", " & myArray(i) & " = " 

IF Request.Form(myArray(i)) = "ON" THEN 
fieldData = fieldData & "true" 
ELSE 
fieldData = fieldData & "false" 
END IF 

NEXT 
fieldData = fieldData & ", Comments = '" & Request.Form("Comments") & "' WHERE ID=" & lngRecordNo 
strSQL = "UPDATE AttendanceTbl SET " & fieldData 
strSQL1 = "UPDATE HistoricalAttendanceTbl SET " & fieldData 

Response.write(strSQL) 
myDSN = "DSN=attendance2004" 

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open myDSN
adoCon.execute(strSQL) 
adoCon.execute(strSQL1) 
adoCon.close 
%>

jaberwocky · #2 (**permalink**) 02-25-04, 19:34

Ok, i finally see 2 errors in my code: missing the apostrphes in my fieldData lines and having the WHERE line at the end of my first fieldData line, but i am still getting an error:
Unterminated string constant on line 10, which goes back to my Array.

Is there any better way to handle a single checkbox than using Array ??

Bullschmidt · #3 (**permalink**) 03-02-04, 02:01

Well here's a checkbox page I did for myself for reference that uses a few proprietary functions and and include but still it might give you some ideas (definitely no arrays):

<% Option Explicit
Response.Expires = -1000 ' Make browser not cache pg.
Response.Buffer = True ' Buffer content so Response.Redirect will work.
%>

<% ' Dim var.
Dim Color
Dim Width

Color = Request.Form("Color")
Width = Request.Form("Width")

' Set default Color.
If Color = "" Then
Color = "Red"
End If

' Set default Width.
If Width = "" Then
Width = "Narrow"
End If

'!!!
Response.Write "<br>"
Response.Write "Color: " & Color & "<br><br>"
Response.Write "Width: " & Width & "<br><br>"
Response.Flush
%>
<html>
<head>
<meta name="robots" content="noindex,nofollow">
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<meta http-equiv="page-enter" content="revealTrans(duration=<%= mintTransitionSec %>,transition=<%= mintTransitionNum %>)">
<link rel="stylesheet" type="text/css" href="<%= mstrSiteMain %>/~incl/style.css">
<script type="text/javascript" src="<%= mstrSiteMain %>/~incl/jpsutility.js"></script>
<script type="text/javascript" src="<%= mstrSiteMain %>/~incl/misc.js"></script>
<script type="text/javascript">
</script>
<title>TEST</title>
</head>

<body>
<form id="frmMain" name="frmMain" action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="post">
Color - Uses jpsvbBoolToChecked():<br>
<% ' *** Color - Checkbox Buttons %>
<% ' *** Red - Checkbox Button %>
<input type="checkbox" name="Color" <%= jpsvbBoolToChecked(InStr(Color, "Red") > 0) %> value="Red" onclick="jpsjsSetUnsaved(true);">
Red<br>
<% ' *** Green - Checkbox Button %>
<input type="checkbox" name="Color" <%= jpsvbBoolToChecked(InStr(Color, "Green") > 0) %> value="Green" onclick="jpsjsSetUnsaved(true);">
Green<br>
<% ' *** Blue - Checkbox Button %>
<input type="checkbox" name="Color" <%= jpsvbBoolToChecked(InStr(Color, "Blue") > 0) %> value="Blue" onclick="jpsjsSetUnsaved(true);">
Blue<br>
<br>
Width - Does Not Use jpsvbBoolToChecked():<br>
<% ' *** Width - Checkbox Buttons %>
<% ' *** Narrow - Checkbox Button %>
<input type="checkbox" name="Width" <% If InStr(Width, "Narrow") > 0 Then Response.Write "checked" %> value="Narrow" onclick="jpsjsSetUnsaved(true);">
Narrow<br>
<% ' *** Wide - Radio Button %>
<input type="checkbox" name="Width" <% If InStr(Width, "Wide") > 0 Then Response.Write "checked" %> value="Wide" onclick="jpsjsSetUnsaved(true);">
Wide<br>
<br>
<% ' *** btnSave %>
<input type="submit" name="btnSave" value="Save">
</form>
</body>
</html>

jaberwocky · #4 (**permalink**) 03-02-04, 11:22

thanks
i actually found a 3rd error on my page, which fixed the whole thing and now its working fine. But i appreciate the code example

I was missing the ending quotes on my sql line (I think ..lol, dont remember now)

Internet Services

Web Development

Digital Marketing

Consumer Tech