Newbie(ish) ASP/Access Problem

[Tom McQ]

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={Micros oft 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,RaceSurf)"
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

[ADezii]

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={Micros oft 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=serv er.createobject ("ADODB.connect ion")
46 adoConnect.mode = 3
47 strDBLocation = server.mappath( "/d1/d101_2000.mdb")
48 adoConnect.open "PROVIDER=MICRO SOFT.JET.OLEDB. 4.0;DATA SOURCE=" & strDBLocation
49
50 strSQL="INSERT INTO D101a (HorseName,Birt hYear,SireName, DamName,StOfOri g,"
51 strSQL=strSQL & "Colour,Sex,SW, GSW,Active,HasW ins,EnteredBy)"
52 strSQL=strSQL & " VALUES "
53 strSQL=strSQL & "('" & Request.Form("U serHorseName") & "',"
54 strSQL=strSQL & Year(Now) - Request.Form("U serHorseAge") & ","
55 strSQL=strSQL & "'" & Request.Form("U serSireName") & "',"
56 strSQL=strSQL & "'" & Request.Form("U serDamName") & "',"
57 strSQL=strSQL & "'" & Request.Form("S tOfOrig") & "',"
58 strSQL=strSQL & "'" & Request.Form("C olour") & "',"
59 strSQL=strSQL & "'" & Request.Form("S ex") & "',"
60 strSQL=strSQL & Request.Form("S W") & ","
61 strSQL=strSQL & Request.Form("G SW") & ","
62 strSQL=strSQL & Request.Form("A ctive") & ","
63 strSQL=strSQL & HasWins & ","
64 strSQL=strSQL & "'" & EnteredBy & "')"
65 adoConnect.exec ute strSQL
66
67 if HasWins then
68 dim index
69 for index = 1 to request.form("N umWins")
70 strSQL="INSERT INTO D101b (HorseName,Birt hYear,RaceYear, RaceGrade,RaceD ist,RaceSurf)"
71 strSQL=strSQL & " VALUES "
72 strSQL=strSQL & "('" & Request.Form("U serHorseName") & "',"
73 strSQL=strSQL & "'" & Year(Now) - Request.Form("U serHorseAge") & "',"
74 strSQL=strSQL & "'" & Request.Form("R aceYear" & index) & "',"
75 strSQL=strSQL & "'" & Request.Form("R aceGrade" & index) & "',"
76 strSQL=strSQL & "'" & Request.Form("R aceDist" & index) & "',"
77 strSQL=strSQL & "'" & Request.Form("R aceSurf" & index) & "')"
78 adoConnect.exec ute strSQL
79 next
80 end if
81
82 adoConnect.clos e
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.

[Tom McQ]

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.

[nico5038]

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)