ERROR [42000] [Simba][SimbaEngine ODBC Driver]SELECT DISTINCT (<< ??? >>[Field 1]) FROM dbo.MyTable

Business Central/NAV Developers Forum
1

Hi,

I am new to Navision development, but experienced in C#. I am running on Windows 2003 Server - both webserver and Navision. NODBC.dll driver version is 5.00.24199.00.
I am trying to built an ASP.NET webinterface for a Native Navision database - I am using the C/ODBC drivers for Navision and connecting via System.Data.Odbc. Upgrading to SQL database is unfortunately not an option.

I get this error:

ERROR [42000] [Simba][SimbaEngine ODBC Driver]SELECT DISTINCT (<< ??? >>[Field 1]) FROM dbo.MyTable

The query I am running is the following:

SELECT DISTINCT ([Field 1]) FROM dbo.MyTable

  • however the SimbaEngine driver (C/ODBC) appends the << ??? >> in the query…
    I have tried removing brackets, removing “dbo.” - but no luck… what am I missing?

According to Microsoft Dynamics™ NAV ODBC Driver 5.0 Guide, DISTINCT keyword is supported.

part of my C# source:

…

DataSet result = new DataSet();
OdbcDataAdapter OdbcDA = new OdbcDataAdapter();
OdbcCommand cmd = new OdbcCommand();

cmd.Connection = new OdbcConnection(ConfigurationManager.ConnectionStrings[“myConn”].ToString());
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 600;
cmd.Connection.ConnectionTimeout = 600;
cmd.CommandText = “SELECT DISTINCT ([Field 1]) FROM dbo.MyTable”;

cmd.Connection.Open();
OdbcDA.SelectCommand = cmd;
OdbcDA.Fill(result, “Master”);

…

Hoping for some help :slight_smile:

2

Hi perels,

not sure if this can help you. I’ve worked with NAV ODBC and Sharepoint BDC service, it’s supposed to display information of NAV on sharepoint lists and webparts but it doesn’t work, even if it works with other db’s ODBC.

I’ve to say this happens only (obviously) with native database, maybe more ODBC NAV errors can be discovered with your thread.
Thanks :wink:

3

This maybe off topic, but since you are developin gin C#, why not use C/FRONT instead of N/ODBC to interface to Navision?

4

Hello David and gallatin,

Thanks for the replies.
I am unaware of this C/Front - is this downloadable? - I have tried googling - no luck. Could you post a link?

What is valid SQL for this native driver? Shouldn’t something like “SELECT DISTINCT([Field 1]) * FROM dbo.MyTable” be valid??

5

Its on the same CD where you got N/ODBC from.

6

Hi David,

Thanks for the reply :slight_smile:
Before I try the cfront; I have narrowed down the problem, it is because I have a called Field 1 - so that Field 1 - I have escaped the space with _, so my SQL becomes SELECT DISTINCT(Field_1) FROM dbo.MyTable. This seems to work for some queries.

However I also have a field called something like “Field no_2” - what is the escape sequence for such a field? I have tried Field_no_2 and Field__no_2 no luck :frowning:

7

is it “Field No 2” or “Field No. 2”?

Also why not try just changing the name of the field?

8

Unfortunately I am not the architect of this “lame” database layout, if I could I would have avoided spaces in column names…

The column is called Field no_2 in the table, so this is Fieldno2.

9

I have more or less solved the problem, it turns out that the System DSN had the wrong Identifiers set up:

For recognition of fields with space I set the Identifiers to a-z,A-Z,0-9,_ and now it works…

However I now have a new problem, because some of the field names are using international characters like æ ø å

Does you (or anybody) know how to reference columns containing ø æ å? e.g. fieldname like “Årstal”

10
  • For anybody interested I found the solution to international characters;

Just replace Æ Ø Å with an underscore, e.g. “Årstal” becomes “_rstal” and “Årstal field blahblah” becomes “_rstal_field_blahblah”

11

please try with double quotes “” around the table name. eg select * from ""Table Name “”