Recordset Query!

Discussion in 'ASP General' started by AJ, Jul 18, 2006.

  1. AJ

    AJ Guest

    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
     
    AJ, Jul 18, 2006
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Jul 18, 2006
    #2
    1. Advertising

  3. AJ

    AJ Guest

    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 & '*';
     
    AJ, Jul 18, 2006
    #3
  4. 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/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], Jul 18, 2006
    #4
  5. AJ

    Mike Brind Guest

    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/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 & '*';
     
    Mike Brind, Jul 18, 2006
    #5
  6. AJ

    AJ Guest

    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/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.
    >
    >
    >
     
    AJ, Jul 18, 2006
    #6
  7. 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.
     
    Bob Barrows [MVP], Jul 18, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    990
    Marina Levit [MVP]
    Jan 30, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,479
    Anonymous
    Oct 13, 2005
  3. Hung Huynh
    Replies:
    8
    Views:
    311
    Bob Barrows
    Sep 24, 2003
  4. Bruno Alexandre
    Replies:
    5
    Views:
    177
    Bob Barrows
    Mar 3, 2004
  5. Recordset Query / Edit / Save

    , Mar 22, 2005, in forum: ASP General
    Replies:
    0
    Views:
    120
Loading...

Share This Page