ADODB and Stored Procedure

[hamed.rahimian]

i need help about COMMAND and PARAMETERS Object in ADODB in Visual Basic 6.0>>>>

i have some code that uses command and parameters object. ADODB connection is connected to a access.mdb file.

all parameters are constructed in a stored procedure in sas.mdb before.

I've declared more than one parameters in my code.I've declared my fisrt parameter and append it to command object collection. for next parameter, i have constructed new parameters with the same parameter's name in next line in code, such this:

"set objprm=new adodb.parameters" (objprm is my parameters' object name)

for all parameters i've done likely and finally will execute command object.

when i track my code step by step with f8, it performs exactly and gives solution ,,but when i run it with f5, it gives wrong solution that differs from first. also if i run it with f5 several times,
it gives different solutions every time.....
i don't know what i must to do ..i'm sure that there is a problem in PARAMETERS DECLERATION but i don't know exactly where.

here's a piece of my code..

mobjCnn.Open JETOLEDB & App.Path & "\SAS.mdb;"
Set mobjPrm = New ADODB.Parameter
Set mobjCmd = New ADODB.Command
With mobjCmd
.ActiveConnection = mobjCnn
.CommandType = adCmdStoredProc
.CommandText = "cmdone"
End With

With mobjPrm
.Name = "prmone"
.Type = adInteger
.Direction = adParamInput
.Value = 2
End With

mobjCmd.Parameters.Append mobjPrm
Set mobjPrm = New ADODB.Parameter
With mobjPrm
.Name = "prmtwo"
.Type = adInteger
.Direction = adParamInput
.Value = 2
End With

mobjCmd.Parameters.Append mobjPrm
Set mobjPrm = New ADODB.Parameter

With mobjPrm
.Name = "prmthree"
.Type = adBoolean
.Direction = adParamInput
.Value = False
End With

mobjCmd.Parameters.Append mobjPrm
Set mobjRst = mobjCmd.Execute

[QVeen72]

Hi,

Try to Create Parameters like this :

Set mobjPrm = mobjCmd.CreateParameter("@prmOne", adInteger, adParamInput, 2)
mobjCmd.Parameters.Append mobjPar

Regards
Veena

[hamed.rahimian]

Dear Veena
Thank you a lot for your guide.
I try it but it doesn't work correctly.
this syntax didn't set the "value" Properties. value is gotten from a variable.although when i set a number for value, didn't work too.

as you know i have more than one parameters in a command.the errors says that just the final parameter that i have declared, is appended to parameters collection and the other parameters doesn't.

regards,

[QVeen72]

Hi,

Can you post your code after making changes...?

Regards
Veena

[hamed.rahimian]

Dear Veena
Here's my code according to your suggestion:

mobjCnn.Open JETOLEDB & App.Path & "\SAS.mdb;"
Set mobjPrm = New ADODB.Parameter
Set mobjCmd = New ADODB.Command
With mobjCmd
.ActiveConnection = mobjCnn
.CommandType = adCmdStoredProc
.CommandText = "Process"
End With

Set mobjPrm = mobjCmd.CreateParameter("ID", adInteger, adParamInput, Chain_maxTempShare(1))
mobjCmd.Parameters.Append mobjPrm
Set mobjPrm = New ADODB.Parameter

Set mobjPrm = mobjCmd.CreateParameter("p_factor", adInteger, adParamInput, Chains(Chain_maxTempShare(1)).pFactor)
mobjCmd.Parameters.Append mobjPrm
Set mobjPrm = New ADODB.Parameter

Set mobjPrm = mobjCmd.CreateParameter("Processed", adBoolean, adParamInput, False)
mobjCmd.Parameters.Append mobjPrm
Set mobjRst = mobjCmd.Execute

Regards,

[debasisdas]

i think you are missing something in this line

Set mobjPrm = mobjCmd.CreateParameter("ID", adInteger, adParamInput, Chain_maxTempShare(1))

are you getting any error message ?

[QVeen72]

Hi,

This Should work:

mobjCnn.Open JETOLEDB & App.Path & "\SAS.mdb;"
Set mobjPrm = New ADODB.Parameter
Set mobjCmd = New ADODB.Command
With mobjCmd
.ActiveConnection = mobjCnn
.CommandType = adCmdStoredProc
.CommandText = "Process"
End With
'
mobjCmd.Parameters.Append mobjCmd.CreateParameter("ID", adInteger, adParamInput, Chain_maxTempShare(1))
mobjCmd.Parameters.Append mobjCmd.CreateParameter("p_factor", adInteger, adParamInput, Chains(Chain_maxTempShare(1)).pFactor)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("Processed", adBoolean, adParamInput, False)
'
Set mobjRst = mobjCmd.Execute

Also check your Procedures return proper results...

Regards
Veena

[hamed.rahimian]

Dear Veena

I do your new suggestion ,but it didn't work too. like the early syntax, i get the same error like this:

"Parameter ID has no default value"

is it possible that my stored procedure was incorrect??
so,i send stored procedure ,if you know SQL, please help me..it works in access..

'PARAMETERS ChainID Short, p_factor Short, Processed Bit;
SELECT Jobs.JobID, Jobs.ChainID, Jobs.RunTime, Jobs.Weight, Jobs.StartTime, Jobs.CompletionTime, Jobs.Processed
FROM Jobs
WHERE (((Jobs.JobID)<=[p_factor]) AND ((Jobs.ChainID)=[ChainID]) AND ((Jobs.Processed)=[Processed]));
'

Regards,

[QVeen72]

Hi,

Not Sure but, In SQL You have to preceed the parameter with @,
your Stored procedure, need to look like this :

CREATE PROCEDURE Myproc @ChainID int,  @p_factor int, @Processed  Char1(1) AS 

SELECT Jobs.JobID, Jobs.ChainID, Jobs.RunTime, Jobs.Weight, Jobs.StartTime, Jobs.CompletionTime, Jobs.Processed
FROM Jobs
WHERE Jobs.JobID)<=@p_factor  AND Jobs.ChainID=@ChainID AND Jobs.Processed=@Processed;

Regards
Veena