ASP ADO C/ODBC Problems with SQL-join

abe2003

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?

borsicsj

Hi,
just the simplest thing: Has te user you use in the DSN has access in Navisio to the specified tables?

abe2003

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_Madsen

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...

Faberman

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.

](*,)

fb

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'.

Faberman

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.

](*,)

fb

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'

Faberman

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 ](*,)

Faberman

: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

fb

[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

philippegirod

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" ?