Inserting value in a sql table from native database

anuragatish · 2007-12-22

Hi,

i want to insert some updated records from a nav table in native database to a sql table of similar structure

i have sucessfully made the connection and 'm able to insert record only if i insert all the records by there exact value.
eg. 'Insert into [UPS Interface]([doc_no])
values ('doc-10000')

but my problem is that, while running the function i have to pass the table record to the sql tables , which are as variables with me.

eg. Interface_rec."Document No."

and if i try to insert it through
SQLString := 'insert into [UPS Interface]([doc_no])
values([Interface_rec."Document No."])
it throws syntax error.

how do i insert the rec values into the sql table.

and second prob is that i'm not being able to write long SQLString.
how do i write a SQLString which spans into more than one line in C/Side.

Thanks and regards
Anurag

NaviDev · 2007-12-23

What do you used in writing to SQL? In case you can use ADO. Search the forum with "ADO" you can see lots of posting regarding this one. Cheers!

anuragatish · 2007-12-26

hi buddy,
thanks for the reply.
But i had mentioned in my post that i have made the connection and i can retrieve and insert record to an outside sql table .

The connection was made using ADO only.

my problem was that i was not able to pass rec type variables in the sql string .

Now i have found the way..the correct syntax would be as follows..

sqlstring = 'insert into adotest..table([id], [name]) values ('''rec.id''','''rec.name''')

. you just have to run the debugger and see that string being passed to sql is correct in syntax.

Cheers and merry christmas to all

cnicola · 2007-12-26

Simpler way would be to use linked tables. You can link a Navision table with a view or table on a SQL db and then making modifications in one will reflect in the other.

anuragatish · 2007-12-26

thanks friend,

but as far as i know, i can use this trick only with navision's sql db. its not possible with native db.

i tried linking the native db in sql. but all in vain.

if anyone knows the correct way of linking the native nav db in sql db..then please respond...

Savatage · 2007-12-26

Can you explain how you solved it so others with a similar issue in the future can resolved their problem.

Inserting value in a sql table from native database

Answers

Categories