Master/Detail page with Update function

I'm starting to think I've found a new bug of some sort. After creating an Insert Record page using the Wizard, I'm getting a similar error at the cmd.Execute line. BTW, I'm using the new IE7 beta. $10 says that was a mistake and this is the result.

hi .. I just saw your response .. give me a few minutes to get through the
code. :) thanks for posting it.


--
Nancy Gill
Adobe Community Expert
BLOG: http://www.dmxwishes.com/blog.asp
Author: Dreamweaver 8 e-book for the DMX Zone
Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A Beginner's
Guide, Mastering Macromedia Contribute
Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web Development


"adduncan" <webforumsuser@macromedia.com> wrote in message
news:e83nkb$ef6$1@forums.macromedia.com...
> Thanks for replying. Here's the code for the updates:
> ---------------------------------------
> ' *** Edit Operations: declare variables
>
> Dim MM_editAction
> Dim MM_abortEdit
> Dim MM_editQuery
> Dim MM_editCmd
>
> Dim MM_editConnection
> Dim MM_editTable
> Dim MM_editRedirectUrl
> Dim MM_editColumn
> Dim MM_recordId
>
> Dim MM_fieldsStr
> Dim MM_columnsStr
> Dim MM_fields
> Dim MM_columns
> Dim MM_typeArray
> Dim MM_formVal
> Dim MM_delim
> Dim MM_altVal
> Dim MM_emptyVal
> Dim MM_i
>
> MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
> If (Request.QueryString <> "") Then
> MM_editAction = MM_editAction & "?" &
> Server.HTMLEncode(Request.QueryString)
> End If
>
> ' boolean to abort record edit
> MM_abortEdit = false
>
> ' query string to execute
> MM_editQuery = ""
> %>
> <%
> ' *** Update Record: set variables
>
> If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId"))
> <>
> "") Then
>
> MM_editConnection = MM_connJoe_STRING
> MM_editTable = "DAR2"
> MM_editColumn = "ID"
> MM_recordId = "" + Request.Form("MM_recordId") + ""
> MM_editRedirectUrl = "registrar.asp"
> MM_fieldsStr =
> "Prefix|value|FirstName|value|MiddleName|value|MaidenName|value|LastName|value|N
> ickname|value|AreaCode|value|PhoneNo|value|DOB|value|DateApproved|value|SpouseNa
> me|value|SpouseNameSuffix|value|Address1|value|Address2|value|City|value|State|v
> alue|Zip|value|MemType|value|AncestorName|value|AncestorState|value|Email|value|
> FaxNumber|value|DateDeceased|value|DateTransferrredIn|value|DateTransferredOut|v
> alue|Resigned|value|Reinstated|value|CurrentJunior|value|year_25|value|year_50|v
> alue|ChapOffice|value|StateOffice|value|NatlOffice|value|ChapChair|value|StateCh
> air|value|NatlChair|value|LastUpdate|value|rec_newsletter|value|access_level|val
> ue|deceased|value|current_member|value"
> MM_columnsStr =
> "Prefix|',none,''|FirstName|',none,''|MiddleName|',none,''|MaidenName|',none,''|
> LastName|',none,''|Nickname|',none,''|AreaCode|',none,''|PhoneNo|',none,''|DOB|'
> ,none,NULL|DateApproved|',none,NULL|SpouseName|',none,''|SpouseNameSuffix|',none
> ,''|Address1|',none,''|Address2|',none,''|City|',none,''|State|',none,''|Zip|',n
> one,''|MemType|',none,''|AncestorName|',none,''|AncestorState|',none,''|E-mail|'
> ,none,''|FaxNumber|',none,''|DateDeceased|',none,NULL|DateTransferrredIn|',none,
> NULL|DateTransferredOut|',none,NULL|Resigned|',none,NULL|Reinstated|',none,''|Cu
> rrentJunior|none,'Y','N'|year_25|none,'Y','N'|year_50|none,'Y','N'|ChapOffice|',
> none,''|StateOffice|',none,''|NatlOffice|',none,''|ChapChair|',none,''|StateChai
> r|',none,''|NatlChair|',none,''|LastUpdate|',none,NULL|rec_newsletter|',none,''|
> access_level|',none,''|deceased|none,'Y','N'|current_member|none,'Y','N'"
>
> ' create the MM_fields and MM_columns arrays
> MM_fields = Split(MM_fieldsStr, "|")
> MM_columns = Split(MM_columnsStr, "|")
>
> ' set the form values
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_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
> %>
> <%
> ' *** Update Record: construct a sql update statement and execute it
>
> If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <>
> "")
> Then
>
> ' create the sql update statement
> MM_editQuery = "update " & MM_editTable & " set "
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_formVal = MM_fields(MM_i+1)
> MM_typeArray = Split(MM_columns(MM_i+1),",")
> MM_delim = MM_typeArray(0)
> If (MM_delim = "none") Then MM_delim = ""
> MM_altVal = MM_typeArray(1)
> If (MM_altVal = "none") Then MM_altVal = ""
> MM_emptyVal = MM_typeArray(2)
> If (MM_emptyVal = "none") Then MM_emptyVal = ""
> If (MM_formVal = "") Then
> MM_formVal = MM_emptyVal
> Else
> If (MM_altVal <> "") Then
> MM_formVal = MM_altVal
> ElseIf (MM_delim = "'") Then ' escape quotes
> MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
> Else
> MM_formVal = MM_delim + MM_formVal + MM_delim
> End If
> End If
> If (MM_i <> LBound(MM_fields)) Then
> MM_editQuery = MM_editQuery & ","
> End If
> MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
> Next
> MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " &
> MM_recordId
>
> If (Not MM_abortEdit) Then
> ' execute the update
> Set MM_editCmd = Server.CreateObject("ADODB.Command")
> MM_editCmd.ActiveConnection = MM_editConnection
> MM_editCmd.CommandText = MM_editQuery
> MM_editCmd.Execute <------- This is Line 111
> MM_editCmd.ActiveConnection.Close
> ---------------------------------------------------------------
> I already closed up the gaps/spaces in the column names. That caused a
> different error message.
> Funny thing is, if I just use a plain, ordinary update page without all of
> the
> search and other functions, it works.
>
> Thank you again.
>

Thanks for replying. Here's the code is attached.
Line 111 which generates the error is simply the cmd Execute line toward the end.
I already closed up the gaps/spaces in the column names. That caused a different error message.
Funny thing is, if I just use a plain, ordinary update page without all of the search and other functions, it works.

Thank you again.
(Edited to use the Attach Code button so you can read it better.)

Could we see the code for your Update statement please? The first thing I
would check is whether you might have a space in your database table.


--
Nancy Gill
Adobe Community Expert
BLOG: http://www.dmxwishes.com/blog.asp
Author: Dreamweaver 8 e-book for the DMX Zone
Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A Beginner's
Guide, Mastering Macromedia Contribute
Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web Development



"adduncan" <webforumsuser@macromedia.com> wrote in message
news:e82cfa$kr6$1@forums.macromedia.com...
> Hi folks, I hope someone can help me with this issue.
> I'm writing a site in ASP for a club w/ a large membership. Part of the
> private side involves selecting a member and updating her info. The
> procedure
> is broken down because the end-users aren't all that computer savvy and
> need
> step-by-step instructions.
> 1) Text box for a search where they enter a last name.
> 2) Search brings back list of all w/ that name (Master Page)
> 3) Detail page *ideally* should be the update page, but that wasn't
> happening. (Would like it to though.) So I left the detail page alone
> and
> linked it to a separate update page w/ the same variable passed through.
> 4) The update page appears w/ the correct record, but when the submit
> button
> gets hit, I get the following error message:
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> statement.
>
> /etcetcetcetc.asp, line 111
> I'm using the DW8 wizard to create the Update code (all of the code,
> really)
> and I have *never* run into this problem and I've been using DW since
> Version 2!
>
> Any ideas? Best case scenario is to have the detail page also be the
> update
> page, but when I tried that, I got the detail page in a table and a
> separate
> update form with the wrong record.
>
> Help???
>