ASP - setting form field to insert memo

[dlcmpls]

Hi all. I am using DW's Insert Record behavior in an effort to create a form
to insert a record into a MS Access database. Everything works fine - I can
access the db with queries etc. When I use the Server Behavior to create the
asp insert code, I am given some choices for how the form field information is
inserted: Text, Date, etc. However, the field in my db is set to Memo and
that is not one of the choices offered by DW's Insert Record Server Behavior.
So my script chokes. All my other db fields are set to text and they work fine
with an insert. But as soon as I add the Memo field into the mix, the script
chokes and I get this message: Microsoft OLE DB Provider for ODBC Drivers
error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement. /viewpointmsea/msea/admin/insert.asp, line 78 Line
78 in my code is this: MM_editCmd.Execute(); So, I am assuming that there
is a problem related to the fact that I am trying to insert text into a Memo
field, but I don't know how to resolve this? Any help is greatly appreciate.
FYI, here is the code for the Insert that DW created: <%@LANGUAGE='JAVASCRIPT'
CODEPAGE='1252'%> <!--#include file='../../Connections/connViewPoint.asp' -->
<% // *** Edit Operations: declare variables // set the form action variable
var MM_editAction = Request.ServerVariables('SCRIPT_NAME'); if
(Request.QueryString) { MM_editAction += '?' +
Server.HTMLEncode(Request.QueryString); } // boolean to abort record edit var
MM_abortEdit = false; // query string to execute var MM_editQuery = ''; %> <%
// *** Insert Record: set variables if (String(Request('MM_insert')) ==
'insert') { var MM_editConnection = MM_connViewPoint_STRING; var
MM_editTable = 'issues'; var MM_editRedirectUrl = 'success.asp'; var
MM_fieldsStr =
'title|value|Text|value|signature|value|IssueType| value|EndDate|value'; var
MM_columnsStr =
'Title|',none,''|Text|',none,''|Signature|',none,' '|IssueType|',none,''|EndDate|
',none,'''; // create the MM_fields and MM_columns arrays var MM_fields =
MM_fieldsStr.split('|'); var MM_columns = MM_columnsStr.split('|'); //
set the form values for (var i=0; i+1 < MM_fields.length; i+=2) {
MM_fields[i+1] = String(Request.Form(MM_fields)); } // append the query
string to the redirect URL if (MM_editRedirectUrl &amp;&amp;
Request.QueryString &amp;&amp; Request.QueryString.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?'?':'&amp;') +
Request.QueryString; } } %> <% // *** Insert Record: construct a sql insert
statement and execute it if (String(Request('MM_insert')) != 'undefined') {
// create the sql insert statement var MM_tableValues = '', MM_dbValues = '';
for (var i=0; i+1 < MM_fields.length; i+=2) { var formVal =
MM_fields[i+1]; var MM_typesArray = MM_columns[i+1].split(','); var
delim = (MM_typesArray[0] != 'none') ? MM_typesArray[0] : ''; var altVal
= (MM_typesArray[1] != 'none') ? MM_typesArray[1] : ''; var emptyVal =
(MM_typesArray[2] != 'none') ? MM_typesArray[2] : ''; if (formVal == '' ||
formVal == 'undefined') { formVal = emptyVal; } else { if
(altVal != '') { formVal = altVal; } else if (delim == ''') { //
escape quotes formVal = ''' + formVal.replace(/'/g,'''') + '''; }
else { formVal = delim + formVal + delim; } }
MM_tableValues += ((i != 0) ? ',' : '') + MM_columns; MM_dbValues += ((i !=
0) ? ',' : '') + formVal; } MM_editQuery = 'insert into ' + MM_editTable +
' (' + MM_tableValues + ') values (' + MM_dbValues + ')'; if (!MM_abortEdit)
{ // execute the insert var MM_editCmd =
Server.CreateObject('ADODB.Command'); MM_editCmd.ActiveConnection =
MM_editConnection; MM_editCmd.CommandText = MM_editQuery;
MM_editCmd.Execute(); MM_editCmd.ActiveConnection.Close(); if
(MM_editRedirectUrl) { Response.Redirect(MM_editRedirectUrl); } }
} %>

[Nancy Gill]

Memo in Access should work with Text in DMX just fine. It's not?

--
Nancy Gill
Team Macromedia Volunteer for Dreamweaver MX/UltraDev
[url]http://www.macromedia.com/go/team/[/url]
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

"dlcmpls" <webforumsuser@macromedia.com> wrote in message
news:d0b73o$2d1$1@forums.macromedia.com...

> Hi all. I am using DW's Insert Record behavior in an effort to create a

form

> to insert a record into a MS Access database. Everything works fine - I

can

> access the db with queries etc. When I use the Server Behavior to create

the

> asp insert code, I am given some choices for how the form field

information is

> inserted: Text, Date, etc. However, the field in my db is set to Memo

and

> that is not one of the choices offered by DW's Insert Record Server

Behavior.

> So my script chokes. All my other db fields are set to text and they work

fine

> with an insert. But as soon as I add the Memo field into the mix, the

script

> chokes and I get this message: Microsoft OLE DB Provider for ODBC Drivers
> error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error

in

> INSERT INTO statement. /viewpointmsea/msea/admin/insert.asp, line 78

Line

> 78 in my code is this: MM_editCmd.Execute(); So, I am assuming that

there

> is a problem related to the fact that I am trying to insert text into a

Memo

> field, but I don't know how to resolve this? Any help is greatly

appreciate.

> FYI, here is the code for the Insert that DW created:

<%@LANGUAGE='JAVASCRIPT'

> CODEPAGE='1252'%> <!--#include

file='../../Connections/connViewPoint.asp' -->

> <% // *** Edit Operations: declare variables // set the form action

variable

> var MM_editAction = Request.ServerVariables('SCRIPT_NAME'); if
> (Request.QueryString) { MM_editAction += '?' +
> Server.HTMLEncode(Request.QueryString); } // boolean to abort record edit

var

> MM_abortEdit = false; // query string to execute var MM_editQuery = '';

%> <%

> // *** Insert Record: set variables if (String(Request('MM_insert')) ==
> 'insert') { var MM_editConnection = MM_connViewPoint_STRING; var
> MM_editTable = 'issues'; var MM_editRedirectUrl = 'success.asp'; var
> MM_fieldsStr =
> 'title|value|Text|value|signature|value|IssueType| value|EndDate|value';

var

> MM_columnsStr =
>

'Title|',none,''|Text|',none,''|Signature|',none,' '|IssueType|',none,''|EndD
ate|

> ',none,'''; // create the MM_fields and MM_columns arrays var

MM_fields =

> MM_fieldsStr.split('|'); var MM_columns = MM_columnsStr.split('|');

//

> set the form values for (var i=0; i+1 < MM_fields.length; i+=2) {
> MM_fields[i+1] = String(Request.Form(MM_fields)); } // append the

query

> string to the redirect URL if (MM_editRedirectUrl &amp;&amp;
> Request.QueryString &amp;&amp; Request.QueryString.Count > 0) {
> MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?')

== -1)?'?':'&amp;') +

> Request.QueryString; } } %> <% // *** Insert Record: construct a sql

insert

> statement and execute it if (String(Request('MM_insert')) != 'undefined')

{

> // create the sql insert statement var MM_tableValues = '', MM_dbValues

= '';

> for (var i=0; i+1 < MM_fields.length; i+=2) { var formVal =
> MM_fields[i+1]; var MM_typesArray = MM_columns[i+1].split(',');

var

> delim = (MM_typesArray[0] != 'none') ? MM_typesArray[0] : ''; var

altVal

> = (MM_typesArray[1] != 'none') ? MM_typesArray[1] : ''; var emptyVal

=

> (MM_typesArray[2] != 'none') ? MM_typesArray[2] : ''; if (formVal ==

'' ||

> formVal == 'undefined') { formVal = emptyVal; } else { if
> (altVal != '') { formVal = altVal; } else if (delim == ''')

{ //

> escape quotes formVal = ''' + formVal.replace(/'/g,'''') +

}

> else { formVal = delim + formVal + delim; } }
> MM_tableValues += ((i != 0) ? ',' : '') + MM_columns; MM_dbValues +=

((i !=

> 0) ? ',' : '') + formVal; } MM_editQuery = 'insert into ' +

MM_editTable +

> ' (' + MM_tableValues + ') values (' + MM_dbValues + ')'; if

(!MM_abortEdit)

> { // execute the insert var MM_editCmd =
> Server.CreateObject('ADODB.Command'); MM_editCmd.ActiveConnection =
> MM_editConnection; MM_editCmd.CommandText = MM_editQuery;
> MM_editCmd.Execute(); MM_editCmd.ActiveConnection.Close(); if
> (MM_editRedirectUrl) {

Response.Redirect(MM_editRedirectUrl); } }

> } %>
>

[CarlGrint]

YOu have used a Reserved word in either your Database or Form.

I also find it useful to place the suffix txt before my form field name, such
as txtName and fld before the table column in Access, so would be fldName.

That way you ensure you don't use a reserved word.

Macromedia have a document on this
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_14622[/url]

I have links to both reserved words for SQL server and Access on my site,
[url]www.cgw3.co.uk[/url] which you can go through to.

[dlcmpls]

Thanks Carl - you were right! It was a problem with a reserved word. I
changed the field name and now it's fine. Thanks so much for the help. I
can't tell you how wonderful it is toknow that there are people willing to
help. dlc