Master/Detail page with Update function

[adduncan]

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???

[Newsgroup_User]

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???
>

[adduncan]

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.)

[Newsgroup_User]

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.
>

[adduncan]

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.

[adduncan · Accepted solution]

GOT IT!!!
The problem all along was that I hadn't applied the version 8.0.2 updater. Once I did that, the code generate worked exactly as it was supposed to in the beginning - no extra pages to pass variables through, etc etc.

Thanks everyone for looking.