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()

Xypher

Hmmm... have you tried surrounding the objects that have spaces with []?

ex: "SELECT [Bank Account No_]"


--- edit ---
Actually I think the correct syntax would be @[Name], making it...

"SELECT @[Bank Account No_]"

garak

Field Names with SPACE (Like Bank Account No) you must write in SQL like [Bank Account No]. Fields with an dot in NAV you must write as NO_. fields with SPACE and DOTS you must write as [MY Field Woth DOT_]

Regards

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?

DenSter

This was answered in the public newsgroup, so copied from there:

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.

garak

You write following:

"SELECT [Bank Account No_] FROM [Vendor Bank Account]" 

But the Datas are stored in the table

YOURDATAB_ASENAME.Shema.[COMPANYNAME$Vendor Bank Account]

so if you has created the database under dbo account (i hope this ;-) ) the full name of table [Vendor Bank Account] is:

dbo.[YOURCOMPANYNAME$Vendor Bank Account] (you can leave out dbo)

so, write following (if you are connected to your database):

select [Bank Account No_] 
  from [CompanyName$Vendor Bank Account]

if you have a tabel with Navision Porperty "DataPerCompany = NO" then leave out the COMPANYNAME on your SQL querry.

Regards