mibuso.com
Home› Navision Financials

ASP ADO C/ODBC Problems with SQL-join

abe2003abe2003 Member Posts: 3
edited 2006-09-13 in Navision Financials
I try to connect (read) the NAVISION Database with the c/odbc driver inside IIS 5.0/ADO. Everthing works fine until i try to make a connection join over to tables to connect data in different tables. the error message says unknown error ?

Here is the code o wrote:

strdsn = "DSN=navision_k"
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open strdsn

strSQL ="SELECT Projektteam.Projektnr_, Projekt.Beschreibung FROM Projekt, Projektteam WHERE Projekt.Nr_ = Projektteam.Projektnr_"

Set projekte = Server.CreateObject("ADODB.Recordset")
projekte.Open strSQL, objCon

who can help?

Comments

  • borsicsjborsicsj Member Posts: 32
    Hi,
    just the simplest thing: Has te user you use in the DSN has access in Navisio to the specified tables?
    Brg,

    --
    János Borsics
  • abe2003abe2003 Member Posts: 3
    yes, he has. the testuser has administrator rights. i try the sql request with microsoft query successfuly. only the request inside the asp code returns the errormessage.
  • Jens_MadsenJens_Madsen Member Posts: 23
    I think it's because ms query makes two seperate select statement, one for each table involved...
    Afterwards ms query joins the two tables...

    This feature isn't available in c/odbc alone.... unfortunately...

    --just a thought... :wink:
  • FabermanFaberman Member Posts: 24
    Hello.

    I am trying to run more specific queries on Navision tables than "SELECT * FROM <tablename>"

    My query is :

    strSQL="SELECT No.,Name,Address,Address 2,City FROM Customer WHERE Department Code='Retail'"

    Normally a simple thing in Access, SQLserver does not work with C/ODBC

    I get the following error :

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver]Expected lexical element not found: FROM

    Can anyone shed some light on the syntax to use to make this work?
    Is this functionality available with the C/ODBC driver (pre Navision 4.0)?

    I have a nasty feeling it is not.

    ](*,)
  • fbfb Member Posts: 246
    If you want to do any more than the most trivial SELECTs using C/ODBC, then you really must read the manual -- 'w1w1codbc.pdf'...

    Here's an example taken from that manual: 
    SELECT a.Name, Count(*) FROM Country a, Customer b
WHERE a.Code = b."Country Code" GROUP BY a.Name
    Notice that when a field name contains a space (Country Code in the example), you must enclose the field name in double-quotes...

    In your statement, the C/ODBC select statement parser hits the space in Address 2, assumes the column list is complete and expects the FROM clause to follow. Since it then finds '2', it issues the complaint 'Expected lexical element not found: FROM'.
  • FabermanFaberman Member Posts: 24
    Having read the manual, and chosen the [a-z,A-Z,0-9,_] option in the C/ODBC driver configuration, I have been able to execute more than the most basic queries......

    until

    One attempts to execute a query using certain fields, which have been wonderfully named using hyphens i.e. "Ship-To Name".

    I now get "Column Not Found" errors when I run the page!!

    Having changed the configuration to allow symbols and spaces etc., put quotes around the field name, tried square brackets around the field name, the page still will not run the query.

    Does anyone know how to include hyphenated field names in queries to be used for ADODB.connections????

    Are there such things as naming conventions for field names in Navision!?!

    I am of the tether at my end.

    ](*,)
  • fbfb Member Posts: 246
    Err, sorry if this is a dumb question, but you are referring to the field as "Ship_to_Name" in your query, right?

    (Option [a-z,A-Z,0-9,_] means any char not in [a..z,A..Z,0..9] is changed to '_')

    Example: 
    SELECT Ship_to_Name,Ship_to_Name_2,
      Ship_to_Address,Ship_to_Address_2,
      Ship_to_Postal_Code,Ship_to_City
  FROM Sales_Header
  WHERE Document_Type = 3 AND
             Document_No_ = 'SO-00001'
  • FabermanFaberman Member Posts: 24
    Hi.

    Unfortunately, I have all the underscores where required.

    What I have is a page with a drop down list (populated from a query on Navision) showing customer names and a "view orders" button. When the use picks a customer from the list, the value from the list is passed to a hidden field. They then click the button, which submits the form, whose action is the same page.

    The page reloads and if the "Cust" variable has a value

    i.e. Cust=Request("<hidden field>")

    , another Sub runs.

    The query below is executed and order information for the chosen customer is displayed.

    The idea is that the drop down is always available so the user can choose another customer and view their order information if they so wish.
    (Also, two date fields would be added so as to allow the user to apply a data range filter to the returned data, and so on......)

    Here is my current (simple) query :

    strSQL = "SELECT S.Posting_Description, S.Invoice_Value FROM Sales_Invoice_Header S WHERE S.Ship_To_Name=' "& Cust &" ' "

    (Where Cust is the variable holding the customer name).


    Here is the error message :

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    [Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver]Column not found: Ship_To_Name

    If there is a way to get this to work, then any field could be used for querying.

    Any advice will be welcomed.

    Yours ](*,)
  • FabermanFaberman Member Posts: 24
    :oops: :oops: :oops: :oops: :oops: :oops: :oops: :oops: :oops: :oops:

    Hi all,

    I have made a fool of myself.

    Specific apologies to fb who showed me the solution to my problem though I was too blind to see it.

    I looked at your example again (and again) until I noticed the case of the field names.
    My field names were capitalized in places where they should not have been.

    I have amended my query and it works.

    Thank you and sorry.

    #-o
  • fbfb Member Posts: 246
    [Doh!] -- that's the theme song for coders, isn't it...

    I'm not sure I would have picked up on capitalization either...

    (In a SQL Server env't, table/field names usually aren't case-sensitive...)

    No apologies necessary -- glad this one worked out...

    Now, on to the next obstacle, and the next opportunity for embarrasment (or is it 'opportunity for learning' -- feels the same...)!

    Best regards.
    fb
  • philippegirodphilippegirod Member Posts: 191
    Hello,
    Faberman wrote:
    Hello.

    I am trying to run more specific queries on Navision tables than "SELECT * FROM <tablename>"

    My query is :

    strSQL="SELECT No.,Name,Address,Address 2,City FROM Customer WHERE Department Code='Retail'"


    In a SQL request, you have to be carefull to the '.' and spaces. Your request should work better like this : 
    strSQL="SELECT 'No.',Name,Address,'Address 2',City FROM Customer WHERE 'Department Code'='Retail'"
    No. => 'No.' , Address 2 => 'Adress 2' etc..

    Other thing, are you sure the field name for "Department Code" is not "Global Dimension 1 Code" ?
    My candle burns by both ends, it will not last the night,
    But oh my foes and oh my friends, it gives a lovely light
Sign In or Register to comment.