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 6 1617
Could we please stick with a single thread? Thank you.
More below:
AJ wrote:
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.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
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/steve...les/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 & '*';
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.
--
Mike Brind
AJ wrote:
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/steve...les/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 & '*';
AJ wrote:
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/steve...les/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.
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
"Bob Barrows [MVP]" wrote:
AJ wrote:
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/steve...les/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.
AJ wrote:
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
--
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: HKM |
last post by:
Hello,
I have a query engine that builds the SQL Query to obtain the
recordSet. Following is an Exmaple Query that my QueryBuilder
outputted
SELECT * FROM BookInfo WHERE BookName LIKE...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my...
|
by: Gerry Abbott |
last post by:
Hi all,
I having some confusing effects with recordsets in a recent project.
I created several recordsets, each set with the same number of records, and
related with an index value.
I create...
|
by: lenny |
last post by:
Hi,
I've been trying to use a Sub or Function in VBA to connect to a
database, make a query and return the recordset that results from the
query. The connection to the database and the query...
|
by: Nathan Bloomfield |
last post by:
Hi there,
I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.
Basically, it works as follows:
- adds records...
|
by: Lyn |
last post by:
Hi,
I am opening a form in Continuous mode to list the records from a recordset
created in the calling form. The recordset object is declared as Public and
is set into the new form's Recordset...
|
by: Jan |
last post by:
Hi
I have a database that I use to keep track of the sales promotions that we
send to companies.
I normally send a mailing based on a subset of the companies in the database
(found using the...
|
by: ajspacemanspiff |
last post by:
I currently have a solution that requires 2 sub queries, where each of
them is convereted into a crosstab query and then I join the crosstab
queries to a result. I would like to make this more...
|
by: altreed |
last post by:
Hi,
I am new to ASP, HTML and iis.
I have my asp code working so that I can retrieve my desired record
from the database. I can place the data on the screen in table form.
All works fine.
...
|
by: Henrik |
last post by:
The problem is (using MS Access 2003) I am unable to retrieve long
strings (255 chars) from calculated fields through a recordset.
The data takes the trip in three phases:
1. A custom public...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |