none
Data Reader cannot handle columns that start with a number RRS feed

  • Question

  • Hi together,

    I' ve run into an odd problem lately and am hoping to find a solution here.

    I have programmed a VB.NET class (wrapped into a DLL) that connects to a VERY old Navision ERP system to read some data from tables via ODBC. Since the Navision system does not use a MS SQL database, I have to use the 15 year-old C/ODBC driver that shipped with the software (no newer version of the driver works with it, tested them all).

    One of these tables is called "Project" and contains multiple columns with their names starting with NUMBERS (that bad decision was made 13 years before I joined the company, so don' t blame me ...).

    Let' s take a column named '1__TimeWindow_to' as an example.

    The following SQL query is giving me trouble:

    SELECT 1__TimeWindow_to FROM Project WHERE Nr = 123456

    The EXPECTED return of that query is:

    1__TimeWindow_to

    -----------------------------------

    22

    But what I get is:

    __TimeWindow_to

    -------------------------------------

    1

    Now comes the odd part:

    If I build a query which comprises all columns of the project table, i.e. "SELECT * FROM Project WHERE Nr = 123456",

    ALL column names AND values are returned correctly!!!

    This means that the problem only occurs when I try to access columns with numbers at first position in name DIRECTLY in the query. Unfortunately, I MUST use the column NAMES instead of their ordinal position numbers (as the DLL is accessed by a stored procedure I'm not allowed to re-program and that stored procedure is using column names).

    Of course, I could try to convert the column names in an incoming query to their respective ordinal position numbers, but that' s not a solution but a workaround (and a dirty one, too!).

    Note1: Changing column names in the Navision database is neither possible nor allowed, so it' s not an option for a solution.

    Note2: In MS Excel, a connection with usage of ADO 2.8 Library (ADODB) can read from such columns without a problem ...

    How can I make the ADO.NET Data Reader get to read from such columns?


    Tuesday, January 19, 2016 6:27 PM

Answers

  • Hi standopandshout,

    According to your description, I create a demo, and find that not a data reader issue, it’s a sql issue. it think 1 is a result and “__TimeWindow_to” is its alias name.  it works fine after I add a table name before column name as below.

    SELECT Project.1__TimeWindow_to FROM Project WHERE Nr = 123456

    Best regards,

    Cole Wu

    Wednesday, January 20, 2016 2:25 AM
    Moderator

All replies

  • Hi standopandshout,

    According to your description, I create a demo, and find that not a data reader issue, it’s a sql issue. it think 1 is a result and “__TimeWindow_to” is its alias name.  it works fine after I add a table name before column name as below.

    SELECT Project.1__TimeWindow_to FROM Project WHERE Nr = 123456

    Best regards,

    Cole Wu

    Wednesday, January 20, 2016 2:25 AM
    Moderator
  • Not familiar with the Navison system but have you tried enclosing the column names within brackets (e.g [1_TimeWindow_to]).

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 20, 2016 2:34 PM