INSERT error :: Updateable query?

J

jason

I am picking up an error message on a straightforward INSERT - do I need an
optimistic-type to get this working....here is is the error:

Microsoft JET Database Engine error '80004005' Operation must use an
updateable query. /catamaranco/accounts/email_inc.asp, line 264


Set cnn = CreateObject("ADODB.Connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("../database/acc.mdb") '//This one is for Access 2000/2002
cnn.Open(strCon)

SQL = "INSERT INTO tblCheck_Request (Department_ID, Authorizer_ID,
Requester_ID, Payee, Check_Reason, Amount, Deadline_Date, Yacht) VALUES ("

SQL=SQL & "'" & Department_ID & "', "

SQL=SQL & "'" & Authorizer_ID & "', "

SQL=SQL & "'" & Requester_ID & "', "

SQL=SQL & "'" & Payee & "', "

SQL=SQL & "'" & Check_Reason & "', "

SQL=SQL & "'" & Amount & "', "

SQL=SQL & "'" & Deadline_Date & "', "

SQL=SQL & "'" & Yacht & "')"

Response.Write SQL
Set rs = cnn.Execute(SQL)
 
B

Bob Barrows

http://www.aspfaq.com/show.asp?id=2062 - updatable cursor
http://www.aspfaq.com/show.asp?id=2009 - 80004005 errors

HTH,
Bob Barrows
PS. You may want to consider parameterizing this query, either using a saved
parameter query, or by parameterizing your SQL statement using "?"
placeholders and Command object per the technique described by Daniel Bush
in this thread:
http://tinyurl.com/jiay
Myself, I prefer the saved parameter query approach. Create a saved query
(call it qInsChkReq) using this SQL:
INSERT INTO tblCheck_Request (Department_ID, Authorizer_ID,
Requester_ID, Payee, Check_Reason, Amount, Deadline_Date, Yacht)
VALUES ([p1], [p2], [p3], [p4], [p5], [p6], [p7], [p8])

Note: no delimiters. Using this technique, you do not have to worry about
delimiting strings and dates. When you run this query in Access (which you
should always do to detect syntax errors), you will be prompted for the 8
parameter values. In ASP, you will provide these values in your code.

In ASP, do this after opening your connection:

cnn.qInsChkReq Department_ID, Authorizer_ID, Requester_ID, _
Payee, Check_Reason, Amount, Deadline_Date, Yacht

That's it. Again: notice that no delimiters or concatenation had to be used.
And, you don't have to worry about escaping literal quotes in your string
data.

You still have to take care of the permissions problem discussed in the
aspfaq articles.
 
J

jason

Thanks Bob - I am trying to confirm from my host provider is they have
permissions enabled - its wierd as I am almost certain they do.

Re: Paramatized queries - I have just recently discovered the beauty of
these in another application...I will start doing it the way you described:

But I notice you did not:

1. Flag PARAMETER at the top of the saved query: eg PARAMETER p1 Long

Also, Do you use the following command to execute the para. query (including
dates):

SQL = "EXEC qry_Listings @P1" & varPI

set rs = cnn.execute(SQL)

....Is this good enough - or, is the command object a better choice?

Cheers

Jason

Bob Barrows said:
http://www.aspfaq.com/show.asp?id=2062 - updatable cursor
http://www.aspfaq.com/show.asp?id=2009 - 80004005 errors

HTH,
Bob Barrows
PS. You may want to consider parameterizing this query, either using a saved
parameter query, or by parameterizing your SQL statement using "?"
placeholders and Command object per the technique described by Daniel Bush
in this thread:
http://tinyurl.com/jiay
Myself, I prefer the saved parameter query approach. Create a saved query
(call it qInsChkReq) using this SQL:
INSERT INTO tblCheck_Request (Department_ID, Authorizer_ID,
Requester_ID, Payee, Check_Reason, Amount, Deadline_Date, Yacht)
VALUES ([p1], [p2], [p3], [p4], [p5], [p6], [p7], [p8])

Note: no delimiters. Using this technique, you do not have to worry about
delimiting strings and dates. When you run this query in Access (which you
should always do to detect syntax errors), you will be prompted for the 8
parameter values. In ASP, you will provide these values in your code.

In ASP, do this after opening your connection:

cnn.qInsChkReq Department_ID, Authorizer_ID, Requester_ID, _
Payee, Check_Reason, Amount, Deadline_Date, Yacht

That's it. Again: notice that no delimiters or concatenation had to be used.
And, you don't have to worry about escaping literal quotes in your string
data.

You still have to take care of the permissions problem discussed in the
aspfaq articles.

I am picking up an error message on a straightforward INSERT - do I
need an optimistic-type to get this working....here is is the error:

Microsoft JET Database Engine error '80004005' Operation must use an
updateable query. /catamaranco/accounts/email_inc.asp, line 264


Set cnn = CreateObject("ADODB.Connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("../database/acc.mdb") '//This one is for Access
2000/2002 cnn.Open(strCon)

SQL = "INSERT INTO tblCheck_Request (Department_ID, Authorizer_ID,
Requester_ID, Payee, Check_Reason, Amount, Deadline_Date, Yacht)
VALUES ("

SQL=SQL & "'" & Department_ID & "', "

SQL=SQL & "'" & Authorizer_ID & "', "

SQL=SQL & "'" & Requester_ID & "', "

SQL=SQL & "'" & Payee & "', "

SQL=SQL & "'" & Check_Reason & "', "

SQL=SQL & "'" & Amount & "', "

SQL=SQL & "'" & Deadline_Date & "', "

SQL=SQL & "'" & Yacht & "')"

Response.Write SQL
Set rs = cnn.Execute(SQL)
 
J

jason

Thanks - I'll check with my host to confirm permissions assignment...seems
to be what aspfaq.com is recommending....
 
B

Bob Barrows

jason said:
Thanks Bob - I am trying to confirm from my host provider is they have
permissions enabled - its wierd as I am almost certain they do.

Re: Paramatized queries - I have just recently discovered the beauty
of these in another application...I will start doing it the way you
described:

But I notice you did not:

1. Flag PARAMETER at the top of the saved query: eg PARAMETER p1 Long
Not necessary. But it does help enforce data typing.
Also, Do you use the following command to execute the para. query
(including dates):

SQL = "EXEC qry_Listings @P1" & varPI

set rs = cnn.execute(SQL)

You must have missed this in my post. I said no concatenation was needed. I
showed the technique I would use:

Actually, you may have to do this (I rarely use Access):
cnn.qInsChkReq Department_ID, Authorizer_ID, Requester_ID, _
Payee, Check_Reason, Amount, CDate(Deadline_Date), Yacht

...Is this good enough - or, is the command object a better choice?

While I prefer the above technique when using Access, IMO, a Command object
is a better choice than concatenating the call to the procedure (I avoid
concatenation whenever possible). When you use concatenation, you have all
the headaches from having to remember to delimit the string and date
parameters, as well as having to escape literal delimiters in your
parameters.

Aaron disagrees. He contends that it is easier to use concatenation because
it allows you to response.write the concatenated statement for debugging
purposes. I disagree: to me, the need to response.write the statement for
debugging is caused by the complication of having to worry about delimiters.
In other words, if you weren't using such an error-prone technique, you
would not have to worry about debugging it as much.

For more of my reasoning, check out this post:
http://tinyurl.com/jifs

HTH,
Bob Barrows
 
A

Aaron Bertrand [MVP]

Aaron disagrees. He contends that it is easier to use concatenation
because
it allows you to response.write the concatenated statement for debugging
purposes. I disagree: to me, the need to response.write the statement for
debugging is caused by the complication of having to worry about
delimiters.

Well, my usual desire for using response.write to debug the SQL statement is
fourfold: to check delimiters for different data types, to check values of
*all* variables in one spot, to copy to Query Analyzer so I can alter the
statement slightly to see why I'm not getting the desired results, and to
copy to Query Analyzer to generate showplan / statistics etc. to see why my
performance stinks. It's not solely because using delimiters is, as you
call it, "error-prone."

A
 
B

Bob Barrows

Aaron said:
Well, my usual desire for using response.write to debug the SQL
statement is fourfold: to check delimiters for different data types,
to check values of *all* variables in one spot, to copy to Query
Analyzer so I can alter the statement slightly to see why I'm not
getting the desired results, and to copy to Query Analyzer to
generate showplan / statistics etc. to see why my performance stinks.
It's not solely because using delimiters is, as you call it,
"error-prone."

A

Understood. Given that I had the first word, I will leave you with the last
word. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top