SP and forms 1

[EMEP]

I have a Stored Proc which works when run directly, but when trying to run through form I get error message. Does anybody have a suggestion??

Thanks a lot

Eric

With cmd
.ActiveConnection = cnn
.CommandText = "dbo.SP_TravelRate"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@EMPLOYEE_ID", adInteger, adParamInput, Forms!frmwork_entry.SubFrmWork.Form!Employee_ID)
.Parameters.Append .CreateParameter("@PROJECT_ID", adInteger, adParamInput, Forms!frmwork_entry.SubFrmWork.Form!Proj_ID)
.Execute


error message
Procedure "SP_TravelRate" expects parameter '@PROJECT_ID', which was not supplied
End With

 

[Crowley16]

try using

.parameters("@PROJECT_ID") = forms!...

instead...

--------------------
Procrastinate Now!

 

[EMEP]

Thanks for your input crowley,

I have adjusted like this

.Parameters.Append .CreateParameter("@PROJECT_ID", adInteger, adParamInput)
.Parameters("@PROJECT_ID") = Forms!frmwork_entry.SubFrmWork.Form!Proj_ID

when in debugging mode it shows a value, but a still get same error message. What to do?

 

[Crowley16]

no append needed at all

--------------------
Procrastinate Now!

 

[EMEP]

this is SP_TravelRate

SELECT TravelRate_Km
FROM dbo.TblProject_Crewing
WHERE (Employee_ID = @EMPLOYEE_ID) AND (Proj_ID = @PROJECT_ID)

When adjusting VBA to just

.Parameters("@PROJECT_ID") = Forms!frmwork_entry.SubFrmWork.Form!Proj_ID

I get error message
Item cannot be found in the collection corresponding to the requested name or ordinal.

Any other suggestions?

Thanks for your troubles

Eric

 

[Crowley16]

the .parameters collection is part of the command object, you still need to initiate the command/commection...

dim command...
dim connection...

with command
set activeConnection...
set commandText...
set commandType...

.parameters("@para1") = someValue
.parameters("@para2") = someValue
...
...

.execute

debug.print .parameters("@outputParameter1")
debug.print .parameters(0) 'return value of the sproc
end with


--------------------
Procrastinate Now!

 

[danvlas]

Place another comma after adParameInput.

Now you have the parameter value in the 'size' position...


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[EMEP]

I am still not getting output. Also I would like to use the stored procedure as rowsource in a combobox. How do i go about that?

thanks for your input.

Eric

 

[Crowley16]

you have to first load the results of the stored procedure into a recordset, then set the combobox's recordset property to the loaded recordset.

the recordset property is a hidden property which can only be manipulated through code, so you won't see it on the properties page...

--------------------
Procrastinate Now!

 

[rluciano]

If you run the wizard when you add the combo box to the form, the wizard will walk you through using the sproc as the recordset or control source for the combo box

 

[ESquared]

Some of the replies so far have been misleading, and the actual answer appears to have been completely missed.

try using

.parameters("@PROJECT_ID") = forms!...

instead...

This works, but it's no different than specifying the parameter at creation time. Not going to help.

no append needed at all

No append is needed only if you do Parameters.Refresh. But this is poor practice as it necessitates a round trip to the server simply for convenience. If you want Parameters in your collection, you have to add them somehow. Best practice is Append.

Item cannot be found in the collection corresponding to the requested name or ordinal.

As I said, you have to create the parameter before you can access its properties. Sorry about the confusion.

Place another comma after adParameInput.
Now you have the parameter value in the 'size' position...

Ding ding ding ding ding. There's the answer. Let's fix your original code. First, see the ADO CreateParameter Method.

It says name, type, drection, size, value.

Size is missing, just like danvlas said, who deserves a star by the way, please click on "Thank danvlas for this valuable post!"

With cmd
    .ActiveConnection = cnn
    .CommandText = "dbo.SP_TravelRate"
    .CommandType = adCmdStoredProc
    [blue].Prepared = False 'unless you keep the command object lying around to reuse, save some effort by the server[/blue]
    .Parameters.Append .CreateParameter("@EMPLOYEE_ID", adInteger, [COLOR=white red],[/color] adParamInput, Forms!frmwork_entry.SubFrmWork.Form!Employee_ID)
    .Parameters.Append .CreateParameter("@PROJECT_ID", adInteger, adParamInput, [COLOR=white red],[/color] Forms!frmwork_entry.SubFrmWork.Form!Proj_ID)
    .Execute
End With

you have to first load the results of the stored procedure into a recordset, then set the combobox's recordset property to the loaded recordset.

That works. But why not set the rowsource property instead? ADPs work that way and it's a far sight easier:

Me.Controls!cboTravelRate.RowSource = "EXEC dbo.SP_TravelRate " & Forms!frmwork_entry.SubFrmWork.Form!Employee_ID & ", " & Forms!frmwork_entry.SubFrmWork.Form!Proj_ID

You do have to pay attention to SQL injection. But these look like values not exposed to the user. In any case, you can use some scrubbing to make sure the values contain only numbers.

If it was a form it would be even easier. In the form properties at design time you would set the RecordSource property to "EXEC dbo.SP_TravelRate ?, ?" where the question marks are parameter placeholders, and the InputParameters property would be "[Forms]![frmwork_entry].[SubFrmWork].[Form]![Employee_ID], [Forms]![frmwork_entry].[SubFrmWork].[Form]![Proj_ID]".

If you really want to use a parameterized command object instead of text, create a generic function or procedure that does the work for you, code it right once and reuse it over and over again. You should NOT be declaring connections and commands and things over and over again throughout your code. What a mess.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]