Queries with functions using ADO

[snoopy75]

I have an ASP page that is using ADO to connect to an Access 2000 database. In the database itself, I have created a query that goes like this:

SELECT tblEquipmentInfo.EquipmentID, tblEquipmentInfo.Account, tblEquipmentInfo.Detail_Number, Replace(Replace([tblEquipmentInfo]![Stock_Number],"-","")," ","") AS Stock_Number, tblEquipmentInfo.Prime, tblEquipmentInfo.Authorized
FROM tblEquipmentInfo;

I used the Replace functions because I need the query to return values with the spaces and dashes removed, for reasons too complex to explain here. When I run this query from within Access itself, it runs perfectly and generates the desired results.

Now, my ASP page is calling the query in the usual way:

Set rsTest = Server.CreateObject("ADODB.Recordset")
rsTest.ActiveConnection = MM_equipment2_STRING
rsTest.Source = "SELECT * FROM qrySbssCompare"
rsTest.CursorType = 0
rsTest.CursorLocation = 2
rsTest.LockType = 1
rsTest.Open()

But I get the following error, which refers to the line number with the Open() statement:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.

So... I've connected to Access queries in ASP before, even ones with functions in them. How come it doesn't work this time? What can I do differently, yet still get the recordset in the format I want?

--Ryan

 

[onpnt]

do the replace functionality on the returned rs values as you loop through them so you have the scripting engine with the function resource. I believe your main down fall here is the fact that executing a query with the built in functions at access's level with all it's resources verses interacting with asp is not the same thing and the resources just are not available in this level. The query test in the database is a good test but never take it to heart it will react the same through your app.

_____________________________________________________________________
_{Where have all my friends gone to????}

http://www.onpntwebdesigns.com

 

[snoopy75]

I'd like to do just that, but I'd like to use that field (the one without dashes or spaces) in a Join clause of another query, something like this:

SELECT qrySbssCompare.Account, qrySbssCompare.Detail_Number, qrySbssCompare.Stock_Number
FROM qrySbssCompare INNER JOIN tblSbssInfo ON (qrySbssCompare.Stock_Number = tblSbssInfo.Stock_Number)

The problem I'm trying to solve is that one of my tables has values without the dashes or spaces, and the other table has the same kinds of values, only with the dashes and spaces, so they'll never be equal to each other in that Join clause, unless I can remove the odd characters from the one field.

The point is, I need to do a comparison. I was hoping to get a query to do it for me, but if that's not possible with this odd data, I guess I'll need to loop through the records manually. :-( Or is there some SQL function that I'm unaware of that could help me? Or an alternate method to get what I need?

--Ryan

 

[snoopy75]

Okay, I tried to do a manual looping through the recordset, using the VBScript Replace function, and after about a minute I got this error:

Error Type:
Active Server Pages, ASP 0113 (0x80004005)
The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.

I kind of expected something like that, since I'm looping through about 2500 records, and for each record, trying to find a match by manually looping through another 2500 records...