INSERT error :: Updateable query?

Discussion in 'ASP General' started by jason, Aug 9, 2003.

  1. jason

    jason Guest

    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, Aug 9, 2003
    #1
    1. Advertising

  2. jason

    Bob Barrows Guest

    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)
    Bob Barrows, Aug 9, 2003
    #2
    1. Advertising

  3. jason

    jason Guest

    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" <> wrote in message
    news:...
    > 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, Aug 9, 2003
    #3
  4. jason

    jason Guest

    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#...
    > 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" <> wrote in message
    > news:%23mu$...
    > > 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

    >
    >
    jason, Aug 9, 2003
    #4
  5. jason

    Bob Barrows Guest

    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
    Bob Barrows, Aug 9, 2003
    #5
  6. > 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
    Aaron Bertrand [MVP], Aug 9, 2003
    #6
  7. jason

    Bob Barrows Guest

    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. :)
    Bob Barrows, Aug 9, 2003
    #7
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. abdul haleem
    Replies:
    3
    Views:
    419
    Wim Hollebrandse
    Oct 19, 2003
  2. =?Utf-8?B?S2Vu?=

    Operation must use an updateable query Error!!!

    =?Utf-8?B?S2Vu?=, Feb 13, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    1,280
    Hans Kesting
    Feb 13, 2004
  3. =?Utf-8?B?S2Vu?=

    Operation must use an updateable query Error!!!

    =?Utf-8?B?S2Vu?=, Feb 13, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    328
    =?Utf-8?B?S2Vu?=
    Feb 13, 2004
  4. John
    Replies:
    1
    Views:
    412
    =?Utf-8?B?S2F1c3Rhdg==?=
    Oct 21, 2004
  5. jason

    Insert error :: must be updateable?

    jason, Jul 18, 2003, in forum: ASP General
    Replies:
    3
    Views:
    86
    jason
    Jul 18, 2003
Loading...

Share This Page