BrianGooch
Programmer
Runtime Error from TADOQuery and TADOCommand
Error message:
'Arguments of the wrong type, out of range, or in conflict with one another.'
The following may help to explain some of our questions in previous posts.
The whole of these TADO aspects have arisen because we wish to take the construct of a Server SP and use that info through to the Client target without writing more than a Delphi handler which can cope with a wide range of SPs. Comments received in respect of "Declare Parameter" (?not Delphi), "using the code inspector", have not been helpful: the app is doing its stuff completely on the fly using the Server SP as the starting point.
We took as the basis of our INSERT statement the following code fragment from the on-line Help.
Delphi Example.
with ADOCommand1 do begin
CommandText := 'INSERT INTO Talley ' +
'(Counter) ' +
'VALUES
NewValueParam)';
CommandType := cmdText;
Our input data comes from the output parameters of a TADOStoredProc on the Server. As we had discovered you cannot copy TADOstored Proc Parameters to a TADOQuery, and we wanted to construct the INSERT SQL on the fly we decided use a loop to create the statement. The Stored Proc Parameter Names were obtained from the Server system tables, and have exactly the same names as the Client target column names.
Please ignore any typos: we've lifted the code out of the test project but have aligned the names here for continuity. All the code compiled OK.
Next test: as the parameters are in a pre-determined sequence we used:
CommandText:= 'INSERT INTO <tablename> ('
{LOOP of i= 1 to 40} SP_Parm.Name, ..) '+
VALUESSP_Parm[1].Name, .. )';
{LOOP of assigning values using the form
SP_Parm.ParmByName('< SP_Parm.Name>').asString }
Compiled OK; above runtime error.
Next we tried being precise about the datatype, also obtained from the Server system table, and a case statement inserted into the loop:
CommandText:= 'INSERT INTO <tablename> ('
{LOOP of i= 1 to 40} SP_Parm.Name, .. ) '+
VALUESSP_Parm[1].Name, .. )';
{LOOP of assigning values using the form
SP_Parm.ParmByName('< SP_Parm.Name>').as<datatype> }
Compiled OK; above runtime error.
At this stage we thought we might be doing the loop incorrectly, so we wrote out the whole of the INSERT by hand, thus:
CommandText:= 'INSERT INTO <tablename> ('col1, col2, .., col40)'+
'VALUES ('arm1, arm2, .., arm40)';
Compiled OK; above runtime error.
At one stage we also tried a double loop searching for the Parameter Name but that does not work since there is no TADOQuery Parameter Collection to loop through (even if a TADOCommand is used).
We thought the mismatch in the error might refer to the use of column names, so we left them out:
CommandText:= 'INSERT INTO <tablename> VALUES ('arm1, .., arm40)';
Compiled OK; above runtime error.
No change! We tried all of the above using TADOQuery instead of TADOCommand with the SQL:= <each of the above statements in turn>;
Result: exactly the same as before: above runtime error.
By this time it had dawned on us that 'Arguments' were not a Delphi term - it comes from C, C++. It also occurred to us that there might be a mismatch between the Server datatypes and Delphi datatypes, so we added a case statement into the loop (see test above). This made no difference.
One other, but I expect could be a significant point, is that: when running the Delphi debugger one line of code at a time, the above error message occurs on
i) the first line of code : SQL.Add('INSERT ..'); or
ii) at the end of CommandText:= 'INSERT ..'+ '..' etc
At no time did the debugger ever get to the ExecSQL in the Try..finally loop.
There was no difference either whether ParamCheck was True or False, or whether 'Prepared:=True' was called or not.
As we had never used TADO before this current exercise, we had used the constructs which we had previously used in respect of TParams and thought, perhaps naively, that TParameters would be the same. By this time we had carried out some 100+ tests (including trying to copy parameters and assign values) and were no nearer the a working piece of code as we had with the other standard components. So we wondered what we were doing wrong: we were trying to apply a technique which for years worked elsewhere, but not here.
We are therefore interested in
a) why TADOQuery does not have a Collection of Parameters
b) why would the Delphi example code work as at the beginning of this piece, but apparently not with as few as 40 parameters (we can sometimes have 80, 90+).
Error message:
'Arguments of the wrong type, out of range, or in conflict with one another.'
The following may help to explain some of our questions in previous posts.
The whole of these TADO aspects have arisen because we wish to take the construct of a Server SP and use that info through to the Client target without writing more than a Delphi handler which can cope with a wide range of SPs. Comments received in respect of "Declare Parameter" (?not Delphi), "using the code inspector", have not been helpful: the app is doing its stuff completely on the fly using the Server SP as the starting point.
We took as the basis of our INSERT statement the following code fragment from the on-line Help.
Delphi Example.
with ADOCommand1 do begin
CommandText := 'INSERT INTO Talley ' +
'(Counter) ' +
'VALUES
NewValueParam)'; CommandType := cmdText;
Our input data comes from the output parameters of a TADOStoredProc on the Server. As we had discovered you cannot copy TADOstored Proc Parameters to a TADOQuery, and we wanted to construct the INSERT SQL on the fly we decided use a loop to create the statement. The Stored Proc Parameter Names were obtained from the Server system tables, and have exactly the same names as the Client target column names.
Please ignore any typos: we've lifted the code out of the test project but have aligned the names here for continuity. All the code compiled OK.
Next test: as the parameters are in a pre-determined sequence we used:
CommandText:= 'INSERT INTO <tablename> ('
{LOOP of i= 1 to 40} SP_Parm.Name, ..) '+
VALUES
SP_Parm[1].Name, .. )';{LOOP of assigning values using the form
SP_Parm.ParmByName('< SP_Parm.Name>').asString }
Compiled OK; above runtime error.
Next we tried being precise about the datatype, also obtained from the Server system table, and a case statement inserted into the loop:
CommandText:= 'INSERT INTO <tablename> ('
{LOOP of i= 1 to 40} SP_Parm.Name, .. ) '+
VALUES
SP_Parm[1].Name, .. )';{LOOP of assigning values using the form
SP_Parm.ParmByName('< SP_Parm.Name>').as<datatype> }
Compiled OK; above runtime error.
At this stage we thought we might be doing the loop incorrectly, so we wrote out the whole of the INSERT by hand, thus:
CommandText:= 'INSERT INTO <tablename> ('col1, col2, .., col40)'+
'VALUES ('
arm1, arm2, .., arm40)';Compiled OK; above runtime error.
At one stage we also tried a double loop searching for the Parameter Name but that does not work since there is no TADOQuery Parameter Collection to loop through (even if a TADOCommand is used).
We thought the mismatch in the error might refer to the use of column names, so we left them out:
CommandText:= 'INSERT INTO <tablename> VALUES ('
arm1, .., arm40)';Compiled OK; above runtime error.
No change! We tried all of the above using TADOQuery instead of TADOCommand with the SQL:= <each of the above statements in turn>;
Result: exactly the same as before: above runtime error.
By this time it had dawned on us that 'Arguments' were not a Delphi term - it comes from C, C++. It also occurred to us that there might be a mismatch between the Server datatypes and Delphi datatypes, so we added a case statement into the loop (see test above). This made no difference.
One other, but I expect could be a significant point, is that: when running the Delphi debugger one line of code at a time, the above error message occurs on
i) the first line of code : SQL.Add('INSERT ..'); or
ii) at the end of CommandText:= 'INSERT ..'+ '..' etc
At no time did the debugger ever get to the ExecSQL in the Try..finally loop.
There was no difference either whether ParamCheck was True or False, or whether 'Prepared:=True' was called or not.
As we had never used TADO before this current exercise, we had used the constructs which we had previously used in respect of TParams and thought, perhaps naively, that TParameters would be the same. By this time we had carried out some 100+ tests (including trying to copy parameters and assign values) and were no nearer the a working piece of code as we had with the other standard components. So we wondered what we were doing wrong: we were trying to apply a technique which for years worked elsewhere, but not here.
We are therefore interested in
a) why TADOQuery does not have a Collection of Parameters
b) why would the Delphi example code work as at the beginning of this piece, but apparently not with as few as 40 parameters (we can sometimes have 80, 90+).