Professional Web Applications Themes

INSERT error :: Updateable query? - ASP

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 & "'" ...

  1. #1

    Default INSERT error :: Updateable query?

    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 Guest

  2. #2

    Default Re: INSERT error :: Updateable query?

    [url]http://www.aspfaq.com/show.asp?id=2062[/url] - updatable cursor
    [url]http://www.aspfaq.com/show.asp?id=2009[/url] - 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:
    [url]http://tinyurl.com/jiay[/url]
    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 Guest

  3. #3

    Default Re: INSERT error :: Updateable query?

    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" <reb_01501> wrote in message
    news:OrYp1gpXDHA.1744TK2MSFTNGP12.phx.gbl...
    > [url]http://www.aspfaq.com/show.asp?id=2062[/url] - updatable cursor
    > [url]http://www.aspfaq.com/show.asp?id=2009[/url] - 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:
    > [url]http://tinyurl.com/jiay[/url]
    > 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 Guest

  4. #4

    Default Re: INSERT error :: Updateable query?

    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#ZMXpXDHA.2448TK2MSFTNGP09.phx.gbl...
    > [url]www.aspfaq.com[/url]
    >
    > 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" <jasoncatamaranco.com> wrote in message
    > news:%23mu$vTpXDHA.2032TK2MSFTNGP10.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
    >
    >

    jason Guest

  5. #5

    Default Re: INSERT error :: Updateable query?

    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:
    [url]http://tinyurl.com/jifs[/url]

    HTH,
    Bob Barrows


    Bob Barrows Guest

  6. #6

    Default Re: INSERT error :: Updateable query?

    > 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 yzer so I can alter the
    statement slightly to see why I'm not getting the desired results, and to
    copy to Query yzer 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] Guest

  7. #7

    Default Re: INSERT error :: Updateable query?

    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
    > yzer so I can alter the statement slightly to see why I'm not
    > getting the desired results, and to copy to Query yzer 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 Guest

Similar Threads

  1. updateable query
    By Georg Bauer in forum ASP Components
    Replies: 4
    Last Post: September 29th, 04:00 AM
  2. What is updateable Query?
    By Arif Çimen in forum ASP.NET Web Services
    Replies: 1
    Last Post: April 21st, 01:30 PM
  3. Operation must use an updateable query Error
    By Ray P. in forum ASP Database
    Replies: 2
    Last Post: November 30th, 05:14 AM
  4. Replies: 2
    Last Post: July 29th, 07:27 PM
  5. Insert error :: must be updateable?
    By jason in forum ASP
    Replies: 3
    Last Post: July 18th, 10:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139