ASP from Access 2000 Query (Access2000 SR1)

Topic

New Reply

ASP generated from database query containing wildcards fails to load and times out.
I have generated ASP from a query which has a filter in one column ( Like “*bone*” ) the field being filtered may have data such as (bone) or (bone, charcoal) or (ash, bone, charcoal) etc. This filter pulls all the records containing bone in any combination.

However, the ASP fails to load and the script times out . I have removed wildcards from other queries and this has solved the problem but on some I cannot remove the wildcard, or at least I can’t find a way to pull the same records without using them.

Is there any way to modify the filter in the DB to prevent this problem with ASP.

Seoras

Reply | Quote

Replies

When you are using ASP, I’m not sure I understand you use of the term filter. In the ASP enviornment, you use fully formed SQL statements, so do you have a WHERE clause in the SQL that includes the Like criteria?

Reply | Quote

When you are using ASP, I’m not sure I understand you use of the term filter. In the ASP enviornment, you use fully formed SQL statements, so do you have a WHERE clause in the SQL that includes the Like criteria?

Reply | Quote

Not sure what “generated ASP from a query” exactlly signifies, w/o more details only thing I can suggest is that if you are using ADO to generate a recordset, you have to use the “standard” SQL wildcard character, which is a percent sign (%), not an asterisk (*). So in the SQL statement being used to “generate ASP”, you may want to try replacing the asterisks with percent signs. Other than that, have no suggestions, as do not have very much experience with ASP.

HTH

Reply | Quote

In the Access DB I have a query which uses ( Like “*Bone*” ) to pull out all records with Bone in the that particular column.

In Access I exported the query as an ASP. But the ASP fails to load in the browser and times out. I knew this had something to do with the wildcard * used in the Access DB but did not know how to resolve it.

I have now edited the exported ASP and replaced all * in the SQL statement with % and this has solved the problem.

I still cannot understand why Access uses * as a wildcard in a query but if you export the query as ASP it still uses the * .

Cheers
Seoras

Reply | Quote

The main explanation for this is, ASP uses ADO, and ADO uses standard ANSI SQL rather than Jet SQL used by Access. See MSKB article 162977:

ACC97: ASP Query Cannot Be Used with the LIKE Predicate

Article states in part:

CAUSE:
“Because ASP files use ActiveX Data Objects (ADO) to communicate with ODBC drivers in order to query the back-end data, the SQL statements they contain have different character requirements than typical Microsoft Access SQL statements. ASP files use the percent sign (%) as a wildcard character in SQL statements whereas Microsoft Access uses the asterisk (*). ”

RESOLUTION:
“Edit the SQL statements in the ASP files so that they use the percent sign (%) for wildcards. If parameter queries with wildcards are exported, edit the SQL statements in the ASP file so that they use the percent sign (%) as the wildcard along with the correct sequence and number of delimiters around the wildcard.”

See article for more details. Also see Access Help topics, “Comparison of Microsoft Jet SQL and ANSI SQL” and “Using Wildcard Characters in String Comparisons” for further explanation. Recommend use the ANSI wildcard characters (% and _ ) for any SQL that is being used for ASP pages.

HTH

Reply | Quote

The main explanation for this is, ASP uses ADO, and ADO uses standard ANSI SQL rather than Jet SQL used by Access. See MSKB article 162977:

ACC97: ASP Query Cannot Be Used with the LIKE Predicate

Article states in part:

CAUSE:
“Because ASP files use ActiveX Data Objects (ADO) to communicate with ODBC drivers in order to query the back-end data, the SQL statements they contain have different character requirements than typical Microsoft Access SQL statements. ASP files use the percent sign (%) as a wildcard character in SQL statements whereas Microsoft Access uses the asterisk (*). ”

RESOLUTION:
“Edit the SQL statements in the ASP files so that they use the percent sign (%) for wildcards. If parameter queries with wildcards are exported, edit the SQL statements in the ASP file so that they use the percent sign (%) as the wildcard along with the correct sequence and number of delimiters around the wildcard.”

See article for more details. Also see Access Help topics, “Comparison of Microsoft Jet SQL and ANSI SQL” and “Using Wildcard Characters in String Comparisons” for further explanation. Recommend use the ANSI wildcard characters (% and _ ) for any SQL that is being used for ASP pages.

HTH

Reply | Quote

In the Access DB I have a query which uses ( Like “*Bone*” ) to pull out all records with Bone in the that particular column.

In Access I exported the query as an ASP. But the ASP fails to load in the browser and times out. I knew this had something to do with the wildcard * used in the Access DB but did not know how to resolve it.

I have now edited the exported ASP and replaced all * in the SQL statement with % and this has solved the problem.

I still cannot understand why Access uses * as a wildcard in a query but if you export the query as ASP it still uses the * .

Cheers
Seoras

Reply | Quote

Not sure what “generated ASP from a query” exactlly signifies, w/o more details only thing I can suggest is that if you are using ADO to generate a recordset, you have to use the “standard” SQL wildcard character, which is a percent sign (%), not an asterisk (*). So in the SQL statement being used to “generate ASP”, you may want to try replacing the asterisks with percent signs. Other than that, have no suggestions, as do not have very much experience with ASP.

HTH

Reply | Quote