Forum Discussion

tpoirier's avatar
tpoirier
Contributor
12 years ago
Solved

Calling a stored procedure

I am trying to call a stored procedure passing in several aruments and not having any success, with a trace I was able to find out that the parameter names were not being included when when the data i...
  • agabriel's avatar
    agabriel
    12 years ago
    For a dataset this might work:



    Instead of:




    var test = SProc.Parameters.ParamByName("OnHand").value;

    Log.Message(test);



    Try this:




    SProc.Open();


      SProc.First();


      while (! SProc.EOF)


      {


        Log.Message(SProc.FieldByName("OnHand").Value);


        SProc.Next();


      }


      SProc.Close();





tpoirier's avatar
tpoirier
Contributor
12 years ago

Here is what I am working with, by creating all of the fields and passing in values I can get it to complete, but now I am working on trying to get the returned data back out. Additionally is there any way to pass in a value of null? In the Store List param the proc is set up to accept null and use the org id then but when I try to pass a null it does nothing. 



Thanks,



var SProc = ADO.CreateADOStoredProc();


  SProc.ConnectionString = "Data Source=DEV02; Provider=SQLOLEDB; Trusted_Connection=Yes; Integrated Security=SSPI; database=Rics";


  SProc.ProcedureName = 'dbo.Inventory_API_OnHandSummary';


  SProc.CommandTimeout = 3;


 


 


  


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(0).name = "OrganizationId";


  SProc.Parameters.Items(0).value = 'DB9AEE36-D771-405C-8D6E-9D9800EB73D0';


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(1).name = "UserId";


  SProc.Parameters.Items(1).value = '71BA3107-A5B7-42F7-89CF-A044014A0CE4';


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(2).name = "StoreList";


  SProc.Parameters.Items(2).value = '1,5';


 


 


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(3).name  = "ItemList";


  SProc.Parameters.Items(3).value = "sku=CM574NE"; //need to form?


  


//  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(4).name = "IncludeNeagativeOnHand";


  SProc.Parameters.Items(4).value = 0;


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(5).name = "Debug";


  SProc.Parameters.Items(5).value = 0;


 


  


  SProc.Parameters.CreateParameter("OnHand", adInteger, adParamReturnValue, 6, null);


  


  


  


  var test = SProc.Parameters.ParamByName("OnHand").value;


  Log.Message(test);


  SProc.ExecProc();