0

I use the following code to call my stored procedure using TADOStoredProc type

MySP.Connection := aConnection;
MySP.ProcedureName := 'dbo.UpdateErrors';
MySP.Parameters.ParamByName('@Error_Number').value := -1;
MySP.Parameters.ParamByName('@NewError_Name').value := 'errorM1';
MySP.Parameters.Refresh;

MySP.ExecProc;

The parameter @Error_Number is part of the stored procedure UpdateErrors using SQL Server Management Studio, I add snip image for confirmation

enter image description here

but I can't understand why I get an error

kobik
  • 21,001
  • 4
  • 61
  • 121
user1769184
  • 1,571
  • 1
  • 19
  • 44
  • 4
    Try to call `MySP.Parameters.Refresh` *before* you set the parameters. Or create theses parameters in your `TADOStoredProc` without calling `Refresh`. e.g. `MySP.Parameters.CreateParameter('@Error_Number'...)` (or create them in design mode) – kobik Aug 09 '15 at 11:39
  • Kobik, you should make this an answer. – David Dubois Aug 09 '15 at 12:56
  • 1
    I not sure about that `@` in their names. – TLama Aug 09 '15 at 15:35
  • Nice painting man! Red, and hand-crafted for optimal effect. I'm appreciating it. – Wouter van Nifterick Aug 09 '15 at 15:46
  • 2
    @TLama -- @ should be before parameter name. tested. really. – Zam Aug 09 '15 at 19:31
  • Can you added `MySP.Prepared := True` instead of `MySP.Parameters.Refresh;` ? – Zam Aug 09 '15 at 19:32
  • still Problem is unsolved ; no Change MySP.Prepared := True or MySP.Parameters.Refresh; directly placed after Name Setting LOC – user1769184 Aug 09 '15 at 20:21
  • no effect MySP.ProcedureName := '[dbo]..functionname' or MySP.ProcedureName := 'functionname' without [dbo]. – user1769184 Aug 09 '15 at 20:22
  • 1
    Can you use the Sql Profiler to see exactly the command you are sending? It might give you some leads. Also there is a name mismatch on the second parameter '@NewError_Name' in code while in db it is '@New_Name' – Liviu Costea Aug 09 '15 at 20:44
  • @Liviu : the param writing is correct, my post is in correct because I changed from used names in the real application to a public Version with modified names – user1769184 Aug 10 '15 at 07:47
  • 1
    @user1769184, `"I changed from used names in the real application to a public Version with modified names"` In that case please [edit](http://stackoverflow.com/posts/31903397/edit) your code. – kobik Aug 10 '15 at 08:59
  • 1
    @user176918 Have you tried the suggestions from kobik ? Could you please edit your post so it looks exactly like you code, I mean fix the parameter name and put "MySP.Parameters.Refresh" at the position where it is in your code. Like this it is hard for us to know what you tried or did not tried and thus makes it hard for us to help you – GuidoG Aug 10 '15 at 10:44
  • I really don't see why you can't resolve this by now... BTW, From my testing, letting ADO retrieve the parameters from the server via `Parameters.Refresh` can be bogus + the extra trip to the SQL server. I would construct the parameters programmatically like I explained in my first comment. see more information: [A better way of passing parameters to a TADOStoredProc](http://stackoverflow.com/questions/1159489/a-better-way-of-passing-parameters-to-a-tadostoredproc-delphi) – kobik Aug 10 '15 at 14:34

1 Answers1

2

Simply use a TADOCommand

  MyCommand.Connection := aConnection;
  MyCommand.CommandText := 'EXEC dbo.UpdateErrors :Er, :Na'; //you can call the params what you want
  MyCommand.Parameters[0].value := -1; //Or you can do ParamByNname and use Er and Na (or whatever you called your params) instead of indices
  MyCommand.Parameters[1].value := 'errorM1';
  MyCommand.Execute;

If you want to fix your code

Do 

 ErParam := MySP.Parameter.Add;
 ErParam.Name := '@Error_Number';
 ErParam.DataType := ftInteger; //put your correct type here
 ErParam.Direction := pdInput; //set your direction for the param

etc. Lots more work... do the first way with ADOCommands

kobik
  • 21,001
  • 4
  • 61
  • 121
Jasper Schellingerhout
  • 1,070
  • 1
  • 6
  • 24