I am trying to connect a database to a website for the first time using Sean Nicholson's Dreamweaver MX2004 and Databases. I am at a point where I don't know where to go next. Any help would be appreciated.

I am creating a form in Dreamweaver from an Access database using Record Insertion Form Wizard. When I test the form I get the following error:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at DreamweaverCtrls.DataSet.DoInit()

Edited by - huck4 on 02 Mar 2004 06:18:41

I am having a similar problem, I hope that someone can help me, please.
This is the error I get when submitting a very simple INSERT statement using an Access Database (which does have read/write permissions for IUSR_Guest

System.Data.OleDb.OleDbException: Operation must use an updateable query.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at DreamweaverCtrls.DataSet.DoInit()

Below you can find the Dreamweaver generated code:

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=1.0.0.0,publicKeyToken=836f606ede05d46a,culture=neutral" %>
&lt;MM<img src=../images/dmxzone/forum/icon_smile_blush.gif border=0 align=middle>nsert
runat="server"
CommandText='&lt;%# "INSERT INTO Categorias (Categoria) VALUES (?)" %&gt;'
ConnectionString='&lt;%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_conBateo" %&gt;'
DatabaseType='&lt;%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_conBateo" %&gt;'
Expression='&lt;%# Request.Form("MM_insert" = "form1" %&gt;'
CreateDataSet="false"
SuccessURL='&lt;%# "default.aspx" %&gt;'
Debug="true"
&gt;
&lt;Parameters&gt;
&lt;Parameter Name="@Categoria" Value='&lt;%# IIf((Request.Form("Categoria" &lt;&gt; Nothing), Request.Form("Categoria", "" %&gt;' Type="WChar" /&gt;
&lt;/Parameters&gt;
&lt;/MM<img src=../images/dmxzone/forum/icon_smile_blush.gif border=0 align=middle>nsert&gt;
&lt;MM<img src=../images/dmxzone/forum/icon_smile_tongue.gif border=0 align=middle>ageBind runat="server" PostBackBind="true" /&gt;
&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
&lt;html xmlns="www.w3.org/1999/xhtml"&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;form method="post" name="form1" runat="server"&gt;
&lt;table align="center"&gt;
&lt;tr valign="baseline"&gt;
&lt;td&gt;&lt;asp:textbox id="Categoria" TextMode="SingleLine" Columns="32" runat="server" /&gt;
&lt;/td&gt;
&lt;td&gt;&lt;input type="submit" value="Crear categor&iacute;a"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;input type="hidden" name="MM_insert" value="form1"&gt;
&lt;/form&gt;
&lt;p&gt;&nbsp;&lt;/p&gt;
&lt;/body&gt;
&lt;/html&gt;

It could be that you are using some Reserved words on your fields or record names.

For example, date is a reseved word and you cannot use it as is, you can use something like dteDate. See this link for a list of reserved words: support.microsoft.com/kb/q209187/

Also you can check the www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_14622 at the bottom of the page you'll see other links as well.

Cheers,

Javier

Thanks Javier,

I finally solved it. The problem was with the permissions. I didn't know that Windows 2003 server uses the NETWORK SERVICE account to execute asp .Net code (unlike other servers that use the ASPNET account) or more common yet the IUSR_machinename account for traditional asp.

I hope that someone else finds this useful. Just grant read/write permissions to the database file and that's it.


<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
It could be that you are using some Reserved words on your fields or record names.

For example, date is a reseved word and you cannot use it as is, you can use something like dteDate. See this link for a list of reserved words: support.microsoft.com/kb/q209187/

Also you can check the www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_14622 at the bottom of the page you'll see other links as well.

Cheers,

Javier
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

PERFECT!!! Thanks! Network Service account who knew!!!