Hi Everybody...

I am having a problem trying to add a new user to my db...

done it thousand times before (maybe not a thousand)i am accessing the db via dsnless connection string localy and on my server...

can anyone spot the mistake I am making?

here is the error message I receive:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/websites/whssch/www/sitemaster/admin.asp, line 136

and here is the page code:

<%@LANGUAGE="VBSCRIPT"%>
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="admin"
MM_authFailedURL="login.asp"
MM_grantAccess=false
If Session("MM_Username" <> "" Then
If (false Or CStr(Session("MM_UserAuthorization")="" Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization")>=1) Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?" >= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL"
if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If
%>
<!--#include file="../Connections/thevci.asp" -->
<%
' *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL")
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 = ""
%>
<%
' *** Redirect if username exists
MM_flag="MM_insert"
If (CStr(Request(MM_flag)) <> "" Then
MM_dupKeyRedirect="sorry.html"
MM_rsKeyConnection=MM_thevci_STRING
MM_dupKeyUsernameValue = CStr(Request.Form("username")
MM_dupKeySQL="SELECT Username FROM Sitemaster WHERE Username='" & MM_dupKeyUsernameValue & "'"
MM_adodbRecordset="ADODB.Recordset"
set MM_rsKey=Server.CreateObject(MM_adodbRecordset)
MM_rsKey.ActiveConnection=MM_rsKeyConnection
MM_rsKey.Source=MM_dupKeySQL
MM_rsKey.CursorType=0
MM_rsKey.CursorLocation=2
MM_rsKey.LockType=3
MM_rsKey.Open
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
MM_qsChar = "?"
If (InStr(1,MM_dupKeyRedirect,"?" >= 1) Then MM_qsChar = "&"
MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
Response.Redirect(MM_dupKeyRedirect)
End If
MM_rsKey.Close
End If
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert") <> "" Then

MM_editConnection = MM_thevci_STRING
MM_editTable = "Sitemaster"
MM_editRedirectUrl = "thankyou.html"
MM_fieldsStr = "username|value|password|value|access|value"
MM_columnsStr = "Username|',none,''|Password|',none,''|Access|',none,''"

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

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(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
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request("MM_insert") <> "" Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),","
Delim = MM_typeArray(0)
If (Delim = "none" Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none" Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none" Then EmptyVal = ""
If (FormVal = "" Then
FormVal = EmptyVal
Else
If (AltVal <> "" Then
FormVal = AltVal
ElseIf (Delim = "'" Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''" & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & " values (" & MM_dbValues & ""

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute <----- line 136!!!!!!!!!!!
MM_editCmd.ActiveConnection.Close

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

End If
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; ch****t=iso-8859-1">
<STYLE TYPE="text/css">

<!--A {text-decoration: none}A:hover {background:yellow}-->

</style>
</head>
<body bgcolor="#FFFFFF" text="#000000" link="#000000" vlink="#000000" alink="#000000" background="../shad.gif">
<table width="500" border="0" cellspacing="0" cellpadding="2" align="center">
<tr>
<td bgcolor="#FF0000" rowspan="8">
<form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#FF0000" colspan="3"><b><font face="Verdana, Arial, Helvetica, sans-serif">New
User</font></b></td>
</tr>
<tr>
<td valign="bottom" bgcolor="#FF0000" colspan="3"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font></td>
</tr>
<tr>
<td valign="bottom" bgcolor="#FF0000" colspan="3"><b>
<input type="text" name="username">
</b></td>
</tr>
<tr>
<td valign="bottom" bgcolor="#FF0000" colspan="3"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Password</font><b></b></td>
</tr>
<tr>
<td valign="top" bgcolor="#FF0000" colspan="3"><b>
<input type="text" name="password">
</b></td>
</tr>
<tr>
<td valign="bottom" bgcolor="#FF0000"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Access</font></b>
</td>
<td valign="bottom" bgcolor="#FF0000"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">admin</font></td>
<td valign="bottom" bgcolor="#FF0000">
<input type="radio" name="access" value="admin" checked>
</td>
</tr>
<tr>
<td valign="top" bgcolor="#FF0000"><b> </b> </td>
<td valign="top" bgcolor="#FF0000"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">registered</font>
</td>
<td valign="top" bgcolor="#FF0000">
<input type="radio" name="access" value="registered">
</td>
</tr>
<tr>
<td bgcolor="#FF0000" colspan="3"><b><b>
<input type="submit" name="Submit2" value="Add User">
</b></b> </td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="true">
</form>
</td>
<td bgcolor="#FFE6CC"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Subjects</font></b></td>
<td bgcolor="#FFE6CC">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newsubjects.asp">Add</a></font></div>
</td>
<td bgcolor="#FFE6CC">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editsubjects.asp">Edit</a></font></div>
</td>
<td bgcolor="#FFE6CC">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deletesubjects.asp">Remove</a></font></div>
</td>
<td bgcolor="#FFE6CC">
<div align="center"><a href="addlink.asp"><font face="Verdana, Arial, Helvetica, sans-serif">Add
Link</font></a></div>
</td>
</tr>
<tr>
<td bgcolor="#FFE6CC"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Staff</font></b></td>
<td bgcolor="#FFE6CC">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newstaff.asp">Add</a></font></div>
</td>
<td bgcolor="#FFE6CC">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editstaff.asp">Edit</a></font></div>
</td>
<td bgcolor="#FFE6CC">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deletestaff.asp">Remove</a></font></div>
</td>
<td bgcolor="#FFE6CC">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="removelink.asp">Remove<font face="Verdana, Arial, Helvetica, sans-serif">
Link</font></a></font></div>
</td>
</tr>
<tr>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><b>Class</b></font></td>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="newclass.asp">Add</a></font></td>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="editclass.asp">Edit</a></font></td>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="deleteclass.asp">Remove</a></font></td>
<td bgcolor="#FFE6CC"> </td>
</tr>
<tr>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><b>Results</b></font></td>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="newresults.asp">Add</a></font></td>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="editresults.asp">Edit</a></font></td>
<td bgcolor="#FFE6CC"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="deleteresults.asp">Remove</a></font></td>
<td bgcolor="#FFE6CC"> </td>
</tr>
<tr>
<td bgcolor="#FFBE7D"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">House</font></b></td>
<td bgcolor="#FFBE7D">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newhouse.asp">Add</a></font></div>
</td>
<td bgcolor="#FFBE7D">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="edithouse.asp">Edit</a></font></div>
</td>
<td bgcolor="#FFBE7D">
<div align="center"></div>
</td>
<td bgcolor="#FFBE7D">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"></font></div>
</td>
</tr>
<tr>
<td bgcolor="#FF8C8F"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Gallery</font></b></td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newgallery.asp">Add</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editgallery.asp">Edit</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deletegallery.asp">Remove</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="activgallery.asp">Activate</a></font></div>
</td>
</tr>
<tr>
<td bgcolor="#FF8C8F"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Awards</font></b></td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newawards.asp">Add</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editawards.asp">Edit</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deleteawards.asp">Remove</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="activawards.asp">Activate</a></font></div>
</td>
</tr>
<tr>
<td bgcolor="#FF8C8F"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">News</font></b></td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newnews.asp">Add</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editnews.asp">Edit</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deletenews.asp">Remove</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="activnews.asp">Activate</a></font></div>
</td>
</tr>
<tr>
<td bgcolor="#FF0000" rowspan="4">
<form name="form2" method="get" action="deleteuser.asp">
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif">Remove
User</font> </b></div>
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font>
<input type="text" name="deleteuser">
<input type="submit" name="Submit" value="Remove">
</form>
</td>
<td bgcolor="#FF8C8F"><font face="Verdana, Arial, Helvetica, sans-serif"><b>Sports</b></font></td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="newsports.asp">Add</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="editsports.asp">Edit</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="deletesports.asp">Remove</a></font></div>
</td>
<td bgcolor="#FF8C8F">
<div align="center"></div>
</td>
</tr>
<tr>
<td bgcolor="#FFE366"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Events</font></b></td>
<td bgcolor="#FFE366">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newevents.asp">Add</a></font></div>
</td>
<td bgcolor="#FFE366">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editevents.asp">Edit</a></font></div>
</td>
<td bgcolor="#FFE366">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deleteevents.asp">Remove</a></font></div>
</td>
<td bgcolor="#FFE366" rowspan="3">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"></font></div>
</td>
</tr>
<tr>
<td bgcolor="#FFE366"><b><font face="Verdana, Arial, Helvetica, sans-serif">Developments</font></b></td>
<td bgcolor="#FFE366"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="newdevelopments.asp">Add</a></font></td>
<td bgcolor="#FFE366"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="editdevelopments.asp">Edit</a></font></td>
<td bgcolor="#FFE366"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="deletedevelopments.asp">Remove</a></font></td>
</tr>
<tr>
<td bgcolor="#FFE366"><b><font face="Verdana, Arial, Helvetica, sans-serif">Thanks</font></b></td>
<td bgcolor="#FFE366"><font face="Verdana, Arial, Helvetica, sans-serif">Add</font></td>
<td bgcolor="#FFE366"><font face="Verdana, Arial, Helvetica, sans-serif">Edit</font></td>
<td bgcolor="#FFE366"><font face="Verdana, Arial, Helvetica, sans-serif"><a href="deletethanks.asp">Remove</a></font></td>
</tr>
<tr>
<td><b></b></td>
<td bgcolor="#99CC99"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Governors</font></b></td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newgovernors.asp">Add</a></font></div>
</td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editgovernors.asp">Edit</a></font></div>
</td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deletegovernors.asp">Remove</a></font></div>
</td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"></font></div>
</td>
</tr>
<tr>
<td><b></b></td>
<td bgcolor="#99CC99"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Vacancies</font></b></td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newvacancies.asp">Add</a></font></div>
</td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editvacancies.asp">Edit</a></font></div>
</td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deletevacancies.asp">Remove</a></font></div>
</td>
<td bgcolor="#99CC99">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"></font></div>
</td>
</tr>
<tr>
<td> </td>
<td bgcolor="#CCFFFF"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Activitites</font></b></td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newactivities.asp">Add</a></font></div>
</td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editactivities.asp">Edit</a></font></div>
</td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deleteactivities.asp">Remove</a></font></div>
</td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="activactivities.asp">Activate</a></font></div>
</td>
</tr>
<tr>
<td bgcolor="#FF00FF"><b><a href="pdfupload.asp"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">PDF
Upload</font></a></b></td>
<td bgcolor="#CCFFFF"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">Reviews</font></b></td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="newreviews.asp">Add</a></font></div>
</td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="editreviews.asp">Edit</a></font></div>
</td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="deletereviews.asp">Remove</a></font></div>
</td>
<td bgcolor="#CCFFFF">
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><a href="activreviews.asp">Activate</a></font></div>
</td>
</tr>
</table>
</body>
</html>

I had a similar issue and it dealt with permissions setup on windows 2000. IIS5.0 was not setup properly. Use error number and search macromedia tech support. The answer is there. I tried to cut/paste the url but no luck.

Michael Rudge

<pre id=code><font face=courier size=2 id=code>MM_editCmd.CommandText = MM_editQuery
<font color=red>response.write MM_EditQuery</font id=red>
MM_editCmd.Execute &lt;----- line 136!!!!!!!!!!!</font id=code></pre id=code>Add the line in red right before the execute, and you will get to see the SQL Statement before it happens. you'll have to troubleshoot from there, but a common error to happen in situations like this is to have a blank numeric field, so the sql ends up like this: <pre id=code><font face=courier size=2 id=code>VALUES ('strValue',,3)</font id=code></pre id=code>Notice the empty space between commas?
SQL Doesn't like this.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/