By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

Newbie(ish) ASP/Access Problem

P: 2
Would somebody please have a look at the following piece of code for me? It is supposed to write a single record to one table in a database and a group of related records to another table. It worked without any problems when I was using Access 97 and a "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("/d1/d101.mdb") connection string. However, since upgrading to Access 2000 it is failing.

The error I get is:
Microsoft JET Database Engine (0x80040E57)
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
/u1/u20/u204.asp, line 78

I know for sure that none of the data exceeds field size and when I check the database manually, I find that the main record and the first of the subsidiary records have been written successfully but the rest have not, so it's obviously getting through the first iteration of the loop but failing on the second.

Any suggestions?

Btw, I'm pretty new to all this stuff so be gentle :)

Expand|Select|Wrap|Line Numbers
  1. 45 set adoConnect=server.createobject("ADODB.connection")
  2. 46 adoConnect.mode = 3
  3. 47 strDBLocation = server.mappath("/d1/d101_2000.mdb")
  4. 48 adoConnect.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strDBLocation
  5. 49
  6. 50 strSQL="INSERT INTO D101a (HorseName,BirthYear,SireName,DamName,StOfOrig,"
  7. 51 strSQL=strSQL & "Colour,Sex,SW,GSW,Active,HasWins,EnteredBy)"
  8. 52 strSQL=strSQL & " VALUES "
  9. 53 strSQL=strSQL & "('" & Request.Form("UserHorseName") & "',"
  10. 54 strSQL=strSQL & Year(Now) - Request.Form("UserHorseAge") & ","
  11. 55 strSQL=strSQL & "'" & Request.Form("UserSireName") & "',"
  12. 56 strSQL=strSQL & "'" & Request.Form("UserDamName") & "',"
  13. 57 strSQL=strSQL & "'" & Request.Form("StOfOrig") & "',"
  14. 58 strSQL=strSQL & "'" & Request.Form("Colour") & "',"
  15. 59 strSQL=strSQL & "'" & Request.Form("Sex") & "',"
  16. 60 strSQL=strSQL & Request.Form("SW") & ","
  17. 61 strSQL=strSQL & Request.Form("GSW") & ","
  18. 62 strSQL=strSQL & Request.Form("Active") & ","
  19. 63 strSQL=strSQL & HasWins & ","
  20. 64 strSQL=strSQL & "'" & EnteredBy & "')"
  21. 65 adoConnect.execute strSQL
  22. 66
  23. 67 if HasWins then
  24. 68   dim index
  25. 69   for index = 1 to request.form("NumWins")
  26. 70     strSQL="INSERT INTO D101b (HorseName,BirthYear,RaceYear,RaceGrade,RaceDist,RaceSurf)"
  27. 71     strSQL=strSQL & " VALUES "
  28. 72     strSQL=strSQL & "('" & Request.Form("UserHorseName") & "',"
  29. 73     strSQL=strSQL & "'" & Year(Now) - Request.Form("UserHorseAge") & "',"
  30. 74     strSQL=strSQL & "'" & Request.Form("RaceYear" & index) & "',"
  31. 75     strSQL=strSQL & "'" & Request.Form("RaceGrade" & index) & "',"
  32. 76     strSQL=strSQL & "'" & Request.Form("RaceDist" & index) & "',"
  33. 77     strSQL=strSQL & "'" & Request.Form("RaceSurf" & index) & "')"
  34. 78     adoConnect.execute strSQL
  35. 79   next
  36. 80 end if
  37. 81 
  38. 82 adoConnect.close
  39. 83 set adoConnect = nothing
May 16 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,638
Would somebody please have a look at the following piece of code for me? It is supposed to write a single record to one table in a database and a group of related records to another table. It worked without any problems when I was using Access 97 and a "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("/d1/d101.mdb") connection string. However, since upgrading to Access 2000 it is failing.

The error I get is:
Microsoft JET Database Engine (0x80040E57)
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
/u1/u20/u204.asp, line 78

I know for sure that none of the data exceeds field size and when I check the database manually, I find that the main record and the first of the subsidiary records have been written successfully but the rest have not, so it's obviously getting through the first iteration of the loop but failing on the second.

Any suggestions?

Btw, I'm pretty new to all this stuff so be gentle :)

45 set adoConnect=server.createobject("ADODB.connection")
46 adoConnect.mode = 3
47 strDBLocation = server.mappath("/d1/d101_2000.mdb")
48 adoConnect.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strDBLocation
49
50 strSQL="INSERT INTO D101a (HorseName,BirthYear,SireName,DamName,StOfOrig,"
51 strSQL=strSQL & "Colour,Sex,SW,GSW,Active,HasWins,EnteredBy)"
52 strSQL=strSQL & " VALUES "
53 strSQL=strSQL & "('" & Request.Form("UserHorseName") & "',"
54 strSQL=strSQL & Year(Now) - Request.Form("UserHorseAge") & ","
55 strSQL=strSQL & "'" & Request.Form("UserSireName") & "',"
56 strSQL=strSQL & "'" & Request.Form("UserDamName") & "',"
57 strSQL=strSQL & "'" & Request.Form("StOfOrig") & "',"
58 strSQL=strSQL & "'" & Request.Form("Colour") & "',"
59 strSQL=strSQL & "'" & Request.Form("Sex") & "',"
60 strSQL=strSQL & Request.Form("SW") & ","
61 strSQL=strSQL & Request.Form("GSW") & ","
62 strSQL=strSQL & Request.Form("Active") & ","
63 strSQL=strSQL & HasWins & ","
64 strSQL=strSQL & "'" & EnteredBy & "')"
65 adoConnect.execute strSQL
66
67 if HasWins then
68 dim index
69 for index = 1 to request.form("NumWins")
70 strSQL="INSERT INTO D101b (HorseName,BirthYear,RaceYear,RaceGrade,RaceDist,R aceSurf)"
71 strSQL=strSQL & " VALUES "
72 strSQL=strSQL & "('" & Request.Form("UserHorseName") & "',"
73 strSQL=strSQL & "'" & Year(Now) - Request.Form("UserHorseAge") & "',"
74 strSQL=strSQL & "'" & Request.Form("RaceYear" & index) & "',"
75 strSQL=strSQL & "'" & Request.Form("RaceGrade" & index) & "',"
76 strSQL=strSQL & "'" & Request.Form("RaceDist" & index) & "',"
77 strSQL=strSQL & "'" & Request.Form("RaceSurf" & index) & "')"
78 adoConnect.execute strSQL
79 next
80 end if
81
82 adoConnect.close
83 set adoConnect = nothing
One or more Fields that you are Appending Data to may be too small for the value. For instance, if you have the [Sex] Field in Table D101a defined as TEXT 1 (single character M or F), and try to append 'Male' to this Field, you will get this Error. Check all your Field sizes against the data to be appended.
May 17 '07 #2

P: 2
Thanks for taking the time to reply but unfortunately, it isn't invalid data that's causing the problem (I wish it was that simple). No, most of the input, like Sex etc, is controlled by dropdowns in an HTML form and in the few fields where the user is invited to type their own input, I've got decent validation routines in place to block out of range data. The problem lies elsewhere.

Not to worry though. As a temporary fix, I've gone back to my original Access 97 version which still works without problem.

Thanks again,
Tom.
May 18 '07 #3

nico5038
Expert 2.5K+
P: 3,072
The Jet-engine has been rewritten for A2000 and higher versions, so the problem might be in the Jet-engine.
I would start with setting a breakpoint in the errorline and use the immediate window to print the strSQL like:
? strSQL
Then use the resulting string to paste in the target database in a new query.
Activate the query and see what message is triggered.

Nic;o)
May 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.