Link to home
Start Free TrialLog in
Avatar of venmarces
venmarcesFlag for Canada

asked on

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
   
Avatar of vs1784
vs1784

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

Avatar of venmarces

ASKER

there is an error in the first line

what we have to add in "aaa"
Its the value of parameter you want to pass.
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


Is parameter you are using VarChar?

Is Size = 10?

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

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.
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)
ASKER CERTIFIED SOLUTION
Avatar of vs1784
vs1784

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.