Different result in Access vs ASP page

[iannorthwood]

I have a query which returns 2 records when executed in Access but, when run from my ASP page, I get -1 for RecordCount or, if I loop through the RS incrementing a counter, the counter is 0. In either case, it's basically saying that there were no records.

The ASP page uses the following settings:

strSQL = "SELECT DISTINCT T_Request.RequestID, T_Request.RequestAppID, T_Request.RequestedBy, T_Request.RequestedOn, T_Request.RequestRequiredBy, T_Request.RequestStatusID, T_Request.RequestPackagerID, T_Request.RequestSignedOffOn, T_Request.RequestSignedOffBy, T_Request.RequestTypeID, T_Request.RequestPriorityID, T_Request.RequestReleaseLocation FROM T_Request, T_App WHERE RequestAppID IN (SELECT AppID FROM T_App WHERE AppVendorID = (SELECT VendorID FROM T_Vendor WHERE VendorName LIKE 'Aspect*'))"

'// The connection dbConn is open already at this point.
'// Trust me, it is. Also, the standard ADOVBS.INC
'// is also included (for defining adUseClient, etc)

Set RS = dbConn.Execute(strSQL)
response.write strSQL & "<BR><BR>"

Dim intCount
intCount = 0

With RS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.MoveFirst
response.write "Count=" & .RecordCount

While NOT .EOF AND NOT .BOF
intCount = intCount + 1
.MoveNext()
Wend

response.write "<BR>Count=" & intCount
End With

 

[Remou]

The ASP wild card is %, not *

 

[mp9]

I am understanding this right?

When the query and datasource ar native Access, all is well. When the query is in an ASP page and the back-end is something else (SQL Server, presumably), you get nothing back?

My ASP and SQL Server knowledge are limited but could it be your wildcard character? * works for Access but don't you need % for SQL Server?

Like I say, I don't have too much experience here, so could be talking rubbish, but for your ASP version try:

strSQL = "SELECT DISTINCT T_Request.RequestID, T_Request.RequestAppID, T_Request.RequestedBy, T_Request.RequestedOn, T_Request.RequestRequiredBy, T_Request.RequestStatusID, T_Request.RequestPackagerID, T_Request.RequestSignedOffOn, T_Request.RequestSignedOffBy, T_Request.RequestTypeID, T_Request.RequestPriorityID, T_Request.RequestReleaseLocation FROM T_Request, T_App WHERE RequestAppID IN (SELECT AppID FROM T_App WHERE AppVendorID = (SELECT VendorID FROM T_Vendor WHERE VendorName LIKE 'Aspect%'))"

 

[iannorthwood]

No, for now (don't ask...recalcitrant IT dept...) the ASP page talks to the exact same MDB. I have tried Exclusive access, closing the d/b in Access, closing Access altogether (even though the d/b is closed) - that shows how frustrated I am!

 

[PHV]

If you use ADO then the wildcar is definitively % instead of *.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iannorthwood]

Aaaaaaaahhhhhhhhh.......All is clear now. Susbtituting % for * results in 2 records! Good work, thank you, PHV.

 

[Remou]

Did you miss my post 13 Apr 07 4:45?

 

[iannorthwood]

> Did you miss my post 13 Apr 07 4:45?
No, but I knew that % is SQL Server's wildcard and thought that you'd assumed I was talking to SQL Server: PHV's response explicitly explained that it is used for ADO, too. Please accept my apologies. I guess this shows that it's important to be explicit in responses. In other fora I participate in, I always give too much information than assume the poster will know what I mean.

 

[PHV]

Remou, if in ASP you use DAO then the wildcar is *