Executing a Stored Procedure form ActiveX

• Chong Yuan

  Valued Member

  Points: 68

  More actions

  January 7, 2008 at 7:33 am

  #95698

  Hi,

  I am quitte a junior when it comes to programming with the SQL database. But with help of forums like SqlServerCentrale i am getting things done. But in this case i am kinda stuck.

  I am trying to create a DTS with an ActiveX Script to call and Execute a Stored Procedure. But when i run it i get the following error:

  Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

  Please help!!!!

  This is my code:

  ----------------------------------------------------------------------------------------------

  Function Main()

  Dim objAdoRs, objAdoConn, objCMD, objResult

  Set objAdoConn = CreateObject("ADODB.Connection")

  Set objAdoRs = CreateObject("ADODB.Recordset")

  set objCMD = CreateObject("ADODB.Command")

  objAdoConn.Open "DRIVER=SQL Server;SERVER=ubn030s;UID=bvt_admin;PWD=@dm1n_85t"

  objAdoRs.Open "SELECT * FROM dbo.vwInternalOrder_ChaBwDslam", objAdoConn

  objCMD.ActiveConnection = objAdoConn

  If objAdoRs.EOF Then

  Main = DTSTaskExecResult_Failure

  Else

  Do While NOT objAdoRs.Eof

  objCMD.CommandText = "{? = call Calculate_BW_Dslam(?, ?, ?, ?, ?, ?)}"

  objCMD.CommandType = 4 'adCmdStoredProc

  objCMD.Parameters.Append objCMD.CreateParameter("New_BW", adSingle, adParamReturnValue)

  objCMD.Parameters.Append objCMD.CreateParameter("BW_Down",adInteger,adParamInput,objAdoRs("BW_Down"))

  objCMD.Parameters.Append objCMD.CreateParameter("AccessArea_ID",adInteger,adParamInput,objAdoRs("AA_ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("Order_ID",adInteger,adParamInput,objAdoRs("ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("QoS_ID",adInteger,adParamInput,objAdoRs("QoS_ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("Current_BW",adInteger,adParamInput,objAdoRs("Current_BW"))

  objCMD.Parameters.Append objCMD.CreateParameter("Num_Dslam_AA",adInteger,adParamInput,objAdoRs("Number_DSLAMs"))

  objCMD.Execute

  msgbox objCMD.Parameters("New_BW")

  set objCMD = nothing

  objAdoRs.MoveNext

  Loop

  Main = DTSTaskExecResult_Success

  End If

  End Function

  ----------------------------------------------------------------------------------------------

  And this is the definition of the stored procedure:

  ----------------------------------------------------------------------------------------------

  CREATE PROCEDURE dbo.Calculate_BW_Dslam

  @IntBW_Down Integer,

  @IntAccessArea_ID Integer,

  @IntOrder_ID Integer,

  @IntQoS_ID Integer,

  @IntCurrent_BW Integer,

  @IntNum_Dslam_AA Integer,

  @IntNew_BW FLOAT OUTPUT

  ----------------------------------------------------------------------------------------------

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  January 7, 2008 at 8:22 am

  #765361

  Looks like you have the names wrong in the ActiveX script. While you're adding them in the order, I thought there was a name mapping.

• Chong Yuan

  Valued Member

  Points: 68

  More actions

  January 7, 2008 at 11:18 pm

  #765582

  Hi,

  Thank you for replying on my post.

  Like I said I am a junior in programming DTS and Stored Procedures.

  So can you be a bit more specific about what i am doing wrong and how to solve it.

  Thanks.

  Chong

• Ian Crandell

  SSCommitted

  Points: 1603

  More actions

  January 15, 2008 at 3:24 pm

  #768368

  Chong,

  I am not sure if the names need to be the same or not. In my application, I changed the name of a parameter and it executed just fine. However, the parameters need to be added in the same order that they are listed in the stored procedure. I notice that you are adding the last variable ("New_BW") first.

  objCMD.Parameters.Append objCMD.CreateParameter("New_BW", adSingle, adParamReturnValue)

  objCMD.Parameters.Append objCMD.CreateParameter("BW_Down",adInteger,adParamInput,objAdoRs("BW_Down"))

  objCMD.Parameters.Append objCMD.CreateParameter("AccessArea_ID",adInteger,adParamInput,objAdoRs("AA_ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("Order_ID",adInteger,adParamInput,objAdoRs("ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("QoS_ID",adInteger,adParamInput,objAdoRs("QoS_ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("Current_BW",adInteger,adParamInput,objAdoRs("Current_BW"))

  objCMD.Parameters.Append objCMD.CreateParameter("Num_Dslam_AA",adInteger,adParamInput,objAdoRs("Number_DSLAMs"))

  ----------------------------------------------------------------------------------------------

  CREATE PROCEDURE dbo.Calculate_BW_Dslam

  @IntBW_Down Integer,

  @IntAccessArea_ID Integer,

  @IntOrder_ID Integer,

  @IntQoS_ID Integer,

  @IntCurrent_BW Integer,

  @IntNum_Dslam_AA Integer,

  @IntNew_BW FLOAT OUTPUT

  ----------------------------------------------------------------------------------------------

  I hope this helps.

  Ian.

  "If you are going through hell, keep going."
  -- Winston Churchill

• leokarp

  SSC Enthusiast

  Points: 149

  More actions

  January 17, 2008 at 8:59 am

  #769259

  Chong Yuan (1/7/2008)

  ---

  objCMD.CommandText = "{? = call Calculate_BW_Dslam(?, ?, ?, ?, ?, ?)}"

  objCMD.CommandType = 4 'adCmdStoredProc

  objCMD.Parameters.Append objCMD.CreateParameter("New_BW", adSingle, adParamReturnValue)

  objCMD.Parameters.Append objCMD.CreateParameter("BW_Down",adInteger,adParamInput,objAdoRs("BW_Down"))

  objCMD.Parameters.Append objCMD.CreateParameter("AccessArea_ID",adInteger,adParamInput,objAdoRs("AA_ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("Order_ID",adInteger,adParamInput,objAdoRs("ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("QoS_ID",adInteger,adParamInput,objAdoRs("QoS_ID"))

  objCMD.Parameters.Append objCMD.CreateParameter("Current_BW",adInteger,adParamInput,objAdoRs("Current_BW"))

  objCMD.Parameters.Append objCMD.CreateParameter("Num_Dslam_AA",adInteger,adParamInput,objAdoRs("Number_DSLAMs"))

  objCMD.Execute

  ----------------------------------------------------------------------------------------------

  CREATE PROCEDURE dbo.Calculate_BW_Dslam

  @IntBW_Down Integer,

  @IntAccessArea_ID Integer,

  @IntOrder_ID Integer,

  @IntQoS_ID Integer,

  @IntCurrent_BW Integer,

  @IntNum_Dslam_AA Integer,

  @IntNew_BW FLOAT OUTPUT

  ----------------------------------------------------------------------------------------------

  Chong,

  There are problems in your code.

  1. If CommandType is set to stored Procedure then CommandText should be the name of the Stored Procedure being called without any additions.

  2. In classic ADO Command's Parameters should be in the same order and the same quantity as in Stored Procedure's definition (with the one exception for optional parameters if they are located at the end of the parameter's list). In your procedure definition you have 7 parameters (output parameter is included in that count) but in the command definition you have only 6 Stored Procedure parameters defined (Return Value parameter is not corresponding to any of procedure's parameters and represents Return Value of the Stored Procedure supplied by explicit or implicit Return statement there). You need to add one more parameter with parameter type adParamOutput.

  Hope this will help.

• Ian Crandell

  SSCommitted

  Points: 1603

  More actions

  January 17, 2008 at 10:01 am

  #769304

  leokarp (1/17/2008)

  ---

  Chong,

  There are problems in your code.

  1. If CommandType is set to stored Procedure then CommandText should be the name of the Stored Procedure being called without any additions.

  2. In classic ADO Command's Parameters should be in the same order and the same quantity as in Stored Procedure's definition (with the one exception for optional parameters if they are located at the end of the parameter's list). In your procedure definition you have 7 parameters (output parameter is included in that count) but in the command definition you have only 6 Stored Procedure parameters defined (Return Value parameter is not corresponding to any of procedure's parameters and represents Return Value of the Stored Procedure supplied by explicit or implicit Return statement there). You need to add one more parameter with parameter type adParamOutput.

  Hope this will help.

  Leokarp,

  I didn't even catch the discrepancy with CommandText. Thanks for pointing that out. Also, I currently don't have any procedures with an output parameter and as such wasn't completely sure of the syntax, so I appreciate your input on that.

  Ian.

  "If you are going through hell, keep going."
  -- Winston Churchill

• Chong Yuan

  Valued Member

  Points: 68

  More actions

  January 17, 2008 at 11:17 pm

  #769557

  Hi Guys,

  Thanks for your input.

  I edited my code based on your recommendations ans now it works fine.

  Thanks alot.

  Chong