Error running variable ADO

hugoisti
Member Posts: 26
Hi to all the experts, need spend two parameters to a SP sql, from a Form in Navision, the code is the next
//Here create connection to the base and to the server
g_recDatabase.RESET;
g_recDatabase.SETRANGE("My Database", TRUE);
IF g_recDatabase.FIND('-') THEN BEGIN
g_txtDbName := g_recDatabase."Database Name";
END;
g_recServer.RESET;
g_recServer.SETRANGE("My Server", TRUE);
IF g_recServer.FIND('-') THEN BEGIN
g_txtSvrName := g_recServer."Server Name";
END;
IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection);
lADOConnection.ConnectionString:=
'Provider=SQLOLEDB.1;' +
'User ID= user;' +
'Password=pass;' +
'Initial Catalog='+ DELCHR(g_txtDbName, '>', ' ') + ';' + 'Data Source=' + DELCHR(g_txtSvrName, '>', ' ');
lADOConnection.Open;
//Run the SP
IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);
lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;
lADOCommand.CommandText := 'sp_Compel_test';
lADOCommand.CommandType := 4;
lADOCommand.CommandTimeout:= 180;
l_txtParam1 := FORMAT(g_dateDateFrom,0,9);
l_txtParam2 := FORMAT(g_dateDateTo,0,9);
lADOParameter:=lADOCommand.CreateParameter('@Date_Frm', 133,1,0,l_txtParam1);
lADOCommand.Parameters.Append(lADOParameter);
lADOParameter:=lADOCommand.CreateParameter('@Date_To', 133,1,0,l_txtParam2);
lADOCommand.Parameters.Append(lADOParameter);
window.OPEN('Executing Store Procedure...');
lADOCommand.Execute;
window.CLOSE();
lADOConnection.Close();
The SP receives two paramaters datetime. It is very rare that happens, because when you run for the first time goes well, takes the dates passes by parameter and the SP creates the csv, Now, when run again strip an error which reads as follows:
"An exeption was raised in method Append. The OLE control or Automation server has returned error (HRESULT) -2147352567. The component did not provide the exception description."
was reading and testing a bunch of things and the truth that is no longer where you can be a problem.
Since already Thank you very much.
Best Regards.
//Here create connection to the base and to the server
g_recDatabase.RESET;
g_recDatabase.SETRANGE("My Database", TRUE);
IF g_recDatabase.FIND('-') THEN BEGIN
g_txtDbName := g_recDatabase."Database Name";
END;
g_recServer.RESET;
g_recServer.SETRANGE("My Server", TRUE);
IF g_recServer.FIND('-') THEN BEGIN
g_txtSvrName := g_recServer."Server Name";
END;
IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection);
lADOConnection.ConnectionString:=
'Provider=SQLOLEDB.1;' +
'User ID= user;' +
'Password=pass;' +
'Initial Catalog='+ DELCHR(g_txtDbName, '>', ' ') + ';' + 'Data Source=' + DELCHR(g_txtSvrName, '>', ' ');
lADOConnection.Open;
//Run the SP
IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);
lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;
lADOCommand.CommandText := 'sp_Compel_test';
lADOCommand.CommandType := 4;
lADOCommand.CommandTimeout:= 180;
l_txtParam1 := FORMAT(g_dateDateFrom,0,9);
l_txtParam2 := FORMAT(g_dateDateTo,0,9);
lADOParameter:=lADOCommand.CreateParameter('@Date_Frm', 133,1,0,l_txtParam1);
lADOCommand.Parameters.Append(lADOParameter);
lADOParameter:=lADOCommand.CreateParameter('@Date_To', 133,1,0,l_txtParam2);
lADOCommand.Parameters.Append(lADOParameter);
window.OPEN('Executing Store Procedure...');
lADOCommand.Execute;
window.CLOSE();
lADOConnection.Close();
The SP receives two paramaters datetime. It is very rare that happens, because when you run for the first time goes well, takes the dates passes by parameter and the SP creates the csv, Now, when run again strip an error which reads as follows:
"An exeption was raised in method Append. The OLE control or Automation server has returned error (HRESULT) -2147352567. The component did not provide the exception description."
was reading and testing a bunch of things and the truth that is no longer where you can be a problem.
Since already Thank you very much.
Best Regards.
0
Comments
-
Already settled!!!!! \:D/
After several hours of reading and prove ](*,) , find another way to move the parameters to SP 8) , leave the solution and I hope that someone else to serve. greetings and thanks to all.
sSql:=STRSUBSTNO('EXEC sp_Compel_test ''%1'',''%2''', l_txtParam1, l_txtParam2);
lADOConnection.Execute(sSql);0 -
I just stumbled on this solution while searching for a solution to different problem.
Please be VERY careful, that you prevent SQL Injections while using this method...
http://en.wikipedia.org/wiki/SQL_injectionRegards
Peter0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions