Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 Slippery Error		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Carefree
Advanced Member

Philippines
4224 Posts
Posted - 05 October 2010 :  10:35:37  Show Profile  Reply with Quote
Here's a spot of code for a registrar's office, there's a syntax error in the SQL format for both update/add (all fields are text). Anyone see the error?


<!--#INCLUDE FILE="config.asp" -->
<!--#INCLUDE FILE="sha256.asp" -->
<!--#INCLUDE FILE="top.asp" -->
<%
dim intID
intID=chkString(Request.QueryString("id"),"SqlString")
if (not Session("strRegistrar") = "Success" and not Session("aLev")=1) then 
	Response.Redirect "default.asp"
end if
strRCode = trim(uCase(ChkString(Request.Form("RCode"), "SqlString")))
if Request.Form("Mode") > "" and Request.Form("Mode") <> "Search" then
	strRNameL = trim(ChkString(Request.Form("RNameL"), "SqlString"))
	strRNameF = trim(ChkString(Request.Form("RNameF"), "SqlString"))
	strRNameM = trim(ChkString(Request.Form("RNameM"), "SqlString"))
	strRSSAN = trim(ChkString(Request.Form("RSSAN"), "SqlString"))
	if not isNumeric(strRSSAN) or len(strRSSAN) < 9 then
		strError = "<br>SSAN not in proper format (i.e., 123456789)" & vbNewLine
	end if
	strRPass = trim(ChkString(Request.Form("RPass"), "SqlString"))
	encRPass = sha256("" & strRPass)
	if strError > "" then
		Response.Write	strError & "<br>Go <a href=""JavaScript:history.go(-1)"">back</a> to try again.</b></center>" & vbNewLine
		Response.End
	end if
end if
if Request.Form("Mode") = "Add" then
	strSql = "INSERT INTO REGISTRARS SET (Reg_NameL, Reg_NameF, Reg_NameM, Reg_SSAN, Reg_Code, Reg_PW) VALUES ('" & strRNameL & "','" &  strRNameF & "','" &  strRNameM & "','" &  strRSSAN & "','" &  strRCode & "','" &  strRPass & "')"
	my_Conn.Execute(strSql)
elseif Request.Form("Mode") = "Edit" then
	strSSAN=ChkString(Request.Form("SSAN"), "SqlString")
	if not isNumeric(strSSN) then
		Response.Write	"SSAN contains an improper character or is blank, go <a href=""JavaScript:history.go(-1)"">back</a> to try again.</b></center>" & vbNewLine
		Response.End	
	end if
	strSql = "UPDATE REGISTRARS SET (Reg_NameL, Reg_NameF, Reg_NameM, Reg_SSAN, Reg_Code, Reg_PW) VALUES ('" & strRNameL & "','" &  strRNameF & "','" &  strRNameM & "','" &  strRSSAN & "','" &  strRCode & "','" &  strRPass & "') WHERE Reg_Code = '" & strRCode & "'"
	my_Conn.Execute(strSql)
elseif Request.Form("Mode") = "Search" then
	strSql = "SELECT * FROM REGISTRARS WHERE Reg_Code='" & strRCode & "'"
	set rsRegis = my_Conn.Execute(strSql)
	if rsRegis.EOF then
		Response.Write	"<table align=""center"" width=""610"" border=""1"" style=""border-collapse:collapse;"">" & vbNewLine & _
			"	<tr valign=""middle"" height=""40"">" & vbNewLine & _
			"		<td align=""center"" width=""100%"" bgcolor=""cyan"">" & vbNewLine & _
			"			<font face=""lucida console"" size=""5"" color=""navy""><b>Search Results</b>" & vbNewLine & _
			"			</font>" & vbNewLine & _
			"		</td>" & vbNewLine & _
			"	</tr>" & vbNewLine & _
			"	<tr valign=""middle"" height=""30"">" & vbNewLine & _			
			"		<td align=""center"" width=""100%"">" & vbNewLine & _
			"			<font face=""lucida console"" size=""3"" color=""navy"">Registrar record not found, go <a href=""JavaScript:history.go(-1)"">back</a> to try again.</b>" & vbNewLine & _
			"			</font>" & vbNewLine & _
			"		</td>" & vbNewLine & _
			"	</tr>" & vbNewLine & _
			"</table>" & vbNewLine
		set rsRegis=Nothing
		Response.End
	else
		strRNameL=rsRegis("Reg_NameL")
		strRNameF=rsRegis("Reg_NameF")
		strRNameM=rsRegis("Reg_NameM")
		strRSSAN=rsRegis("Reg_SSAN")
		strRCode=rsRegis("Reg_Code")
		strRPass=rsRegis("Reg_PW")
		rsRegis.Close
		set rsRegis=Nothing
	end if
end if
Response.Write	"<table height=""80%"" border=""0"" padding=""0"" spacing=""0"" width=""100%"">" & vbNewLine & _
	"	<tr valign=""middle"">" & vbNewLine & _
	"		<td align=""center"" width=""100%"">" & vbNewLine & _
	"<form method=""post"" name=""voter"" action=""admin.asp?id="&intID&""">" & vbNewLine & _
	"	<table align=""center"" width=""610"" border=""1"" border-style=collapse:collapse; spacing=""0"" padding=""4"">" & vbNewLine & _
	"		<tr valign=""middle"" height=""50"" bgcolor=""cyan"">" & vbNewLine & _
	"			<td align=""center"" colspan=""2"" width=""100%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""4"" color=""navy""><b>Registrar Administration</b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td align=""right"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""><b>Last Name: </b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td align=""left"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""> " & vbNewLine & _
	"					<input type=""text"" name=""RNameL"" size=""50"" maxlength=""50"" value=""" & trim(strRNameL) & """>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td align=""right"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""><b>First Name: </b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td align=""left"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""> " & vbNewLine & _
	"					<input type=""text"" name=""RNameF"" size=""50"" maxlength=""50"" value=""" & trim(strRNameF) & """>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td align=""right"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""><b>MI: </b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td align=""left"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""> " & vbNewLine & _
	"					<input type=""text"" name=""RNameM"" size=""50"" maxlength=""50"" value=""" & trim(strRNameM) & """>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td align=""right"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""><b>SSAN (123456789): </b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td align=""left"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""> " & vbNewLine & _
	"					<input type=""text"" name=""RSSAN"" size=""50"" maxlength=""9"" value=""" & trim(strRSSAN) & """>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td align=""right"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""><b>Code (max 6): </b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td align=""left"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""> " & vbNewLine & _
	"					<input type=""text"" name=""RCode"" size=""50"" maxlength=""6"" value=""" & trim(strRCode) & """>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"">" & vbNewLine & _
	"			<td align=""right"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""><b>Password (max 20): </b>" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"			<td align=""left"" width=""50%"">" & vbNewLine & _
	"				<font face=""lucida console"" size=""2"" color=""navy""> " & vbNewLine & _
	"					<input type=""password"" name=""RPass"" size=""50"" maxlength=""20"">" & vbNewLine & _
	"				</font>" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine & _
	"		<tr valign=""middle"" height=""30"">" & vbNewLine & _
	"			<td colspan=""2"" align=""center"" width=""100%"">" & vbNewLine & _
	"				<input type=""submit"" name=""Mode"" value=""Add"">   " & vbNewLine & _
	"				<input type=""submit"" name=""Mode"" value=""Edit"">   " & vbNewLine & _
	"				<input type=""submit"" name=""Mode"" value=""Search"">" & vbNewLine & _
	"			</td>" & vbNewLine & _
	"		</tr>" & vbNewLine
if Session("aLev")=1 then 
	Response.Write	"		<tr valign=""middle"" height=""30"">" & vbNewLine & _
		"			<td colspan=""2"" align=""center"" width=""100%"">" & vbNewLine & _
		"<font color=""black"" face=""lucida console"" size=""2""><a href=""voter.asp?id=1"">Voter Registration</a>" & vbNewLine
end if
Response.Write	"	</table>" & vbNewLine & _
	"</form>" & vbNewLine & _
	"</td></tr></table>" & vbNewLine
%>
<!--#INCLUDE FILE="bottom.asp" -->

Carefree
Advanced Member

Philippines
4224 Posts
Posted - 05 October 2010 :  11:24:04  Show Profile  Reply with Quote
Both of the strSQL lines result in the same error.

strSql = "INSERT INTO REGISTRARS SET (Reg_NameL, Reg_NameF, Reg_NameM, Reg_SSAN, Reg_Code, Reg_PW) VALUES ('" & strRNameL & "','" &  strRNameF & "','" &  strRNameM & "','" &  strRSSAN & "','" &  strRCode & "','" &  strRPass & "')"


Example output from update strSQL:

quote:

UPDATE REGISTRARS SET (Reg_NameL, Reg_NameF, Reg_NameM, Reg_SSAN, Reg_Code, Reg_PW) VALUES ('CTest','CTest','R','123456789','WC001','pass') WHERE Reg_Code = 'WC001'


Error Message: Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

Go to Top of Page

HuwR
Forum Admin

United Kingdom
20611 Posts
Posted - 05 October 2010 :  11:58:02  Show Profile  Visit HuwR's Homepage  Reply with Quote
1. you do not use SET in an INSERT statement.

2. in an UPDATE you don't use that format, an update statement look like the following

UPDATE TABLE SET field1=value,field2=value2 .....

MVC .net dev/test site | Snitz .NET Core running on Raspberry Pi

Go to Top of Page

Carefree
Advanced Member

Philippines
4224 Posts
Posted - 05 October 2010 :  12:08:24  Show Profile  Reply with Quote
Just color me brain-dead. lol, thanks.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20611 Posts
Posted - 05 October 2010 :  13:50:36  Show Profile  Visit HuwR's Homepage  Reply with Quote
no worries we all have those moments

MVC .net dev/test site | Snitz .NET Core running on Raspberry Pi

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000 Version 3.4.07