?
Solved

ASP Mysql Error Query-based update failed because the row to update could not be found.

Posted on 2006-05-21
17
Medium Priority
?
594 Views
Last Modified: 2008-02-20
Ok this is really starting to tick me off I am getting the below error message when trying to update a recordset as shown below. I need it use recordset please lets not go into suggesting going with straight SQL because that is just not an option here. I need this to be done using recordsets. Please does anyone have any suggestions. To help out on this I did experience this same issue earlier with another query but that one was resolved by instead of SELECTing all records from that table I only SELECTED the rows that were being updated. That resolved that one but 0n this one I have attempted to do that and it will not work for me.  PLEASE A I NEED  ASSISTENCE

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Query-based update failed because the row to update could not be found.

/register1.asp, line 92
 

Line 92 is rs.updatebatch

      sql="select * from members where id="&dd2
      rs.Open sql,conn,1,3

      rs("sex")=dfsex
      rs("hesex")=dfhesex
      rs("relation")=dfrelation
      rs("sexp")=dfsexp
      rs("dob")=dfdob
      rs("byear")=dfyear
      rs("marital")=dfmarital
      rs("children")=dfchildren
      rs("wantchildren")=dfwantchildren
      rs("city")=dfcity
      rs("state")=dfstate
      rs("country")=dfcountry
      rs("zip")=dfzip
      rs("phone")=dfphone
      rs("ethnicity")=dfethnicity
      rs("religion")=dfreligion
      rs("zodiac")=dfzodiac
      rs("education")=dfeducation      
      rs("occupation")=dfoccupation
      rs("income")=dfincome
      rs("smoking")=dfsmoking
      rs("drinking")=dfdrinking
      rs("cuisine")=dfcuisine
      rs("languages")=dflanguages
      rs("feet")=dffeet
      rs("inches")=dfinches
      rs("weight")=dfweight
      rs("bodytype")=dfbodytype
      rs("haircolor")=dfhaircolor
      rs("hairstyle")=dfhairstyle
      rs("bodyart")=dfbodyart
      rs("eyecolor")=dfeyecolor
      rs.updatebatch
0
Comment
Question by:Scripter25
  • 5
  • 5
  • 4
  • +1
17 Comments
 

Author Comment

by:Scripter25
ID: 16730518
By the way I am using ASP CLASSIC, Windows 2003 server, MYSQL 5.0, MYODBC 3.51
Everything worked prior to about two or so weeks ago from what I can gather with no updates to the system.
This as you can see is extremely frustrating all I can find on MySQL site is them blaming it on MS and MS site Blames it on MYSQL

Point finger here point Finger there

and
 everything I have found on here even though the error seems to have hit many people there has not been a very good solution to what I am needing atleast nothing that I can find
0
 

Author Comment

by:Scripter25
ID: 16730559
Ok I am leaving this open but I have found a work around. I am leaving this open in hopes someone can give me a reason as to why this happens so that I can if nothing else better understand this.
here is the work around

The original query was
sql="select * from members where id="&dd2

The Work around Query is
sql="select Sex,hesex,relation,sexp,dob,byear,marital,children,wantchildren,city,state,country,zip,phone,ethnicity,religion,zodiac,education,occupation,income,smoking,drinking,cuisine,languages,feet,inches,weight,bodytype,haircolor,hairstyle,bodyart,eyecolor from members where id="&dd2

so once again instead of trying to pull all rows just select the ones that you are going to be updating and go from there. This is annoying and I think if someone could provide a good understanding as to why this is happening you would give great assistence to not only me but to many people out there. Remember 500 points for the person that can provide a logical (perferably documented) explaination as to why this has to be done this way and possibly what has caused this changed. As I stated before this used to work just fine and I have always coded that way.

Thank you
0
 
LVL 7

Expert Comment

by:chisholmd
ID: 16730810
I'm not sure why using the wildcard didn't work for you but next time try this.


 rs.Open sql,conn,1,3
IF NOT RS.EOF then
     rs("sex")=dfsex
     ....
ELSE
    response.write "Record not found"
END IF
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:kevp75
ID: 16734324
the only thing I can think of off the top of my point (head) is that dd2 is not getting set, or is getting lost in other code.  Otherwise your first post should work
0
 
LVL 14

Expert Comment

by:CyrexCore2k
ID: 16743576
Okay I'm not going to recommend that you go with straight SQL as production BUT... you might try straight SQL so that you have a chance to see what is going on for testing. It might give you some clues as to what is going on.
0
 

Author Comment

by:Scripter25
ID: 16743701
There are no clues that I can see from that. I know this much and this much only. It appears that if you have 10 fields in a query but you only try to update 8 of them it will mess up because it thinks that you should be updating all 10. so if you re-do the query to only allow the recordset to update the 8 fields then it will update just fine. This is a bit crazy to me.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16743890
what about converting them all to strings?
for example.... rs("religion")=str(dfreligion)
0
 

Author Comment

by:Scripter25
ID: 16744083
First thing is first I appreciate your attepts at this but if I was to convert my data to something it is not it would defeat the purpose of doing this. If it works by using the query of
sql="select Sex,hesex,relation,sexp,dob,byear,marital,children,wantchildren,city,state,country,zip,phone,ethnicity,religion,zodiac,education,occupation,income,smoking,drinking,cuisine,languages,feet,inches,weight,bodytype,haircolor,hairstyle,bodyart,eyecolor from members where id="&dd2


But this query does not work
sql="select * from members where id="&dd2

When they are both for all intensive purposes the same thing then even if changing the data type did work there should be an explaination as to why
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16744289
all we are doing is giving you ideas that would normally work....no need to get snippy about it.
and converting them to strings would only insure that they are all converted to text, since it looks like you are trying to update text fields.

as far as you select statement goes, there is no obvious reason why * does not work.

can you try this out

sql1="select * from members where id="&dd2
sql2="select Sex,hesex,relation,sexp,dob,byear,marital,children,wantchildren,city,state,country,zip,phone,ethnicity,religion,zodiac,education,occupation,income,smoking,drinking,cuisine,languages,feet,inches,weight,bodytype,haircolor,hairstyle,bodyart,eyecolor from members where id="&dd2

response.write(sql1&"<br />")
response.write(sql2&"<br />")

set rs1=conn.execute(sql1)
set rs2=conn.execute(sql2)
if rs1.eof then
  response.write("There are no records with that id ("&dd2&")")
else
  response.write(sql1)
end if
if rs2.eof then
  response.write("There are no records with that id ("&dd2&")")
else
  response.write(sql2)
end if
set rs1=nothing
set rs2=nothing


and tell us what it comes back with
0
 
LVL 14

Accepted Solution

by:
CyrexCore2k earned 1500 total points
ID: 16747217
Well if it wants you to update all 10 fields then just set them equal to themselves? A little annoying but wrapper objects all tend to have their shortcomings.
0
 
LVL 7

Expert Comment

by:chisholmd
ID: 16747787
I can't imagine why it does not work for you.

Check out:
 http://lonecrow.net/splittest.asp

 Full example and source.  Works fine

0
 
LVL 7

Expert Comment

by:chisholmd
ID: 16747792
Whoops! Right answer wrong  question :) I'll go post that in the correct thread now
0
 

Author Comment

by:Scripter25
ID: 16925454
Sorry for my tardiness on getting back to this posting. The Best answer was provided by CyrexCore2k. He stated the Wrapper objects have their shortcummings. This I have found to be true. Even though there was no true answer as to why it does not work I figure everyone has waited to long for a descision so someone should get the points and since CyrexCore2k was at least on the correct page with that statement he is awarded the points.

I appreciate everyones help on this. and I do appologize if I seemed a bit rude with my previous post but it was that I was just in a rush that day and well you know how it goes.

Thank you again
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16925558
well....you know, thanks for at least trying the other suggestions here...
0
 
LVL 7

Expert Comment

by:chisholmd
ID: 16925708
>>>
Well if it wants you to update all 10 fields then just set them equal to themselves? A little annoying but wrapper objects all tend to have their shortcomings.
>>>

I know this question is closed but I can't help pointing out how rudendant this line of reasoning is. When you create a recordset object with the select statement all the fields ALREADY have values even if they happen to be null. (In which case null values are allowed right because you just pulled them from the table.)

The fact that the script only CHANGES the values of some of them does not magically remove the existing values from the other two.

Sorry if this sounds snotty, it isn't meant to. It is just that the accepted answer isn't an answer to anything at all.

I think I would have closed this question and reposted it in the mySQL area to see if anyone there had run into this before.

OK so I have insomnia, I'll log off now and try to sleep again.

0
 
LVL 14

Expert Comment

by:CyrexCore2k
ID: 16925904
Chisholmd,

Since Fields().Value is a property and not a publicly exposed variable my line of reasoning was that perhaps there is a flag the property sets when accessed. Say

Property Let Value(Val as Variant)
         Changed = True
         _internal_value = val
End Property

I know this probably isn't the case but frankly I've never seen the issue before which usually prompts me to try something that sounds ridiculous just to test. /shrugs

Anyway it was just an idea.
0
 
LVL 7

Expert Comment

by:chisholmd
ID: 16926802
Perhaps and sorry if I sounded a little condecending it was very late at night and I was annoyed that I couldn't sleep :)    If there is a bug in the library I would look to the mySQL odbc library first.



0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question