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

coommark
Member Posts: 17
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
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
0
Comments
-
SQLString := 'insert into testdata(texttest) values(' + CustName + ')';
Try this.There are no bugs - only undocumented features.0 -
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.
Melton0 -
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 + '''')';David Machanick
http://mibuso.com/blogs/davidmachanick/0 -
Hi
what is this line UNTIL BankAct.NEXT = 0;???? where is cust.next-=0???0 -
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0
This discussion has been closed.
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions