Another Query is too complex question

Results 1 to 4 of 4

Thread: Another Query is too complex question

  1. #1
    Elaine Pack Guest

    Default Another Query is too complex question

    Code snippet:<BR><BR>PassedInValues = request("ValuesFromPreviousProgram")<BR>PassedInDa te =request("DateFromPrevProgram")<BR>mySQL="SELECT * FROM myTable WHERE SomeTableValue=PassedInValue"<BR>set rs=server.createobject ("ADODB.recordset")<BR>rs.cursorlocation=3<BR>rs.o pen mySQL,cn,3,3,1<BR>cn.begintrans<BR>rs.addnew<BR>rs .fields ("VariousTableValues") = PassedInValues [etc]<BR>if isdate(PassedInDate) = true then &#060;--tests true<BR> rs.fields("TableDate") = cdate(PassedInDate) <BR>end if<BR>rs.update &#060;--error here<BR>cn.committrans<BR>rs.close<BR>set rs=nothing<BR><BR>myTable has 46 fields (including the autonumbered key field). The data has been requested from the preceding form program, and updates the db nicely using the code snippet above. However, if I have a valid date in PassedInDate, I get a "Query is too complex" error on the update statement. If I don&#039;t have anything in the date field, it works fine. I&#039;ve triple checked (and had another set of eyes on it) that the Access 97 db fields and the ODBC are correctly defined, so I&#039;m confident that part is OK. In another part of the posting program, I create other smaller but related table records with dates and they work fine. <BR><BR>I&#039;m aware there is a limit on the number of fields to the SQL statement (40?), but when it&#039;s done like the above (select * instead of insert), does that limit still count? Does the count only include variables with data in them? I can put data in each and every field except the 3 date fields on this particular table and it works fine. That&#039;s still 42 fields not counting the key...<BR><BR>Any ideas or suggestions, anyone? Normalizing the database, which has been suggested in other posts, doesn&#039;t apply here--there&#039;s no repeated data.<BR><BR><BR>Thanks<BR>

  2. #2
    Join Date
    Dec 1969
    Los Angeles, CA

    Default RE: Another Query is too complex question

    Did not really read all you wrote BUT<BR><BR> mySQL,cn,2,3,1<BR><BR>notice that i have changed the 3 ot a 2 you need a keyset or a dynamic cursor to use the .addnew and update. I really do not understand how you say that will work in somecases with a static cursor<BR><BR><BR>there is no limit on the fields to be 40 i think in 6.5 it was 255 but in 7 i dont think that is the limit anymore<BR><BR>as for the date problem...i really did not understand what you were trying to say<BR><BR>

  3. #3
    Elaine Pack Guest

    Default RE: Another Query is too complex question

    Changed the cursor to 2. Didn&#039;t correct my original problem, but will leave it at 2. The original example I modeled this on had a cursor of 3, and all the other programs I did this way worked fine, FWIW. I had wondered about this tho :) so will leave it at 2 in the future. Thanks for that tip!<BR><BR>I&#039;m not using SQL Server, it&#039;s a plain old Access 97 ODBC. I assume that&#039;s what you are referring to with "6.5" and "7"? <BR><BR>The date problem is that if there&#039;s a valid date in one field, it fails with the error message. If not, it works correctly.<BR><BR>Thanks

  4. #4
    Elaine Pack Guest

    Default RE: Another Query is too complex question SOLVED

    I wound up having to split the table into two parts. I still don&#039;t understand why having data in the date fields made it get too big, apparently, but whatever :) It does work now.<BR><BR>Thanks.

Posting Permissions

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