C/ODBC insert statement problem!

Z4k

I have to insert some data into a Navision 3.70 native table from a web
.asp page
This is what i do :
<%
riga = request.form("lettura_riga")
set objdbconnection = server.createobject("ADODB.Connection")
objdbconnection.open "mibuso"
sql = "INSERT INTO ""Movimenti Lotto"" (""Nr_ Movimento"") VALUES (riga)"
set rs=objdbconnection.execute(sql)
objdbconnection.close
%>

riga is the variable that came from the web form that calls the .asp page
I get an error message that is :
[Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver]Column not found: riga

why ?? ](*,)

riga is the name of the variable in the .asp page not a column in the Navision Table!! :shock:

I read in the C/ODBC guide that i have to use the parameter markers (?)
something like :
INSERT INTO ""Movimenti Lotto"" (""Nr_ Movimento"") VALUES (?)

and then bind the marker ? to the variable (in my case "riga" )

Which is the syntax to bind the marker (?) to the application variable ? :?:

Please Help!! ](*,) ](*,)
any other suggestion to insert data from a web page is welcome!

Charleh

The problem here is the way you are building the sql string,

the symbol (") is used to open and close a string, however if you want one of these symbols inside the string you have to use two ("") next to each other. This sql query is good up to the part with 'riga', you forgot to close the string so that riga is treated as a variable...!

So this code

sql = "INSERT INTO ""Movimenti Lotto"" (""Nr_ Movimento"") VALUES (riga)"

means that the word riga is part of the string, instead of the value of the variable

Change this to

sql = "INSERT INTO ""Movimenti Lotto"" (""Nr_ Movimento"") VALUES ('" & riga & "')"

(you need the ' and ' because the riga variable is a string - you must use ' around strings when inserting into a SQL database)

example

INSERT INTO "Movimenti Lotto" ("Nr_Movimento") VALUES ('hola!')

that should fix it.

NaveenReddy

Hi guys,
I tried to connect to navision database using the dsn connection and asp conn strings, But i was never been successfull. Can you please tell me how to connect to navision database.
Is anyone able to connect to navision databse ?
Naveen Reddy

Charleh

Hello Naveen, could you go into more specific details about your problems? What did you do? What error messages did you get when trying to connect?

Z4k

Thank You Charleh!!

Now i have a problem with the insert of a Date.

the sql statement before VALUES is "{d' " & date() & "'})"

but i get this kind of error Expected lexical element not found: <identifier>

Z4k

Doesn't matter i got it!

visgold

Hi
Z4

I am doing the same thing as trying to insert data into Navision DB via ASP but unable to connect them as i am not using DSN i want it through conn string can u tell me the source code to do this so that i can insert data through ASP in navision table

Thanks in advance

Vishal

Faberman

Hello.

Firstly, Visgold - have a look at my last post in "ASP With Native Navision DB" to make a connection, it might help.


I am trying to run more specific queries on Navision tables than "SELECT * FROM <tablename>"

My query is :

strSQL="SELECT No.,Name,Address,Address 2,City FROM Customer WHERE Department Code='Retail'"

Normally a simple thing in Access, SQLserver, it does not work with C/ODBC

I get the following error :

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver]Expected lexical element not found: FROM

Can anyone shed some light on the syntax to use to make this work?
Is this functionality available with the C/ODBC driver (pre Navision 4.0)?

I have a nasty feeling it is not.

p.s. I have set "identifiers" in the "options" part of the C/ODBC configuration to "All Characters", hence fieldnames such as "No."

:-k