Executing a Stored Procedure form ActiveX

Question

Post reply

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

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 728159

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Chong Yuan Valued Member Points: 68 More actions · Answer 2

Chong Yuan

Valued Member

Points: 68

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 Default port Points: 1493 More actions · Answer 3

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 · Answer 4

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 Default port Points: 1493 More actions · Answer 5

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 · Answer 6

Chong Yuan

Valued Member

Points: 68

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