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.
K.S.
Comments
http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav
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
try by removing this code
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav
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.
it state returns fail
where SP_Test is a stored procedure..
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav
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.
check this
http://www.w3schools.com/ado/prop_comm_state.asp
http://www.devguru.com/technologies/ado/quickref/command_state.html
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav
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
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
the reason for this error message is the program didnt find a field @Result in User table..
If your goal is to get the all use Id's..try below code
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav