he expression is typed incorrectly or it is too complex to be evaluated

Discussion in 'ASP General' started by Guest, Jan 19, 2004.

  1. Guest

    Guest Guest

    The database contains comparetive data on competition for a certain product
    range.

    t_main table contains my product range (codes, parameters, prices)
    competition table contains similar data on somebody else competitive
    products
    analog table indicates which compatitive products are analogues to which of
    my products.(many-to-many relationship)

    I understand you need only those fields which are involved in the query

    SELECT
    Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
    AS Expr1
    FROM competition, analog, t_main
    WHERE (((analog.af_product)=[] AND (analog.af_product)=[t_main].[pr_code])
    AND ((analog.direct_analog)=Yes) AND
    (([analogs].[competitor_id])=[competition].[id]));

    These are:

    competition
    id - Autonumber Long Integer
    price_rub - currency, fixed with dot as decimal point eg. 100.00
    quantity - Number Long integer eg.400
    neither are key or part of the key

    analog
    competitor_id - Number Long integer eg. 75
    af_product -text eg. AAA000
    direct_analog - yes/no
    the first two make a composite key

    t_main
    pr_code - text eg. AAA000 (key field)
    per_pack - Number Long integer eg. 300

    Parameter rub (the exchange rate) is stored in an ASP Application scope
    variable and returns type 5 (double precision floating point number)

    Parameter [] is taken from another recordset containg a value from
    t_main.pr_code, i.e. something like AAA000



     
    Guest, Jan 20, 2004
    #21
    1. Advertisements

  2. Guest

    Guest Guest

    "I'm not sure where you are seeing this "type". Could you show me your code
    where you determine it's type is 0?"

    response.write VarType(objRS(0))
    And it returns 0 for all 85 records in the table.
    When there is no matches, it returns 1. Actually my code based on that. Now
    matches means no analogues or no analog price recorded and the code returns
    "not available". Again the whole thing has been working fine until I desided
    to apply square root to the ratio. Is there a chance that the database
    somehow consideres the numbers negative?
    Just as a test I changed one value in competition.quantity to negative and
    .... nothing changed as if it suxxessfully extracted the square root our of
    the negative number.
    I think I started halucinating, so I have to go to bed now.

    PS. I added the parameter clouse. It changed nothing, so I removed it.
     
    Guest, Jan 20, 2004
    #22
    1. Advertisements

  3. Guest

    Guest Guest

    "I'm not sure where you are seeing this "type". Could you show me your code
    where you determine it's type is 0?"

    response.write VarType(objRS(0))
    And it returns 0 for all 85 records in the table.
    When there is no matches, it returns 1. Actually my code based on that. Now
    matches means no analogues or no analog price recorded and the code returns
    "not available". Again the whole thing has been working fine until I desided
    to apply square root to the ratio. Is there a chance that the database
    somehow consideres the numbers negative?
    Just as a test I changed one value in competition.quantity to negative and
    .... nothing changed as if it suxxessfully extracted the square root our of
    the negative number.
    I think I started halucinating, so I have to go to bed now.

    PS. I added the parameter clouse. It changed nothing, so I removed it.
     
    Guest, Jan 21, 2004
    #23
  4. Guest

    Bob Barrows Guest

    I have never used this function with my data retrieval code. I use rs.EOF to
    detect whether any records were returned, and if records were returned, I
    check the contents of the fields usually using the len() function.

    I can't help you any further without seeing the datatypes of the fields
    involved in the query, a few rows of sample data -
    an INSERT tablename(<column_list>) VALUES (<value_list) statement for each
    row of sample data would be best,
    and the parameter values you are attempting to pass.

    I know you want to keep trying to solve it yourself seeing as how you are so
    close (I fall into the same trap myself), but at this point, you really need
    another set of eyes looking at it first-hand.

    Bob Barrows
     
    Bob Barrows, Jan 21, 2004
    #24
  5. Guest

    Guest Guest

    Thanks, Bob.

    1. I use rs.EOF

    I have to call aggregate functions to determine min, max and average antway.
    And I decied to utilise the by-product of these functions (vartype returnes)
    rather then run another query and evaluating rs.EOF
    But this should not matter - just another way to do the same.

    2. I can't help you any further without seeing the datatypes of the fields

    I've posted it yesterday - is it not sufficient?
    It's difficult for me to post it as an INSERT as I never used these
    statement before (I define the tables in Access and then apload it),. and
    will have to practice it to make sure that I send you the correct one.
    So I sent it like this:

    The database contains comparetive data on competition for a certain product
    range.

    t_main table contains my product range (codes, parameters, prices)
    competition table contains similar data on somebody else competitive
    products
    analog table indicates which compatitive products are analogues to which of
    my products.(many-to-many relationship)

    I understand you need only those fields which are involved in the query

    SELECT
    Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
    AS Expr1
    FROM competition, analog, t_main
    WHERE (((analog.af_product)=[] AND (analog.af_product)=[t_main].[pr_code])
    AND ((analog.direct_analog)=Yes) AND
    (([analogs].[competitor_id])=[competition].[id]));

    These are:

    competition
    id - Autonumber Long Integer
    price_rub - currency, fixed with dot as decimal point eg. 100.00
    quantity - Number Long integer eg.400
    neither are key or part of the key

    analog
    competitor_id - Number Long integer eg. 75
    af_product -text eg. AAA000
    direct_analog - yes/no
    the first two make a composite key

    t_main
    pr_code - text eg. AAA000 (key field)
    per_pack - Number Long integer eg. 300

    Parameter rub (the exchange rate) is stored in an ASP Application scope
    variable and returns type 5 (double precision floating point number)

    Parameter [] is taken from another recordset containg a value from
    t_main.pr_code, i.e. something like AAA000
     
    Guest, Jan 21, 2004
    #25
  6. Guest

    Bob Barrows Guest

    :)
    There were 20 messages yesterday. I must have missed it.
    :)
    Fill in the blanks:

    Sample row 1:
    INSERT t_main (pr_code, per_pack)
    VALUES ('AAA000', 300)
    Sample row 2:
    INSERT t_main (pr_code, per_pack)
    VALUES ('______', ___)

    Sample row 1:
    INSERT competition (price_rub,quantity)
    VALUES (100.00,400)
    Sample row 1:
    INSERT competition (price_rub,quantity)
    VALUES (____, _____)

    etc. Note that I did not include the autonumber field in the column or value
    lists.


    I'll get back to you later.

    Bob Barrows
     
    Bob Barrows, Jan 21, 2004
    #26
  7. Guest

    Bob Barrows Guest

    What is a typical value for this parameter?

    Bob Barrows
     
    Bob Barrows, Jan 21, 2004
    #27
  8. Guest

    Bob Barrows Guest

    aa wrote:

    OK, I recreated your tables in my database and put one row of data (using
    the example data you provided) in each table. I used this sql*:

    SELECT
    Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
    AS Expr1
    FROM competition, analog, t_main
    WHERE (((analog.af_product)=[pCode] AND
    (analog.af_product)=[t_main].[pr_code])
    AND ((analog.direct_analog)=Yes) AND
    (([analog].[competitor_id])=[competition].[id]))


    to create a saved query called qAvg. I then ran this code in asp to try and
    cause your error:

    set cn=server.CreateObject("adodb.connection")
    cn.Open "provider=microsoft.jet.oledb.4.0;" & _
    "data source=" & Server.MapPath("dbfiles/db15.mdb")
    set rs = server.createobject("adodb.recordset")
    cn.qAvg .25,"AAA000", rs
    if not rs.eof then ar = rs.GetRows
    rs.close: set rs = nothing
    cn.close: set cn = nothing
    if isarray(ar) then
    response.write ar(0,0)
    erase ar
    else
    response.write "No records"
    end if

    No problems. Please try it yourself (substituting your database connection
    info of course) and see if it works.

    Bob Barrows

    *I would have used this syntax, myself:
    SELECT Avg(c.price_rub*Sqr(t.per_pack/c.quantity))/[rub] AS Expr1
    FROM (competition AS c INNER JOIN analog AS a ON c.id = a.competitor_id)
    INNER JOIN t_main AS t ON a.af_product = t.pr_code
    WHERE a.af_product=[t_main].[pr_code] AND a.direct_analog)=Yes
     
    Bob Barrows, Jan 21, 2004
    #28
  9. Guest

    Guest Guest

    52.9739

     
    Guest, Jan 21, 2004
    #29
  10. Guest

    Guest Guest

    Hete it goes.
    As we cannot indicate Autonumber fiels in competition, but it is used in
    analog, in analog I suggested the the autonumber values for the first two
    rows in competition are 1 and 2 respectively.


    t_main
    Sample row 1:
    INSERT t_main (pr_code, per_pack)
    VALUES ('AAA000', 200)
    Sample row 2:
    INSERT t_main (pr_code, per_pack)
    VALUES ('BBB000',300)

    competition
    Sample row 1:
    INSERT competition (price_rub,quantity)
    VALUES (100.00,400)
    Sample row 2:
    INSERT competition (price_rub,quantity)
    VALUES (120.00, 500)

    analog
    Sample row 1:
    INSERT analog (competitor_id,af_product,direct_analog)
    VALUES (1,AAA000,yes)
    Sample row 2:
    INSERT analog (competitor_id,af_product,direct_analog)
    VALUES (2,"AAA000",yes)

    I expect that query to return:

    (100.00*sqr(200/400) + 120.00*sqr(200/500))/2/52.9739
     
    Guest, Jan 21, 2004
    #30
  11. Guest

    Bob Barrows Guest

    I've inserted your data into the tables and I still have no problems running
    the query.

    Try my code and see if you have issues.

    What provider are you using to open your connection?

    Bob Barrows
     
    Bob Barrows, Jan 21, 2004
    #31
  12. Guest

    Guest Guest

    Thanks, Bob, I will and report. Actually you've done the job I was supposed
    to do myself - appreciated.
     
    Guest, Jan 21, 2004
    #32
  13. Guest

    Guest Guest

    Microsoft.Jet.OLEDB.4.0
     
    Guest, Jan 21, 2004
    #33
  14. Guest

    Guest Guest

    Bob,
    When I run the query in Access I keep having three prompts for parameters.
    The 1st is for un-named parameter
    The 2nd for rub
    the 3rd for pCode

    And it returns a value only if I enter to the first un-named parameter the
    same value as for pCode. Otherwise it returns emty cell (which perhaps it
    taken by VarType as 0 type)

    I think I need to understand this bit which to me seems to be the root of
    the problem
     
    Guest, Jan 21, 2004
    #34
  15. Guest

    Bob Barrows Guest

    Then you're running a different query than the one you showed me.

    I don't have an unnamed parameter in my query. Here are the two versions of
    the query that I tested (I prefer the second syntax ... actually, I'm going
    to make another change - I never liked that "Expr" column alias - let's
    change that to something meaningful):

    SELECT
    Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
    AS AvgCalculation
    FROM competition, analog, t_main
    WHERE (((analog.af_product)=[pCode] AND
    (analog.af_product)=[t_main].[pr_code])
    AND ((analog.direct_analog)=Yes) AND
    (([analog].[competitor_id])=[competition].[id]))



    SELECT Avg(c.price_rub*Sqr(t.per_pack/c.quantity))/[rub] AS AvgCalculation
    FROM (competition AS c INNER JOIN analog AS a ON c.id = a.competitor_id)
    INNER JOIN t_main AS t ON a.af_product = t.pr_code
    WHERE a.af_product=[t_main].[pr_code] AND a.direct_analog)=Yes

    Paste this sql into a query builder window, switch to Design View and check
    out what happened to the table objects in the upper pane.


    If you still have an unnamed parameter, I suspect you should replace it with
    "[pCode.]" (no quotes)
    I think you will find coding these things a whole lot simpler if you stop
    using unnamed parameters.

    Bob Barrows
     
    Bob Barrows, Jan 21, 2004
    #35
  16. Guest

    Guest Guest

    Bob,
    I cut-and-pasted your query into Access - same.
    One of the explanations might be that I have a typo somewhere so that the
    tables-fields names do not match those in the query. But then it would
    report that there is no such a field, would not it?
    Anyway, I double-checked it - no joy. And after all, the whole thing was
    working perfectly before I added the sqr(). Still it does not explain three
    prompts.

    BTW, did you noticed my remark, that the code does not complain about a
    negative number being passed to sqr()? Which probably means that sqr() is
    not executed

    Also I am confused about used of [] in the syntax. Even in your text they
    seem to be used inconsistently. What is thier purpose? Can I do without
    them? Actually I tested other queries and I see that they are not necessary,
    but for some reasom people use them.


    Regarding "Expr" column alias - it does not seem to be relevant - it is
    just a fieldname. If I omit the hole as Expr nothing changes except Access
    uses some dafault name for the field. This only might be relevant is the
    result of that query is used by some other query, might it not?


     
    Guest, Jan 22, 2004
    #36
  17. Guest

    Bob Barrows Guest

    I did have to modify the table designs so that the column names in the sql
    matched the column names you provided in your email
    It did when I initially tried your query, which lead me to change the field
    names. I received extra prompts for the column names that did not match the
    columns in the tables.

    I guess I'm going to need your database after all. Can you zip it up and
    send it to my email address (remove the "NO SPAM" from my reply-to address)?

    One issue-at-a-time :) Let's get your parameters working right.
    They are needed when using non-standard names for database objects. For
    example, if you had a column name with a space in it, such as This Column,
    in a table named This Table, this sql statement would crash:

    Select This Column from This Table

    The brackets prevent the parser from attempting to evaluate the non-standard
    names, allowing them to be sent to the query engine as-is:

    Select [This Column] from [This Table]

    They may also be required if you've made the mistake of using a reserved
    keyword for a column or table name (see here for the list of reserved
    keywords: http://www.aspfaq.com/show.asp?id=2080)

    I did not say it was relevant: I just was explaining my personal preference.

    HTH,
    Bob Barrows
     
    Bob Barrows, Jan 22, 2004
    #37
  18. Guest

    Guest Guest

    Thanks, Bob,
    I need to clean the DB a bit - it is full of irrelevant to this matter
    things - and let you have it.
    Meanwhile if you are sayin you have similar prompts problem and sorted it
    out by changeing the table/fields names - perhaps you let me know which onse
    were wrong?

    a
     
    Guest, Jan 22, 2004
    #38
  19. Guest

    Bob Barrows Guest

    I don't remember. I knew which ones they were because they (the defective
    names) appeared in the parameter prompts.

    Bob Barrows
     
    Bob Barrows, Jan 22, 2004
    #39
  20. Guest

    Guest Guest

    Bob,
    Your suggestion to let you hev my DB already had a positive outcome. While
    cleaning the DB I found that I confused the name of the query with the name
    of the table (the difference was just one letter) - so I sorted out the
    problem of too many prompts.

    However this have not sorted out the original problem.
    I added the PARAMETERS clouse to make sure the I pass the parameters in the
    right order.
    I still have the same error. But running the query from ASP affected the
    syntax of the query;
    Originally I made it
    PARAMETERS pCode String, rub Double.
    After running ASP code it became
    PARAMETERS pCode Text ( 255 ), rub IEEEDouble

    What is this about and if this has to do with the error?
     
    Guest, Jan 22, 2004
    #40
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.