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
   
venmarcesAsked:
Who is Participating?
 
vs1784Commented:
CreateParameter

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
http://www.w3schools.com/ado/met_comm_createparameter.asp

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.

http://www.developersdex.com/gurus/code/479.asp

Hope it helps

Thanks
0
 
vs1784Commented:
Here is how i run a stored procedure in VB

Hope it helps.

Thanks
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
cmdSP.Execute
Set cmdSP = Nothing

Open in new window

0
 
venmarcesAuthor Commented:
there is an error in the first line

what we have to add in "aaa"
0
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.

 
vs1784Commented:
Its the value of parameter you want to pass.
0
 
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"
MaConnectionInventaire.Open

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
            cmdSp.Execute
            Set cmdSp = Nothing
     End If


0
 
vs1784Commented:
Is parameter you are using VarChar?

Is Size = 10?

What error you are getting?
0
 
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

0
 
vs1784Commented:
OK Replace

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

With

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


Also when you append parameters you have to specify correct type and value too.
0
 
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)
0
 
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  
0
 
vs1784Commented:
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.
0
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.