ACC97: Data Truncated When Using Active Server Pages

• Microsoft Access 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When using Microsoft Active Server Pages (ASP) to add data to a Microsoft Access database, data is truncated in some fields.

CAUSE

The Method attribute of the Form tag is set to Get in the ASP form, and the combined length of the data in fields you are editing plus the length of their field names exceeds the number of characters allowed on the address line of your Internet browser.

MORE INFORMATION

When you set the Method attribute of the Form tag to Get, the ASP form passes parameters containing the data via the Internet browser's address line. This limits the number of characters that an ASP form can submit to the database to the maximum number of characters allowed on your Internet browser's address line. Because ASP forms pass other parameters on the address line, such as the name of the field the data is being written to, the actual data written to the database will be less than the amount allowed on the browser's address line.

Steps to Reproduce Behavior

  1. Create a System DSN on your Web server that points to the Northwind
     sample database. For more information about creating a System DSN,
     please see the following article in the Microsoft Knowledge Base:

        ARTICLE-ID: Q159682
        TITLE     : ACC97: "Data Source Name Not Found" Err Msg Opening Web
                    Page

  2. Start Microsoft Access 97 and open the sample database Northwind.mdb.

  3. Create the following table:

        Table: tblASPTest

        Field Name: MemoText
        Data Type: Memo
        Indexed: No

     Save the table as tblASPTest. When asked if you want to create a
     Primary Key, click No.

  4. Create a module and type the following line in the Declarations
     section:

        Option Explicit

  5. Type the following procedure in the module you just created:

        Sub FillMemo()

          Dim db As Database
          Dim rs As Recordset
          Dim intCount1 As Integer, intCount2 As Integer
          Set db = CurrentDb
          Set rs = db.OpenRecordset("tblASPTest")
          With rs
             .AddNew
             ![MemoText] = "A"
             For intCount1 = 0 To 25
                For intCount2 = 1 To 100
                   ![MemoText] = ![MemoText] & Chr(intCount1 + 65)
                Next intCount2
             Next intCount1
             .Update
          End With
          db.Close

        End Sub

     This procedure fills the memo field in the first record with 2601
     characters.

  6. Create a new select query based on tblASPTest. In the first field in
     the query grid, type the following expression:

        Len([MemoText])

  7. Save the query as qryASPTest.

  8. Run the query and notice that it returns 2601.

  9. Close the query.

 10. In the database window, click the Forms tab, and then click New.

 11. In the New Form dialog box, click AutoForm: Columnar, and type or
     select tblASPTest.

 12. Close the form, and save it as frmASPTest.

 13. Click frmASPTest in the Database window, and then click Save As/Export
     on the File menu.

 14. In the Save As dialog box, click "To an External File or Database,"
     and then click OK.

 15. In the "Save 'frmASPTest' In" dialog box, click "Microsoft Active
     Server Pages (*.asp)" in the Save As Type list, and type
     frmASPTest.asp in the File Name box. Select a folder to store the
     files, and then click Export.

 16. In the "Microsoft Active Server Pages Output Options" dialog box, type
     the name of a System DSN on your Web server that points to the
     Northwind sample database in the Data Source Name box.

 17. In the Server URL box, type the Uniform Resource Locator (URL) that
     points to the Web Server location where your ASP files will be stored,
     and then click OK.

     For example, if you store the ASP files in the \ASPsamp folder on the
     \\PubTest server, type "http://pubtest/aspsamp/" (without the
     quotation marks) as your Server URL. Microsoft Access creates the
     files frmASPTest.asp and frmASPTestalx.asp.

 18. Start Microsoft Internet Explorer, or another Web browser program.

 19. Type the URL in the address box of your Web browser to view
     frmASPTest.asp.

     For example, if you saved the .asp file in a folder called Test in the
     wwwroot folder of your Web Server, type:

        http://<servername>/test/frmASPTest.asp

     Note that the URL depends upon where your files are located on the Web
     server.

 20. Add one character to the memo field displayed in the browser, and then
     click Commit.

     After you have committed the record, note that the address box
     contains not only the actual data, but also additional information,
     such as the name of the field.

 21. Click Refresh.

 22. Return to Microsoft Access and open qryASPTest in Northwind.mdb.

     Note that the query no longer returns 2601. The number you see is the
     maximum number of characters that can be returned to the database by
     the browser minus any non-data characters, such as field names, which
     were needed to return the data to the database.

   ARTICLE-ID: Q153976
   TITLE     : Text Sent Via Forms Command Is Truncated

REFERENCES

For more information about Microsoft Active Server Pages, search the Help Index for "ASP files," or ask the Microsoft Access 97 Office Assistant.