Using Variables to Send a Query to SQL Server from C/SIDE

coommark

Hello. I need some help with using variables to send an Insert query into SQL Server. Let me describe the scenario.

I want to update (periodically) a SQL Server database from inside Nav. I am using ADO to make the connection with success. However, i cant send a query to SQL Server using a variable. Below are the variables i used

Name DataType Subtype Lenght
Cust Record Customer
CustName Text 50

CODE:
WITH Cust DO BEGIN
IF Cust.FIND('-') THEN BEGIN
REPEAT
CustName := Cust."Account Name";
SQLString := 'insert into testdata(texttest) values(CustName)';
ADORecSet := ADOConnection.Execute(SQLString);
UNTIL BankAct.NEXT = 0;
END;
END;

When i run the codeunit, it returns "The Call to member Execute failed. Microsoft OLE DB Provider returned the following message: The Name CustName is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted"

However, when i use '' '' around CustName, it runs through the table and creates a record each for every customer, except that is uses the literal CustName as the name of all the customer.

Please what do i need to do?

Thank you all in advance.

Melton

bbrown

SQLString := 'insert into testdata(texttest) values(' + CustName + ')';

Try this.

coommark

Hello bbrown. Oh thank you soooo very much.

Yes there is some progress. Now the error message is "The call to member Execute failed. Microsoft OLE DB Provider for SQL Server returned the following message: The name 'Mark' is not permitted in this context. Only constants, expressions, or variables are allowed here. Column names not permitted"

The first customer on the Customer table is Mark Melton, which is to say the variable now picks the values in that field. But the problem is, well, still, it cant insert into the SQL database, the values of the variable CustName.

I believe there is a solution to this. Please help me if you can. What do i need to do here?

Thanks for the help. Waiting in anticipation.

Thanks again.

Melton

davmac1

I f you were entering the sql statement, you would need to put the name in quotes, so you will need to add quotes - 2 single quotes together in NAV will output one single quote.

SQLString := 'insert into testdata(texttest) values(''' + CustName + '''')';

ashbhardwaj

Hi
what is this line UNTIL BankAct.NEXT = 0;???? where is cust.next-=0???

kriki

[Topic closed because of double posting]
See http://www.mibuso.com/forum/viewtopic.php?t=32722