Using C/SIDE Variables in SQL Queries

coommark

Please i need your help, desperately.

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 C/SIDE 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 Cust.NEXT = 0;
END;
END;

When i run the codeunit, it iterates through the C/SIDE table as it should, but inserts +CustName+ into the SQL Server table as the name of every of the customers, instead of substituting the variable with the values its supposed to hold. When i use single quotes and the plus sign like this '+CustName+', it picks the first name of the first customer (Mark, the complete name there is Mark Melton) and throws this exception "The Name Mark is not permitted in this context, only constants, expressions or variables are allowed here.

Please what do i need to do? Waiting eagerly.

Melton

coommark

Hello. This is the whole codeunit (Variables + Code i used above). Thanks. Waiting...

Name	             DataType	Subtype	                                                               Length
ADOConnection	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Connection	
ADORecSet	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Recordset	
ADOStream	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Stream	
SQLString	Text		                                                                             1000
Cust	             Record	              Customer	
CustName	Text		                                                                             50


IF ISCLEAR(ADOConnection) THEN BEGIN
      IF NOT CREATE(ADOConnection) THEN BEGIN
      ERROR('Cannot create ADO Connection automation variable.');
   END;
END;

IF ISCLEAR(ADORecSet) THEN BEGIN
      IF NOT CREATE(ADORecSet) THEN BEGIN
         ERROR('Cannot create ADO Recordset automation variable.');
   END;
END;

IF ISCLEAR(ADOStream) THEN BEGIN
      IF NOT CREATE(ADOStream) THEN BEGIN
         ERROR('Cannot create ADO Stream automation variable.');
   END;
END;


ADOConnection.ConnectionString('Provider=SQLOLEDB;Data Source=ITRENDS-8T82Z8O;'
     + 'Initial Catalog=adotest;User ID=mark;Password=test;');
ADOConnection.Open;



//Insert A record into SQL Server

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


//ADORecSet.Close;
//ADOConnection.Close;

garak

you can use also instead of the stringconcetenation a TEXTConst like in this example:

Name	          ConstValue
SQLConstInsert	   insert into MyTableOnSQL(Field1,Field2,Fieldx) values('%1','%2','%3')
-----
ADORecSet := ADOConnection.Execute(STRSUBSTNO(SQLConstInsert,VariableOrAField,'Rtsd',format(4)));

Check also, if you not fill all fields in the table, if the fields on the sqldatabase allowes NULL values.

Regards

coommark

Hi Garak, thanks for the instructions. I tried all you said, substituted VariableOrAField with both the right variable and field alternatively. However it throws this exception

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

Looks like its SQL server that is rejecting the value sent to it. Could it be a problem of type conversion? The first name on the Customer table is Mark Melton, a text field, of Lenght 50. The corresponding SQL field i am trying to inject into is a varchar, 100. What is wrong here?

Please help me out. Grrrrr :bug:

Waiting actively, anticipating...

garak

Write the command in a textfile or in a message to copy it and to see the sql command self.
Can u run the copied command directly in the query analizer?

coommark

Yes the command runs well in Query Analyzer. I executed it and it added the record.

This is the command i ran in SQL Query Analyzer: insert into testdata (texttest) values ('Mark Melton').

So help me God! What is wrong?

freddy.dk

My suggestion would be:

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

coommark

Oh Freddy! Wow! You are too much! Oh thank you soo much!

It worked! It did! I will post a full procedure for other later. Thanks alot! Thank you BBrown and Garak too. You all helped! Now i will not get fired by my customer! Bingo!

Mark!

kriki

@coommark:
1) Can you give a good title to your topic, so that it is possible for someone else, who has the same problem, to find your topic.
2) Can you also change the attribute of the topic to Solved.

coommark

Ok kriki. I have done both. Thanks for the instructions.

Blessings...