Options

C/ODBC insert statement problem!

Z4kZ4k Member Posts: 10
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!

Comments

  • Options
    CharlehCharleh Member Posts: 44
    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.
    Charleh
  • Options
    NaveenReddyNaveenReddy Member Posts: 53
    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
  • Options
    CharlehCharleh Member Posts: 44
    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?
    Charleh
  • Options
    Z4kZ4k Member Posts: 10
    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>
  • Options
    Z4kZ4k Member Posts: 10
    Doesn't matter i got it!
  • Options
    visgoldvisgold Member Posts: 21
    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
  • Options
    FabermanFaberman Member Posts: 24
    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
Sign In or Register to comment.