HELP: Can't get an INSERT statement to run with Access DB
owen
I am trying to acheive something very simple. I have a guestbook page and
all the entries go into a table in my MS-Access database.
When the user clicks the Submit button, the ASP page is meant to construct
an INSERT statement and excecute it, thus creating a new entry in my
'Reviews' table on the database.
' ========== / begin sample / ============
dim comments
comments = request.Form("comments")
comments = replace(comments,chr(13)," ")
comments = replace(comments,chr(10)," ")
dim name
name = request.form("name")
dim email
email = request.form("email")
dim website
website = request.form("website")
dim objCmd
set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = conn.ConnectionString
dim insertSQL
insertSQL = "INSERT INTO reviews (name, email, website, comments) " & _
" VALUES ('" & name & "','" & email & "','" & website & "','" & comments &
"') "
dim intRecs
objCmd.CommandType= adCmdText
objCmd.CommandText = insertSQL
objCmd.Execute intRecs
' ========== / end sample / ============
The problem is that everytime I run this simple bit of code, I get the
following ODBC error from the Microsoft Access driver:
" Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable
query. "
Please help me because I can see no way around this and I've no idea what
I'm doing wrong!
Thanks!
Owen
PS. replies cc'd by email appreciated.
SPAMBLOCKERo...@SPAMBLOCKERdsl.pipex.com
(remove SPAMBLOCKERS to reply by email - thanks)
Bob Barrows
1. This article deals with your immediate problem:
http://www.aspfaq.com/show.asp?id=2062
2. You should stop using ODBC and switch to a connection string using the
native Jet OLEDB provider. See this page for an example:
www.connectionstrings.com
3. You should be using a saved parameter query instead of dynamic sql (see
the SQL Injection FAQ at www.sqlsecurity.com to see one of the problems with
using dynamic sql - other problems include reduced performance and increased
network traffic). Try this:
Create a saved query called qInsReview in your database, using this sql:
INSERT INTO reviews (name, email, website, comments)
VALUES([pname],[pemail],[pwebsite],[pcomments])
Then, in ASP, open your connection using the Jet 4.0 OLEDB provider,
populate and validate your variables, and simply issue this command:
conn.qInsReview name,email,website,comments
That's it. If you have a saved query that returns records, you can open a
recordset using the same technique, simply by supplying an instantiated
recordset variable as the last argument:
set rs = server.createobject("adodb.recordset")
conn.qGetRecords parm1,parm2, rs
HTH,
Bob Barrows
owen wrote:
> Hi.
>
> I am trying to acheive something very simple. I have a guestbook
> page and all the entries go into a table in my MS-Access database.
>
> When the user clicks the Submit button, the ASP page is meant to
> construct an INSERT statement and excecute it, thus creating a new
> entry in my 'Reviews' table on the database.
>
<snip>
owen
Thanks for the helpful reply. I have done what you suggested, I changed the
ConnectionString to the JET version, and used a stored qurery with
paramaters - which looks a much better way, so thanks!
Just one problem though. I'm still getting the error. Only this time it's
the JET version:
" Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query. "
I read the article you suggested in your 1st point, and as a result I've
checked that:
(a) The MDB file is not read only.
(b) The MDB file is not open or in use elsewhere.
(c) There are no user permissions set on the MDB file whatsoever.
(d) There are no constraints or links to other tables that may cause the
problem.
(e) There is WRITE permission on the 'sharing' tab of my 'website' folder.
(f) My paramater-ised query works fine within Access itself, so there's
nothing wrong with the query.
I feel its probably something to do with access permissions on my website
folder but I can't see anything wrong. Any ideas what I should be checking?
Thanks for any help
Owen
SPAMBLOCKERo...@SPAMBLOCKERdsl.pipex.com
(remove SPAMBLOCKERS to reply by email - thanks)
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:eETTdnvF...@TK2MSFTNGP10.phx.gbl...
owen
I've solved it.... on the "Directory Security" tab of 'Default Web Site'
properties, I changed the 'Anonymous Access' username to 'IUSR_MACHINE', and
suddenly it started working.
I dont fully understand why though. I've a feeling you'll be able to
enlighten me?
Thanks!
Bob Barrows
> Bob,
>
> (c) There are no user permissions set on the MDB file whatsoever.
the folder containing the mdb file
> (d) There are no constraints or links to other tables that may cause
> the problem.
> (e) There is WRITE permission on the 'sharing' tab of my 'website'
> folder. (f) My paramater-ised query works fine within Access itself,
> so there's nothing wrong with the query.
Again, it's an file system security issue.
Bob Barrows
> Hooorayyy!!!
>
> I've solved it.... on the "Directory Security" tab of 'Default Web
> Site' properties, I changed the 'Anonymous Access' username to
> 'IUSR_MACHINE', and suddenly it started working.
>
> I dont fully understand why though. I've a feeling you'll be able to
> enlighten me?
>
> Thanks!
> Owen
>
folder, rather than granting the users of the system the permissions. By
changing the 'Anonymous Access' username to the IUSR account, you caused the
IUSR account to masquerade as the logged in user, so the the file
permissions are now sufficient.
Bob Barrows
owen
> owen wrote:
> > Bob,
> >
> > (c) There are no user permissions set on the MDB file whatsoever.
> All users who need to use this database must have NTFS Change permissions
on
> the folder containing the mdb file
But what happens when I upload it to the internet? Do my website visitors
get turned away because they dont have permission to use the folder? Surely
I can set it so ANY user has access to read/write it.
I seem to recall some kind of "Internet Guest" account... does that mean
anything to you?
Owen
Bob Barrows
That's what you set when you set the 'Anonymous Access' setting to the IUSR
account. Again, that caused the IUSR account to masquerade as the user.
Bob