ADO Recordset error

davisj

Hi All! I'm running into a bit of a problem when trying to use ADO to connect to another Navision Database. Here is my code...


CREATE(lADOConnection);
CREATE(lADORecordSet);

lADOConn.Open('Provider=SQLOLEDB.1;Password=xxxx;User ID=xxxx;Initial Catalog=MyDatabase;Data Source=MyServer);

lSQL:='SELECT [Scrap Mileage], [Scrap Count] FROM [Company$Manufacturing Setup]';
lADORecordset.Open(lSQL,lADOConnection);

lScrapCount:=lHTSMfgSetup.Fields.Item('[Scrap Count]').Value;
lScrapMileage:=lHTSMfgSetup.Fields.Item('[Scrap Mileage]').Value;

Here's the problem.... When the code gets to the line that is supposed to assign the recordset field value to my variable, I get the following error:

"The call to member Item Failed. ADODB Fields returned the following message: Item cannot be found in the collection corresponding to the requested name or ordinal"

I'm not sure why I'm getting this!!!! ](*,)

lADORecodset is defined as 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset, so I figure this should work fine.

Anyone run into this problem????

Thanks in advance
Jim

jsnayberk

...just a blindshot, try this:

lScrapCount:=lHTSMfgSetup.Fields.Item('Scrap Count').Value;

davisj

Thanks for the suggestion. It got me pointed in the right direction! Apparently, fields with spaces in them aren't liked by the ADO Recordset. So I ended up giving the field names aliases in my SQL Statement.

SELECT [Scrap Mileage] AS ScrapMileage, [Scrap Count] AS ScrapCount FROM [Manufacturing Setup]

wonmo

You might also try using 'CAST' in your SQL statement so that the Item property can refer to a "field" with no spaces. If you want to use spaces, I think you may have to use double quotes.