Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > INSERT error :: Updateable query?

Reply
Thread Tools

INSERT error :: Updateable query?

 
 
jason
Guest
Posts: n/a
 
      08-09-2003
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)


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      08-09-2003
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)




 
Reply With Quote
 
 
 
 
jason
Guest
Posts: n/a
 
      08-09-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)

>
>
>



 
Reply With Quote
 
jason
Guest
Posts: n/a
 
      08-09-2003
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#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23mu$(E-Mail Removed)...
> > 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

>
>



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      08-09-2003
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


 
Reply With Quote
 
Aaron Bertrand [MVP]
Guest
Posts: n/a
 
      08-09-2003
> 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


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      08-09-2003
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Operation must use an updateable query error John ASP .Net 1 10-21-2004 06:43 AM
Operation must use an updateable query Error!!! =?Utf-8?B?S2Vu?= ASP .Net 6 02-13-2004 03:45 PM
Operation must use an updateable query Error!!! =?Utf-8?B?S2Vu?= ASP .Net 0 02-13-2004 01:46 PM
Operation must use an updateable query error. abdul haleem ASP .Net 3 10-19-2003 05:53 PM
Insert error :: must be updateable? jason ASP General 3 07-18-2003 10:17 PM



Advertisments