Insert into statement error

[Aaronito]

Can someone help... I am sooooo frustrated! I am trying to make an access address book for a small company. I can pull records from the db and display but when I try to do an input record I get this error. I have been checking the FAQ and have done everything I can find.....

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/newform.asp, line 90


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)

Page:
POST 136 bytes to /newform.asp

POST Data:
LASTNAME=Testa&FIRSTNAME=Paul&ADDRESS=&CITY=Herndon&STATE=VA&ZIP=&BIRTHDATE=&WORKTEL=&HOMETEL=&CELLULAR=&EMAIL=&CHILDREN=&MM_insert=true

 

[derren]

Hi

We realy need to see your code for the insert SQL before we can pass judgement. I suspect you have just made a small error there. Post the code and we'll see Derren
[The only person in the world to like Word]

 

[schase]

yup Derren's right we'll need the code.
probably the field names too.

In addition here is something from Macromedia about reserved name fields & invalid field characters.

http://www.macromedia.com/support/ultradev/ts/documents/insert_into.htm

 

[Aaronito]

Guys....

thanks for responding so promptly, I am impressed.
I have included the code for the insert into statement but it was generated by UD. I know the correction is right because I use the same DSN for phone number search (it works!)

If (CStr(Request(&quot;MM_insert&quot) <> &quot;&quot Then

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

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject(&quot;ADODB.Command&quot
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> &quot;&quot Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

 

[schase]

need the table section for fields.

 

[mlawson]

Yep, could you list the Access table field names for your entire table too please?

In addition to the stuff schase asked for.

ta

M

 

[Aaronito]

These are the Access field names... the DSN is good because I used it in a search function. I can't update or insert!
ID
LAST_NAME
FIRST_NAME
ADDRESS
CITY
STATE
ZIP
BIRTHDATE
WORK_TEL
HOME_TEL
CELLULAR
E-MAIL
CHILDREN

 

[schase]

dump the - in E-Mail.

it's a no-no chacter/symbol of Access.
The following symbols should not be used as part of a field name:
.
/
*
:
!
#
&
-
?


Naughty naughty and all that stuff.

And as much as I'm guilty about it too - adopt a uniform naming process.

fields start with fld
tables start with tbl
and so on.

so like fldEmail inside tblUsers.

you get the point i'm sure.

the - is definately a no-no, so dump that, and see how it goes. we need to eliminate known difficulties first - then narrow in for the kill! &quot;Insert witty remark here&quot;

Stuart

 

[linrudland]

Hi
I've got the same problem and error message. I've narrowed mine down to the date field by removing all the fields one at a time. It doesn't seem to matter what format I try (I am careful to match the table formats to the UD field format)it will not work. The dates are for a timesheet and are picked from a menu/list based on a table in access. This then inserts into another table in access with the same field type and format. The only way I can get it to work is to change he whole thing to text which means I have to then reformat the dates back out in the database - it isn't difficult but it is a pain. I'm new to Ultradev so sorry if this is obvious to everyone else.

Remember, the gene pool has no lifeguard.
Linda

 

[schase]

Odd, that shouldnt be a problem, except what field name is your date field? &quot;Insert witty remark here&quot;

Stuart

 

[linrudland]

Hi
Thanks for reminding about the date keyword. When I changed the field to text I changed the fieldnames to txtDate to remind me of the change and that I needed to fix the dev. database to match prod. Therefore the SQL worked. Prod has field prefix so it will be OK.
Many Many thanks.


Linda

 

[schase]

anytime &quot;Insert witty remark here&quot;

Stuart