Stored Procedure with VB6.0

I try to execute a Stored Procedure which exists in SQlServer database within a VB6.0 program. I have a connection of my database in a program. I checked for the call instruction of my Stored procedure in the web an i tried most of them but it didn't work .....Here is my last try :  
Dim RS As New ADODB.Recordset
Set RS = Cmd.Execute("dbo.EstimationFeuillesMetal", Param)
Param is an Integer that i give to my stored procedure
So how can i do to resolve this
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here is how i run a stored procedure in VB

Hope it helps.

Set cmdSP = Server.CreateObject("ADODB.Command")
cmdSP.ActiveConnection = conn1
cmdSP.CommandType = adCmdStoredProc
cmdSP.CommandText = "sp_Test"
With cmdSP.Parameters
    .Append cmdSP.CreateParameter("param1", adVarChar, adParamInput, 10, "aaa")
End With
Set cmdSP = Nothing

Open in new window

venmarcesAuthor Commented:
there is an error in the first line

what we have to add in "aaa"
Its the value of parameter you want to pass.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

venmarcesAuthor Commented:
My connection to the database was done by this code and it is right for all the program so i guess that i don't need to add it again
Public Sub ConnectionInventaire()

Set MaConnectionInventaire = New ADODB.Connection
MaConnectionInventaire.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test015 Source=SVR1"

so what i added in the code is :  

Set cmdSp = Server.CreateObject("ADODB.Command")
    cmdSp.CommandType = adCmdStoredProc
    cmdSp.CommandText = "dbo.EstimationAll"
     If Valid Then
            'Executer la procedure stockée
            With cmdSp.Parameters
                .Append cmdSp.CreateParameter("param", adVarChar, adParamInput, 10, "param")
            End With
            Set cmdSp = Nothing
     End If

Is parameter you are using VarChar?

Is Size = 10?

What error you are getting?
venmarcesAuthor Commented:
the error is at the first line : the server setup  and each declare lin after that ....
The parameter that i'm using is an integer size 5

OK Replace

Set cmdSp = Server.CreateObject("ADODB.Command")


Dim cmdSp As New Command
Set cmdSp = New ADODB.Command

Also when you append parameters you have to specify correct type and value too.
venmarcesAuthor Commented:
Ok that good
so what i have to add in this syntax for the second parameter , i put adint , because my input is Integer

Set prm = cmd.CreateParameter("param1", adInt, adParamInput, 1, Param)

Argument 1 = Name of Parameter in SQL Stored Procedure
Argument 2 = Datatype Integfer should be adInteger
Argument 3 = Direction Input/Output
Argument 4 = Size
Argument 5 = Value to be passed

Check this link for more info

You have to create 2 parameters if your SP takes 2 params. And then append it to your SQL Command.

Here is one more example that might help you.

Hope it helps


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
venmarcesAuthor Commented:
It is really good, now the problem is my stored procedure may not be executed , However, when i execute it in sql server 2005 and enter the parameter it seems Ok and alter the other tables ....  
And when i execute it from my program with debug mode it seems Ok and error but not doing what i want it to do, is their any manner to catch if my program enter really to the stored procedure , how can i check this , and i have no debug in SQL server , so it is difficult

Thanks again  
See what paraeters it is passing from your application. And try passing same parameters from SQL Query Analyzer to the procedure.

Both should give same output if passing same parameters.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.