Can't write to database! Can you help please?

[vinny199]

Hi,
Please help me if you can as everything I have tried has failed and I ran out of ideas:
I can view the database no problem but can't write/update it.
I always get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Operation must use an updateable query.
This error suggests permission issues, but both read/write permissions are given in access2000 for my database and in IIS.
No userid/paswords have been set.
I connect to the database using an OLEDB connection.
I have created the virtual directory in IIS.
All is fine but no writing to the database!!

I have used recordsets to update the database.
There are two recordsets, the first one is to view the database, the second one to insert data.
i am learning how to use Dreamweaver MX which (I don't know if you are familiar with) makes the connection string as a file and calls it up in the page.
So for: <!--#include file=&quot;Connections/conn_webprodmx.asp&quot; --> please read:&quot;Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\webprodmx_files\webprodmx_data.mdb;&quot;

Here is the code .

Thanks you,

Vinny


<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<!--#include file=&quot;Connections/conn_webprodmx.asp&quot; -->
<%
' *** 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(&quot;SCRIPT_NAME&quot)
If (Request.QueryString <> &quot;&quot Then
MM_editAction = MM_editAction & &quot;?&quot; & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = &quot;&quot;
%>
<%
' *** Insert Record: set variables

If (CStr(Request(&quot;MM_insert&quot) = &quot;form1&quot Then

MM_editConnection = MM_conn_webprodmx_STRING
MM_editTable = &quot;categories&quot;
MM_editRedirectUrl = &quot;categories3.asp&quot;
MM_fieldsStr = &quot;category|value&quot;
MM_columnsStr = &quot;category|',none,''&quot;

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, &quot;|&quot
MM_columns = Split(MM_columnsStr, &quot;|&quot

' 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 <> &quot;&quot; And Request.QueryString <> &quot;&quot Then
If (InStr(1, MM_editRedirectUrl, &quot;?&quot;, vbTextCompare) = 0 And Request.QueryString <> &quot;&quot Then
MM_editRedirectUrl = MM_editRedirectUrl & &quot;?&quot; & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & &quot;&&quot; & Request.QueryString
End If
End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

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

' create the sql insert statement
MM_tableValues = &quot;&quot;
MM_dbValues = &quot;&quot;
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),&quot;,&quot
MM_delim = MM_typeArray(0)
If (MM_delim = &quot;none&quot Then MM_delim = &quot;&quot;
MM_altVal = MM_typeArray(1)
If (MM_altVal = &quot;none&quot Then MM_altVal = &quot;&quot;
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = &quot;none&quot Then MM_emptyVal = &quot;&quot;
If (MM_formVal = &quot;&quot Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> &quot;&quot Then
MM_formVal = MM_altVal
ElseIf (MM_delim = &quot;'&quot Then ' escape quotes
MM_formVal = &quot;'&quot; & Replace(MM_formVal,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_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(MM_i)
MM_dbValues = MM_dbValues & MM_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

End If
%>
<%
Dim rsCategories
Dim rsCategories_numRows

Set rsCategories = Server.CreateObject(&quot;ADODB.Recordset&quot
rsCategories.ActiveConnection = MM_conn_webprodmx_STRING
rsCategories.Source = &quot;SELECT * FROM categories ORDER BY category_id ASC&quot;
rsCategories.CursorType = 0
rsCategories.CursorLocation = 2
rsCategories.LockType = 1
rsCategories.Open()

rsCategories_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsCategories_numRows = rsCategories_numRows + Repeat1__numRows
%>
<%
Dim MM_paramName
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

Dim MM_keepNone
Dim MM_keepURL
Dim MM_keepForm
Dim MM_keepBoth

Dim MM_removeList
Dim MM_item
Dim MM_nextItem

' create the list of parameters which should not be maintained
MM_removeList = &quot;&index=&quot;
If (MM_paramName <> &quot;&quot Then
MM_removeList = MM_removeList & &quot;&&quot; & MM_paramName & &quot;=&quot;
End If

MM_keepURL=&quot;&quot;
MM_keepForm=&quot;&quot;
MM_keepBoth=&quot;&quot;
MM_keepNone=&quot;&quot;

' add the URL parameters to the MM_keepURL string
For Each MM_item In Request.QueryString
MM_nextItem = &quot;&&quot; & MM_item & &quot;=&quot;
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
End If
Next

' add the Form variables to the MM_keepForm string
For Each MM_item In Request.Form
MM_nextItem = &quot;&&quot; & MM_item & &quot;=&quot;
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
End If
Next

' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
If (MM_keepBoth <> &quot;&quot Then
MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
End If
If (MM_keepURL <> &quot;&quot Then
MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
End If
If (MM_keepForm <> &quot;&quot Then
MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
End If

' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> &quot;&quot Then
MM_joinChar = &quot;&&quot;
Else
MM_joinChar = &quot;&quot;
End If
End Function
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot; />
</head>

<body>

<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>

<table border=&quot;1&quot;>
<tr>
<td>category_id</td>
<td>category</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF)) %>
<tr>
<td><%=(rsCategories.Fields.Item(&quot;category_id&quot.Value)%></td>
<td><A HREF=&quot;book.asp?<%= MM_keepNone & MM_joinChar(MM_keepNone) & &quot;category_id=&quot; & rsCategories.Fields.Item(&quot;category_id&quot.Value %>&quot;><%=(rsCategories.Fields.Item(&quot;category&quot.Value)%></A></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
</table>
<hr />
<p> </p>
<form name=&quot;form1&quot; id=&quot;form1&quot; method=&quot;POST&quot; action=&quot;<%=MM_editAction%>&quot;>
<table width=&quot;50%&quot; border=&quot;1&quot;>

<tr>
<td>Category</td>
<td> <input name=&quot;category&quot; type=&quot;text&quot; id=&quot;category&quot; /></td>
</tr>
<tr>
<td> </td>
<td> <input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Insert Category&quot; /></td>
</tr>
</table>
<input type=&quot;hidden&quot; name=&quot;MM_insert&quot; value=&quot;form1&quot;>
</form>
<p> </p>
<p> </p>
</body>
</html>
<%
rsCategories.Close()
Set rsCategories = Nothing
%>

 

[AWithers]

I suggest writing your sql string to the screen and then trying to execute it in the Access db, i suspect that it wont execute there..you may be able to ensure that the string is correct or code it slightly different so that it works

Hope this helps

Andy

 

[emblewembl]

Hi Vinny1999,

I have had that error before and making the presumption that your code and SQL query are correct(I haven't got enough time to check!) it is worth checking the following:

In Explorer select your folder that the database is in and right click, then select properties, then the Security tab. In win 2000 you may have a list of different users - make sure that when you select the Internet User/ Everyone option, that you have given write access. Also ensure the check box is checked at the bottom where it says 'allow inheritable permissions from parent....'. Do the same for the folder that the database is in and see if that helps. For other versions of Windows I'm not too sure, but this works for 2000.

Also, if this doesn't help then copy your error message and search for it in google.... i have often solved problems this way!!

 

[vinny199]

Thanks emblewembl, this is exactly what the problem was.

Cheers,


Vinny