Professional Web Applications Themes

strange checkbox yes/no field problem - ASP Database

Hi, I'm getting an error updating an Access record that has a YES/No Field. If I leave the checkbox unchecked I get this error: Error Type: Provider (0x80040E21) Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Strange thing is I have the same code on other pages that works fine. I've worked through it methodically to see where the differences lie but to no avail. Here is the code that writes the check box to the page: <% If recSet1("status") = True Then Response.Write "<input type=checkbox name=status value=True checked>" Else ...

  1. #1

    Default strange checkbox yes/no field problem

    Hi,

    I'm getting an error updating an Access record that has a YES/No Field.
    If I leave the checkbox unchecked I get this error:

    Error Type:
    Provider (0x80040E21)
    Multiple-step OLE DB operation generated errors. Check each OLE DB status
    value, if available. No work was done.

    Strange thing is I have the same code on other pages that works fine. I've
    worked through it methodically to see where the differences lie but to no
    avail.

    Here is the code that writes the check box to the page:

    <% If recSet1("status") = True Then
    Response.Write "<input type=checkbox name=status value=True checked>"
    Else
    Response.Write "<input type=checkbox name=status value=True>"
    End If
    %>

    After that I just do the standard recSet("status") = request("status") to
    update the record

    Darren


    Darren Guest

  2. #2

    Default Re: strange checkbox yes/no field problem

    I think a few more details may be necessary, but one thing, recSet("status")
    = request("status") is not (or should not be) a standard way of doing
    things. If you want to update a record or records, execute an UPDATE
    non-query.

    sSQL = "UPDATE myTable SET [Status]=True WHERE RecordID=30"

    Also, are you using an ODBC connection or an OLEDB connection? Can you show
    us your connection string?

    Another non-standard thing is your use of request("status"). What happens
    if someone goes to yourpage.asp?status=BLAHBLAHBLAH. What is the value of
    request("status") then? Is it what's in the form collection? The
    querystring? Cookies maybe? Always specify the collction you mean.
    http://www.aspfaq.com/show.asp?id=2111

    Ray at home

    "Darren Heinrich" <com.au> wrote in message
    news:da37d.25$ozemail.com.au... 


    Ray Guest

  3. #3

    Default Re: strange checkbox yes/no field problem

    here are the relevant bits of code Ray - thanks
    I hope it makes sense. I'm very much a part-timer, if you couldn't tell
    already....

    Darren


    Dim Cn,recSet1,connStr,bookmark
    Dim Query1
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    Server.MapPath("../../database/repairs.mdb")
    Set Cn=Server.CreateObject("adodb.Connection")
    Set recSet1=Server.CreateObject("adodb.Recordset")
    Cn.Open connStr

    Query1 = "Select repairID, item, problemDesc, solutionDesc, firstName,
    lastName, email, bestPhone, price, status FROM tblRepairs INNER JOIN
    tblCustomers ON tblCustomers.custID = tblRepairs.custID WHERE repairID = " &
    Request("repairID")
    recSet1.open Query1,Cn,adLockOptimistic,adOpenStatic
    %>



    <% ' Edit the data in db,
    recSet1("status") = Request("status")
    recSet1.Update
    %>


    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
    message news:phx.gbl... 
    recSet("status") 
    show [/ref]
    status [/ref]
    I've [/ref]
    no [/ref]
    to 
    >
    >[/ref]


    Darren Guest

  4. #4

    Default Re: strange checkbox yes/no field problem

    Darren Heinrich wrote: 

    Since your query involves two tables, your recordset may not be updatable.
    Which table has the status field? If I can't tell, why do you think ADO is
    going to be able to figure it out? Because only one table has the status
    field? That may be, but if you have a 1-to-many relationship, and the status
    is in the table on the "many" side of that relationship, ADO will likely
    have a problem figuring out which record to update.

    I'll assume it's the tblRepairs table that has the status column.
     

    Again, to reiterate what Ray said, this is not the best way to do data
    modifications. Recordsets should only be used to retrieve read-only data for
    display. Use sql DML statements for data modification. in addition, you need
    to supply 0 (False) or -1 (True) to the statement, instead of the words
    "true" and "false". You do not need to open a wasteful, inefficient
    recordset at all for this. For this example, I will use dynamic sql to
    illustrate it, but I recommend you switch to using saved parameter queries.
    See the links in my PS

    if len(Request("status")) > 0 then
    bStatus = -1
    else
    bStatus = 0
    end if
    sql="update tblRepairs set status=" & bStatus " & _
    " where repairID = " & Request("repairID")
    on error resume next
    cn.execute sql,,129
    'check for errors here
    if err <> 0 then
    'handle the error that occurred

    Bob Barrows

    PS.
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl

    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP1 1.phx.gbl

    http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gb l&oe=UTF-8&output=gplain

    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl


    http://tinyurl.com/jyy0


    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gb l&rnum=11&prev=/groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barro ws%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

Similar Threads

  1. Disable checkbox when field value = null
    By SRuby in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: September 27th, 07:16 PM
  2. Replies: 4
    Last Post: June 13th, 02:29 AM
  3. Show Field based on Checkbox
    By -- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 22nd, 02:55 PM
  4. matching a querystring to a checkbox field?
    By Nathon Jones in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 12th, 03:06 PM
  5. Updating an Access DB Yes/No field with a Checkbox value
    By Harry Devine in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 28th, 04:44 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