The Length of the text string exceeds the size of the string

kmkaot

Dear All,

I want get the data from SQL tables directtly. I have used ADO for connection. Connection is ok.

When I want get results in Record set it is giving an error ' The Length of the text string exceeds the size of the string buffer'

[code]Name	DataType	Subtype	Length
conn	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Connection	
LADOCommand	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Command	
ADOConnection	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Connection	
ADORecSet	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Recordset	
ADOStream	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Stream	
SQLString	Text		1024
ConnString	Text		250
RecordsAffected	Text		1
RSOption	Integer		
FieldValue	Text		100
DatabaseName	Text		30
NewDate	Date		
Lastdate	Date		
firstdate	Date		
VARSQLString	Variant		
mdate	Text		30
kmk	Variant		

IF ISCLEAR(ADOConnection) THEN
BEGIN
IF NOT CREATE(ADOConnection) THEN
BEGIN
ERROR(Text002);
END;
END;

IF ISCLEAR(ADORecSet) THEN
BEGIN
IF NOT CREATE(ADORecSet) THEN
BEGIN
ERROR(Text002);
END;
END;
ADORecSet.MaxRecords(10000);

IF ISCLEAR(conn) THEN
BEGIN
IF NOT CREATE(conn) THEN
BEGIN
ERROR(Text002);
END;
END;



DatabaseName:='TestDataSql';
ConnString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog='+DatabaseName+';Data Source=KRISHNA';
kname:='Binesh';
RecordsAffected:=' ';
RSOption:=0;
//singleQuote:='';


SQLString:='SELECT FGROUPCODE,FLOCATIONCODE,FWAREHOUSECODE,FWAREHOUSENAME FROM MAS001';

MESSAGE(SQLString);
ADOConnection.ConnectionString(ConnString);
ADOConnection.Open;
ADOConnection.Execute(VARSQLString);


RecordsAffected:=' ';
RSOption:=0;


ADORecSet:=ADOConnection.Execute(VARSQLString,RecordsAffected,RSOption);
ADORecSet.MoveFirst;

FieldValue:=ADORecSet.Fields.Item('pname').Value;
MESSAGE(FieldValue);[/code]

Kau_147

Dear,

Try with increase the length of all the variables & check.

Thanks,
Kavita M. Mutha

geordie

I suppose is "RecordsAffected" variable length...think about what happen when you retrieve more than 9 records with your SQL statement

kmkaot

You are right.

I am getting the values.

1. I need to check for big record
2. Put the same in loop to get all values into my Nav table.

Thank you, best catch.

kmkaot

It was nice help actually.

But I am still in trouble to get NULL values in my NAV Table. Unable to convert to field type .

Please help

         Myvar := Myvar +1;
// How to check if it is null?
         AttendanceData.INIT;
         AttendanceData.EntryNum:= vlastno;
         AttendanceData.PName:=   ADORecSet.Fields.Item('pName').Value;
         AttendanceData.DeptName:= ADORecSet.Fields.Item('DeptName').Value ;
         AttendanceData.PNumber:= ADORecSet.Fields.Item('PNumber').Value;
         AttendanceData.Date:=   ADORecSet.Fields.Item('attDate').Value ;
          IF KinTime <> 0D THEN
              BEGIN
              AttendanceData.Intime:=   CREATEDATETIME(KinTime,0T);
              AttendanceData.Outtime:=  CREATEDATETIME(kOutTme,0T);
              AttendanceData.total:= ADORecSet.Fields.Item('Total').Value ;
        /     END;

kmkaot

Please check the code I want fetch records into NAV tables

Intime date and time
Outtime Date and time
Deptname text
Totals Integer

     ADORecSet:=ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
     ADORecSet.MoveFirst;

      REPEAT
      
     KinTime:= ADORecSet.Fields.Item('InTime').Value;
     kOutTme:= ADORecSet.Fields.Item('OutTime').Value;

         Myvar := Myvar +1;
         AttendanceData.INIT;
         AttendanceData.EntryNum:= vlastno;
         AttendanceData.PName:=   ADORecSet.Fields.Item('pName').Value;
         AttendanceData.DeptName:= ADORecSet.Fields.Item('DeptName').Value ;
         AttendanceData.PNumber:= ADORecSet.Fields.Item('PNumber').Value;
         AttendanceData.Date:=   ADORecSet.Fields.Item('attDate').Value ;
          IF KinTime <> 0D THEN
              BEGIN
             AttendanceData.Intime:=   CREATEDATETIME(KinTime,0T);
             AttendanceData.Outtime:=  CREATEDATETIME(kOutTme,0T);
             AttendanceData.total:= ADORecSet.Fields.Item('Total').Value ;
              END;
         AttendanceData.INSERT;
         vlastno:=vlastno+1;
         ADORecSet.MoveNext();
      UNTIL ADORecSet.EOF();

kmkaot

Please check the error