INFO: DFW Filter on Char Datatype Results in No Records Found

ID: Q189201

The information in this article applies to:

SUMMARY

Using the Data Form Wizard with a field of char datatype can result in no records found when filtered. This happens when the string used to filter is not the exact length specified for the char datatype field in the SQL Server table.

With SQL Server, a char datatype has a specific character length and every piece of data put into that field must have that length. If it does not have that length, it is in essence padded with trailing spaces to make it the specified length. On the other hand, the varchar datatype also has a specific character length, but it allows the contents of the field to be less than or equal to that length.

When filtering in the Data Form Wizard the filter value is truncated of all leading or trailing spaces and therefore if it contains fewer characters than the specified char type length, no matches will be found. By changing the datatype of the field to varchar the same filter will return records.


MORE INFORMATION

The easiest way to change the datatype of a field on SQL Server is through the Visual InterDev Visual Data Tools. Use the following steps to change a datatype with Visual Data Tools.

These steps assume the user has a Web project with a data connection open in Visual InterDev:

  1. Go to the Data View tab.


  2. Expand a SQL Server data connection to the desired table.


  3. Right-click the table and select "Design."


  4. Click the datatype column of the desired field and select and datatype from the drop-down list.


  5. Save the table by clicking on the "single disk" icon, pressing Ctrl-S, or click Save from the File menu.


Additional query words: 


Keywords          : kbASP kbVisDBTools kbVisID100 kbGrpASP 
Version           : WINDOWS:1.0; winnt:
Platform          : WINDOWS winnt 
Issue type        : kbinfo

Last Reviewed: May 27, 1999