ADO automation Select query problem

Kisu · 2011-09-13

Hmm I'm practising using ADO with navision as I'm needing it later to retreave data from external database (which will be located in same domain) but somehow I dont get the SELECT statement to work

IF ISCLEAR(lADOConnection) THEN
  CREATE(lADOConnection);

lADOConnection.ConnectionString := 'Driver={SQL Server}; Server=ServerX; Database=DatabaseX; UID=UserX; PWD=PasswordX';

lADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN
  CREATE(lADOCommand);

lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;

lADOCommand.CommandText := 'SELECT * FROM [dbo].[User]';
lADOCommand.Execute;

ltxtResult := FORMAT(lADOCommand.Parameters.Item('@Result').Value);   

MESSAGE(ltxtResult); 

lADOConnection.Close;
CLEAR(lADOConnection);

I tried to retrieve users from other Navision database, there should be just one user, then pop it as message for test.

Any help what could I do wrong here. Running this gives me next error message:
The call to member Item failed. ADODB.Parameters returned the following message:
Object can not be found from the set that corresponds to requested name or number.

I'm quite sure the database connection is working as it does not give error before the query.

mohana_cse06 · 2011-09-13

there is a nice blog by Waldo here
http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx

Kisu · 2011-09-13

mohana_cse06 wrote:

there is a nice blog by Waldo here
http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx

hmm yes I know, Waldo's blog found me back here as I didn't get the query working. I made a stored procedure of that query also but it gives me same error :-k

mohana_cse06 · 2011-09-13

I tried by using stores procedure and got the result set back and displayed in my report also..

try by removing this code

lADOCommand.CommandText := 'SELECT * FROM [dbo].[User]';
lADOCommand.Execute;

ltxtResult := FORMAT(lADOCommand.Parameters.Item('@Result').Value);   

MESSAGE(ltxtResult);

Kisu · 2011-09-13

lADOConnection.State returns 1 so I assume that means the database connection is open, so yeh there is something wrong on my query :-k

mohana_cse06 · 2011-09-13

Kisu wrote:

Object can not be found from the set that corresponds to requested name or number.

I think the query is not able to identify [dbo].[User] table..

Kisu · 2011-09-13

mohana_cse06 wrote:

Kisu wrote:

Object can not be found from the set that corresponds to requested name or number.

I think the query is not able to identify [dbo].[User] table..

I added a dbo.SP_get_user procedure and called it but it gives same result. I tested the query in management studio and it retreaves the user.

Kisu · 2011-09-13

Here I tried using the stored procedure

it state returns fail

mohana_cse06 · 2011-09-13

in my code I have

lADOCommand.CommandText := 'SP_Test';
lADOCommand.CommandType := 4; 
lADOCommand.CommandTimeout := 0;

where SP_Test is a stored procedure..

Kisu · 2011-09-13

mohana_cse06 wrote:
in my code I have
lADOCommand.CommandText := 'SP_Test';
lADOCommand.CommandType := 4; 
lADOCommand.CommandTimeout := 0;
where SP_Test is a stored procedure..

Tried that way, still fails. Its realy odd :-k
I'm gonna test this thing again tomorrow, maybe make simple database and test it that way or try another table from the database. Maybe there is something with navision's user table.

mohana_cse06 · 2011-09-13

I think state = 1 does not mean executed

check this
http://www.w3schools.com/ado/prop_comm_state.asp
http://www.devguru.com/technologies/ado/quickref/command_state.html

mohana_cse06 · 2011-09-13

and with the below code am able to run the query without any error

IF ISCLEAR(lADOConnection) THEN 
  CREATE(lADOConnection); 
lADOConnection.ConnectionString:=
  'Driver={SQL Server};' 
      + 'Server='+'XYZ'+';'
      + 'Database='+'ABC'+';'
      + 'Uid=test;'
      + 'Pwd=navision;';

lADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN 
  CREATE(lADOCommand);    

lvarActiveConnection := lADOConnection; 
lADOCommand.ActiveConnection := lvarActiveConnection;

lADOCommand.CommandText := 'SELECT * FROM [dbo].[User]';

lADOCommand.Execute;

Message('%1',lADOCommand.State);

lADOConnection.Close; 
CLEAR(lADOConnection);

Kisu · 2011-09-13

mohana_cse06 wrote:

I think state = 1 does not mean executed

check this
http://www.w3schools.com/ado/prop_comm_state.asp
http://www.devguru.com/technologies/ado/quickref/command_state.html

Hmm okay, it returns 0 so object is closed. Probably as no result or faulty query.

The DatabaseX on those earlier tests I did is default database which came with Navision, just user test on it with super user role.

The ADO automation and the test is done on another default navision database with super user role. The user I used for the test UserX has sysadmin rights for the DatabaseX

ADO version is 2.8

Kisu · 2011-09-13

mohana_cse06 wrote:

and with the below code am able to run the query without any error

IF ISCLEAR(lADOConnection) THEN 
  CREATE(lADOConnection); 
lADOConnection.ConnectionString:=
  'Driver={SQL Server};' 
      + 'Server='+'XYZ'+';'
      + 'Database='+'ABC'+';'
      + 'Uid=test;'
      + 'Pwd=navision;';

lADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN 
  CREATE(lADOCommand);    

lvarActiveConnection := lADOConnection; 
lADOCommand.ActiveConnection := lvarActiveConnection;

lADOCommand.CommandText := 'SELECT * FROM [dbo].[User]';

lADOCommand.Execute;

Message('%1',lADOCommand.State);

lADOConnection.Close; 
CLEAR(lADOConnection);

The state returns 0 too (with my db and server name of course), server's state function returns 1.
Thank you for clearing up this, it shows there is something up with the database itself and not these tests.
The syntax is pretty much same as mine and even using same code same results as before clarifies that I'd need to check the database.

It is weird though that the tsql query from management studio retreaves the data :l

mohana_cse06 · 2011-09-14

Hi GM

..

the reason for this error message is

ltxtResult := FORMAT(lADOCommand.Parameters.Item('@Result').Value);

the program didnt find a field @Result in User table..

If your goal is to get the all use Id's..try below code

IF ISCLEAR(lADOConnection) THEN
  CREATE(lADOConnection);

lADOConnection.ConnectionString := 'Driver={SQL Server}; Server=ServerX; Database=DatabaseX; UID=UserX; PWD=PasswordX';

lADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN
  CREATE(lADOCommand);

lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;

lADOCommand.CommandText := 'SELECT * FROM [dbo].[User]';
lADOCommand.Execute;

IF ISCLEAR(lADORecordset) THEN
  CREATE(lADORecordset);

lADORecordset.ActiveConnection := lvarActiveConnection; 
lADORecordset.Open(lADOCommand);    
lADORecordset.MoveFirst;
WHILE NOT lADORecordset.EOF DO BEGIN 
  MESSAGE('%1',FORMAT(lADORecordset.Fields.Item('USER ID').Value));
  lADORecordset.MoveNext;
END;

lADOConnection.Close;
CLEAR(lADOConnection);

ADO automation Select query problem

Comments

Categories