Updating Navision Item table thru C/ODBC

Business Central/NAV User Forum
1

Hi all,

I have been trying to figure this out for a number of days now.

I am programming with Microsoft Visual Studio C# language. I connect to Navision perfectly fine using ODBC and see all the tables i want to.

The Goal.
Update the ETA DATE and ETA QTY fields in the Navision Item table with new ETA DATE and new ETA QTY values. (very simple huh)

The Problem
When i use the sql update command:
“UPDATE Item SET “ETA QTY” = ‘31’, “ETA DATE” = '{d ’ “2006-12-01” ‘}’ WHERE No_ = ‘640145’”

I receive the following error:
{“ERROR [42000] [Simba][SimbaEngine ODBC Driver]UPDATE Item SET “ETA QTY” = ‘31’, “ETA DATE” = '”{d ‘“2006-12-01”<< ??? >>’}’ WHERE No_ = ‘640145’\r\nERROR [42000] [Simba][SimbaEngine ODBC Driver]Syntax Error."}

The Issue
It does not like the format of my date. Does anyone have any sample working sql code that updates a field of type date in Navision.
If not can someone please push me in the right direction. My problem is simple. I do not know the correct format of a date to update a date field inside a Navision table. I have switched up the sql code above in more ways then one to try and make it work with no luck. If someone can figure this out for me it would be greatly appreciated.

Thanks in Advance

Paul

2

Looks like this is a common problem, there are a ton of posts here that resolve it, take a look here : http://dynamicsuser.net/search/SearchResults.aspx?q=c%2Fodbc+date+format

I guess it needs a fully qualified Date Time.

3

Have you tried to use the SqlClient as part of the ADO.Net Namespace?

Just remember that it will plonk the data into the table without business logic, so as long as your fields are static it should be fine.

/TH

4

We are not using a SQL database. We are on the navision attain database so SqlClient has not been an option.

THanks

5

Did you look at any of those threads in the search link that I posted. They were not specific to SQL, and I do think they solve your problem?

6

Just one comment.

Date sintax isn’t {d ’ “2006-12-01” ‘}’ but is {d ‘2006-12-01’ '}. Have you checked this ?

7

Thanks for the response. But yes i have tryed that. the reason for the quotes is that i was just trying different ways to write the command hoping the driver would be able to work with it.

I still have not had any luck at updating a date field in Navision via ODBC. Any help would be greatly appreciated.

Any code snippents of sql update date type commands that have actually worked. I would actually love to see if anyone in the world has actually made it happen.

8

http://dynamicsuser.net/forums/permalink/71094/71090/ShowThread.aspx#71090

9

Just a silly question are you using {d ‘2006-12-01’ '} or {d ‘2006-12-01’ '} Notice whitespace after “d”

10

Take a look at :

Re: Date format for ODBC

and

Re: C/ODBC v2 and C/ODBC v3

11

Guys still no luck,

I am writing code in c# visual studio.

Here is the error i get when executing the update command.

notice the <<???>> thats where it says my error is. Any clues???

{System.Data.Odbc.OdbcException: ERROR [42000] [Simba][SimbaEngine ODBC Driver]UPDATE Item SET “ETA QTY” = ‘31’, “ETA DATE” = {ts ‘2006-12-01 00:00:00’}<< ??? >> WHERE No_ = ‘640145’
ERROR [42000] [Simba][SimbaEngine ODBC Driver]Syntax Error.

12

Guys still no luck,

I am writing code in c# visual studio.

Here is the error i get when executing the update command.

notice the <<???>> thats where it says my error is. Any clues???

{System.Data.Odbc.OdbcException: ERROR [42000] [Simba][SimbaEngine ODBC Driver]UPDATE Item SET “ETA QTY” = ‘31’, “ETA DATE” = {ts ‘2006-12-01 00:00:00’}<< ??? >> WHERE No_ = ‘640145’
ERROR [42000] [Simba][SimbaEngine ODBC Driver]Syntax Error.

13

Hi Paul,

I’ve tried to install N/ODBC on my machince, but am having some technical difficulties. The thing I wanted to check was the Simba call that your error is showing up. Is Simba used for N/ODBC I know MS are a Simba customer.

Also is your field “EA Date” a DT field or just a date field?

If I can overcome my NODBC install issue I will see if I can try to get a solution for this.

/TH

14

Yep, just confirmed it is Simba.

Will try to find a solution to this, still having issue with my ODBC

/TH

15

Hi Paul,

Just to let you know, I’ve not been able to get C/ODBC installed and working properly to try to find a solution for you.

I’ve got a stack load of work and personal stuff to sort out at the moment, and its gonna be a while before i can sit down and go at this again.

/TH