INSERT error :: Updateable query?

[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)

[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.
jason wrote:

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)

[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" <re*******@yahoo.com> wrote in message
news:Or**************@TK2MSFTNGP12.phx.gbl...

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.
jason wrote:

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)

[jason]

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

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:u5**************@TK2MSFTNGP09.phx.gbl...

www.aspfaq.com

Also, don't do:
Set rs = cnn.Execute(SQL)
Just do:
cnn.Execute SQL

Ray at home

--
Will trade ASP help for SQL Server help
"jason" <ja***@catamaranco.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

I am picking up an error message

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

[Bob Barrows]

jason wrote:

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:

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

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

[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

[Bob Barrows]

Aaron Bertrand [MVP] wrote:

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

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