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?

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

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"
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Its the value of parameter you want to pass.
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)
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.