having trouble retrieving value from stored procedure when using a table adapter
Posted on 2008-10-20
I'm trying to retrieve the output parameter of a stored procedure. I'm using a table adapter to access the sp. The sp code looks like this:
ALTER PROCEDURE dbo.spSelectFAC_WorkOrderLastID(@Yr varchar(15),
@WorkOrderID varchar(15) OUTPUT)
AS SELECT TOP 1 WorkOrderID
WHERE (WorkOrderID LIKE @Yr)
ORDER BY WorkOrderID DESC;
I have created a class with a function to proccess the input (DateOpened) and execute the sp but I can't get the return value I'm expecting (WorkOrderID). The entire process is supposed to return the last WorkOrderID entered so I can add 1 to it and use it for the next record. The sp works fine and while testing (before I added the return param) I could retrieve the last WorkOrderID if I entered "08%". WorkOrderID is a varchar pk and is in the format "08-1234". Here is the class with the function:
Public Function NewWorkOrderID(ByVal DateOpened As Date) As String
Dim workOrderID As String
Dim yr As String = DatePart(DateInterval.Year, DateOpened)
yr = Right(yr, 2)
yr = yr + "%"
Dim lastWorkOrderAdapter As New WorkOrderDALTableAdapters.FAC_WorkOrderLastIDTableAdapter
Dim lastWorkOrder As String = lastWorkOrderAdapter.GetLastWorkOrderID(yr, workOrderID).ToString '****I think this is where I'm screwing up*****
Can anyone see what I need to do to get the returned value of the sp (WorkOrderID) so I can then manipulate it , which might be another post. How do you add 1 to a varchar? I'll cross that bridge later.
Thanks for any help.