SQL statement in VB.NET for Microsoft Dynamics NAV 4.0 SP1

[Sveta]

I want to extract data and develop a report in Visual Basic. NET.
My question is how to manage in SQL statement Navision database table names
which are having spaces in names, for example, "Vendor Bank Account", and
also
in field names, for example "Bank Account No_"?

Visual Basic .NET doesn't accept spaces in table and field names, so please
help to write such SQL statement.

SELECT no_, name, address, city, Bank Account No_
FROM Vendor INNER JOIN Vendor Bank Account
ON Vendor.no_ = Vendor Bank Account.Vendor no_

I have developed such code for selection only form table Vendor and it works
fine:

Dim myODBConnection As New OdbcConnection("DSN=NV")
Dim myODBCommand As OdbcCommand = New OdbcCommand()

txtResults.Clear()
myODBCommand.Connection = myODBConnection
myODBCommand.CommandText = "SELECT no_, name, address, city FROM vendor"

myODBCommand.CommandType = CommandType.Text

myODBConnection.Open()
Dim reader As OdbcDataReader
reader = myODBCommand.ExecuteReader

While reader.Read()
txtResults.Text = txtResults.Text & reader("No_") &
ControlChars.Tab & reader("Name") & ControlChars.Tab & ControlChars.Tab &
reader("Address") & ControlChars.CrLf & reader("City") & ControlChars.CrLf

End While
reader.Close()
myODBConnection.Close()

[Volker Strähle]

Did You try "[Bank Account No_]"?

Volker

[Sveta]

I have tired

myODBCommand.CommandText = "SELECT [Bank Account No_] FROM [Vendor Bank
Account]"

But when I run report I recieve error message:
ERROR[42000][Simba][SimabEngine ODBC Driver]SELECT << ??? >>[Bank Account
No_] FROM [Vendor Bank Account]

What I am doing wrong?

"Daniel Rimmelzwaan" wrote:

> I believe that is what square brackets are for ( the [ and the ]
> characters). So if you need the value of the "Bank Account No_" field in the
> "Vendor Bank Account" table, you would do:
> SELECT [Bank Account No_] FROM [Vendor Bank Account] etc.
>
> "Sveta" <svetlana...@yahoo.com> wrote in message
> news:C4AB3FE0-FC4A-4CDD...@microsoft.com...

[Daniel Rimmelzwaan]

I just went into my SQL Server query window and ran the following query:
select [Bank Account No_] from [Cronus USA$Vendor Bank Account]
This returned all Bank Account numbers without any errors.

I don't know what kind of syntax you need with your application to
accomplish what you need to do, you will have to consult the documentation
for that. All I know is that you need to use square brackets for field and
table names when they have spaces in them. What you do seem to be missing is
the company name in the table name. Unless you modified some table
properties, the Vendor Bank Account table will be called "Company
Name$Vendor Bank Account" on SQL Server.

"Sveta" <svetlana...@yahoo.com> wrote in message

news:BA3E8F47-64CD-4EF2...@microsoft.com...

[Abiola@discussions.microsoft.com Adewale Abiola]

I believe Daniel is right. For certain tables in Navision 4.0 you will need
to state the company names, before you can access them. I guess the tables
excluded are company wide tables, which cut across all companies.

[Hamilton@discussions.microsoft.com Ramone Hamilton]

Using ODBC Your SQL query will look more like this
"SELECT [Bank Account No_] FROM [CompanyName$Vendor Bank Account]"
where CompanyName will be the company database that you are querying.

Visual Studio returns ??? When the syntax for your SQL query is incorrect.

[Johnson Antony]

Try with table name enclosed with double quotes (") eg select * from "my table name". The same is applicable to field names too.

>> On Thursday, July 24, 2008 11:52 AM Volker Strähle wrote:

>> Did You try "[Bank Account No_]"?
>>
>> Volker

>>> On Friday, July 25, 2008 4:24 AM svetlana.shlapa wrote:

>>> I have tired
>>>
>>> myODBCommand.CommandText = "SELECT [Bank Account No_] FROM [Vendor Bank
>>> Account]"
>>>
>>> But when I run report I recieve error message:
>>> ERROR[42000][Simba][SimabEngine ODBC Driver]SELECT << ??? >>[Bank Account
>>> No_] FROM [Vendor Bank Account]
>>>
>>>
>>> What I am doing wrong?
>>>
>>>
>>>
>>> "Daniel Rimmelzwaan" wrote:

>>>> On Friday, July 25, 2008 7:47 AM Daniel Rimmelzwaan wrote:

>>>> I just went into my SQL Server query window and ran the following query:
>>>> select [Bank Account No_] from [Cronus USA$Vendor Bank Account]
>>>> This returned all Bank Account numbers without any errors.
>>>>
>>>> I don't know what kind of syntax you need with your application to
>>>> accomplish what you need to do, you will have to consult the documentation
>>>> for that. All I know is that you need to use square brackets for field and
>>>> table names when they have spaces in them. What you do seem to be missing is
>>>> the company name in the table name. Unless you modified some table
>>>> properties, the Vendor Bank Account table will be called "Company
>>>> Name$Vendor Bank Account" on SQL Server.
>>>>
>>>>

>>>> "Sveta" <svetlana...@yahoo.com> wrote in message

>>>> news:BA3E8F47-64CD-4EF2...@microsoft.com...

>>>>> On Wednesday, July 30, 2008 1:42 PM Adewale Abiol wrote:

>>>>> I believe Daniel is right. For certain tables in Navision 4.0 you will need
>>>>> to state the company names, before you can access them. I guess the tables
>>>>> excluded are company wide tables, which cut across all companies.

[Johnson Antony]

try enclosing table name and field name in double quotes(")
eg. select * from "my table"

> On Thursday, July 24, 2008 10:38 AM svetlana.shlapa wrote: