Recordset Query!

[AJ]

Hi all,

I am trying to execute the following code:

'create recordset object
SET recData = Server.CreateObject("ADODB.recordset")

'open recordset
recData.Open "exec GetExhibitorsSearchByName 20,38916,38916,'do'", DataConn,
3, 3

20 = First Param (EventID)
38916 = Second Param (Start Date Int)
38916 = Third Param (End Date Int)
'do' = Fourth Parameter ('Search Criteria')

When executed from ASP this code does not return any info.

However, when i execute the query within access, with the same parameters, i
get the desire results.

Anyone have any thoughts on what i am doing wrong?

Cheers,
Adam

 

[Bob Barrows [MVP]]

Could we please stick with a single thread? Thank you.
More below:

Hi all,

I am trying to execute the following code:

'create recordset object
SET recData = Server.CreateObject("ADODB.recordset")

'open recordset
recData.Open "exec GetExhibitorsSearchByName 20,38916,38916,'do'",
DataConn, 3, 3

20 = First Param (EventID)
38916 = Second Param (Start Date Int)
38916 = Third Param (End Date Int)

The dates are Ints??? Why is that? What are the actual datatypes of the
fields in your tables?

'do' = Fourth Parameter ('Search Criteria')

When executed from ASP this code does not return any info.

However, when i execute the query within access, with the same
parameters, i get the desire results.

Anyone have any thoughts on what i am doing wrong?

I prefer this technique:

SET recData = Server.CreateObject("ADODB.recordset")
DataConn.GetExhibitorsSearchByName 20,38916,38916,"do",recData

Unfortunately, I have no chance of telling you what the problem is without
seeing some data and the actual sql in the saved query that you are
executing.

 

[AJ]

Hi Bob,

The stored queries are below:

I don't think this current problem is directly related to my queries though.

I thought it was a parameter issue, but when i removed all parameters from
the queries (hard coded values into selects) and ran 'exec
GetExhibitorsSearchByName' from asp i still got no results; if i run
GetExhibitorsSearchByName in Access all is well.

Thus it appears to be a problem with the querying method or recordset.

I would use your preferred method, but it doesn't provide much flexibility
when
intergrating with my paging class and generating the sql queries 'exec ....'
dynamically.

My code is based on the suggestions on this page.
http://authors.aspalliance.com/stevesmith/articles/sprocs.asp

I am almost at a loss, why this isn't working..

Any other thoughts??

Thanks for you help...and responses so far...!!!!

Cheers,
Adam

ASP:

SET recData = Server.CreateObject("ADODB.recordset")

'open recordset
recData.Open "exec GetExhibitorsSearchByName 20,38916,38916,'do'", DataConn,
3, 3

QUERIES:

ExhibitorsSearchByName
SELECT

c.ID, c.Company_Name, p.[level], 1 As QueryNbr

FROM

(Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)

LEFT JOIN

Package AS p ON s.Package_ID = p.ID

WHERE

c.Category = 'EXH'

AND

(s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))

AND

EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID
=@EventID AND Company_ID = c.ID)

AND

(INT(Start_Date) <= @StartDate) AND (INT(End_Date) >= @EndDate)

ORDER BY

p.[level] DESC , c.Company_Name, c.ID

UNION SELECT

c.ID, c.Company_Name, p.[level], 2 As QueryNbr

FROM

(Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)

LEFT JOIN

Package AS p ON s.Package_ID = p.ID

WHERE

c.Category = 'EXH'

AND

(s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))

AND

EXISTS(SELECT Company_ID FROM Event_Company_Link WHERE Event_ID =
@EventID AND Company_ID = c.ID)
ORDER BY c.Company_Name, c.ID;


GetExhibitorsSearchByName

SELECT ID, First(Company_Name), First([level]), First(QueryNbr)
FROM ExhibitorsSearchByName
GROUP BY ID
HAVING First(Company_Name) LIKE '*' & @CompanyName & '*';

 

[Bob Barrows [MVP]]

Hi Bob,

The stored queries are below:

I don't think this current problem is directly related to my queries
though.

I thought it was a parameter issue, but when i removed all parameters
from the queries (hard coded values into selects) and ran 'exec
GetExhibitorsSearchByName' from asp i still got no results; if i run
GetExhibitorsSearchByName in Access all is well.

Thus it appears to be a problem with the querying method or recordset.

I would use your preferred method, but it doesn't provide much
flexibility when
intergrating with my paging class and generating the sql queries
'exec ....' dynamically.

My code is based on the suggestions on this page.
http://authors.aspalliance.com/stevesmith/articles/sprocs.asp

I am almost at a loss, why this isn't working..

Any other thoughts??

Not until you provide the actual datatypes of the fields in your table.
I'm still flabbergasted that you are attempting to pass dates as
integers.

GetExhibitorsSearchByName

SELECT ID, First(Company_Name), First([level]), First(QueryNbr)
FROM ExhibitorsSearchByName
GROUP BY ID
HAVING First(Company_Name) LIKE '*' & @CompanyName & '*';

Actually, here is your problem right here. When running a query via ADO,
even a saved query, you must use the ODBC wildcards, not the Jet
wildcards. Change this to:

HAVING First(Company_Name) LIKE '%' & @CompanyName & '%';

You might want to save the original version for testing in the Access
environment.

 

[Mike Brind]

Are you using Access? Access doesn't support the Exec command. In
fact, any attempt to try this should have thrown an Invalid Operation
error. Have you got On Error Resume Next on your page?

You should try Bob's method and see if that works. If your query and
parameters return a recordset when run in Access, they should do using
Bob's method from ASP too.

 

[AJ]

Hi Bob,

It was the wildcard characters;

1) % works with ASP & ADO but not with Access
2) * works with Access but not with ASP & ADO.

I find this quite bizarre, so thanks for the insight!!!

Regarding the date issue, it didn't right with me either, but while it worked
i didn't really want to tinker with it. As you have already noticed I don't
particularly like Access, & am not all that good at it;

I find it much easier to do things in SQL Server even MySQL, but a new job
has found me working with Access again until they migrate (which is planned).

The intentions of the previous developer in comparing the Dates as INTS
was to remove the fractional part of any given date.

Consequently 01/02/2006 9:00am & 01/02/2006 12:00pm would amount to
01/02/2006.

This article deals with the problem.
http://support.microsoft.com/kb/210276/

I have since changed the implementation:
AND
(DateValue(Start_Date) <= INT(Now())) AND (DateValue(End_Date) >=
INT(Now()))

Thanks for your help!!!

Cheers,
Adam

Hi Bob,

The stored queries are below:

I don't think this current problem is directly related to my queries
though.

I thought it was a parameter issue, but when i removed all parameters
from the queries (hard coded values into selects) and ran 'exec
GetExhibitorsSearchByName' from asp i still got no results; if i run
GetExhibitorsSearchByName in Access all is well.

Thus it appears to be a problem with the querying method or recordset.

I would use your preferred method, but it doesn't provide much
flexibility when
intergrating with my paging class and generating the sql queries
'exec ....' dynamically.

My code is based on the suggestions on this page.
http://authors.aspalliance.com/stevesmith/articles/sprocs.asp

I am almost at a loss, why this isn't working..

Any other thoughts??

Not until you provide the actual datatypes of the fields in your table.
I'm still flabbergasted that you are attempting to pass dates as
integers.

GetExhibitorsSearchByName

SELECT ID, First(Company_Name), First([level]), First(QueryNbr)
FROM ExhibitorsSearchByName
GROUP BY ID
HAVING First(Company_Name) LIKE '*' & @CompanyName & '*';

Actually, here is your problem right here. When running a query via ADO,
even a saved query, you must use the ODBC wildcards, not the Jet
wildcards. Change this to:

HAVING First(Company_Name) LIKE '%' & @CompanyName & '%';

You might want to save the original version for testing in the Access
environment.



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Hi Bob,

It was the wildcard characters;

1) % works with ASP & ADO but not with Access
2) * works with Access but not with ASP & ADO.

I find this quite bizarre, so thanks for the insight!!!

Regarding the date issue, it didn't right with me either, but while
it worked i didn't really want to tinker with it. As you have already
noticed I don't particularly like Access, & am not all that good at
it;

I find it much easier to do things in SQL Server even MySQL, but a
new job has found me working with Access again until they migrate
(which is planned).

The intentions of the previous developer in comparing the Dates as
INTS
was to remove the fractional part of any given date.

Consequently 01/02/2006 9:00am & 01/02/2006 12:00pm would amount to
01/02/2006.

This article deals with the problem.
http://support.microsoft.com/kb/210276/

I have since changed the implementation:
AND
(DateValue(Start_Date) <= INT(Now())) AND (DateValue(End_Date) >=
INT(Now()))

If you have an index that includes those fields, I think you will find
that this will be much more efficient:

.... (Start_Date < Date()+1 and End_Date <= Date())

Bob Barrows