Solved

having trouble retrieving value from stored procedure when using a table adapter

Posted on 2008-10-20
7
363 Views
Last Modified: 2012-06-27
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
FROM          dbo.FAC_WorkOrders
WHERE      (WorkOrderID LIKE @Yr)
ORDER BY WorkOrderID DESC;
RETURN @WorkorderID

 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*****
        Return workOrderID
    End Function
End Class


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.
0
Comment
Question by:AkAlan
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22760258
I dont think table Adapters were intended to be used to retrive output paramaters.
check this out though: http://www.experts-exchange.com/Web_Development/Miscellaneous/Q_22145948.html

and this
http://www.eggheadcafe.com/community/aspnet/10/52177/stored-procedure-output-p.aspx
0
 
LVL 6

Author Comment

by:AkAlan
ID: 22760485
Thanks. Would you suggest another method for fetching the return value? It looks like maybe creating a sql datasource might be an alternative but is that the best way. This doesn't feel like it should be so difficult but it has proven to take up a lot of my time. Thanks again for your help.
0
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22760814
Check out this page, There is a section labelled "Fetching Output Parameters"
http://www.codeguru.com/vb/gen/vb_database/sqlserver/article.php/c8671__3/
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 6

Author Comment

by:AkAlan
ID: 22761637
I looked at the link but could not get the example to work. I'm willing to try another approach to this. I just need to fetch the output parameter from a stored procedure into a Class that I created. I don't care what method I use. Thanks for your help so far.
0
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22761688
Can you post up what your code currently look like and I'll try yo work it from there
0
 
LVL 6

Author Comment

by:AkAlan
ID: 22761751
the sp and Class code are in the first post. I believe the sp is correct since I can get it to return the expected result. I'll repost the Class since this is where I'm having trouble

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 + "%"
    ****here is where I ned to execute the sp and fetch the output parameter*****

              Return workOrderID
    End Function
End Class

Thanks for your interest in helping me, I'm an asp newbie.
0
 
LVL 6

Accepted Solution

by:
AkAlan earned 0 total points
ID: 22762363
I got it to work. I had to use a sql command and modify my sp. I'll post both for future questions.

SP:
ALTER PROCEDURE dbo.spSelectFAC_WorkOrderLastID(@Yr varchar(15),
                                                                        @WorkOrderID varchar(15) OUTPUT)
AS SET @WorkOrderID = (SELECT      TOP 1 WorkOrderID
FROM          dbo.FAC_WorkOrders
WHERE      (WorkOrderID LIKE @Yr)
ORDER BY WorkOrderID DESC)

Code in Class:

Public Class TestClass
    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 conn As SqlConnection = New SqlConnection("Data Source=ARCTEC-DEV-SVR;Initial Catalog=AAIOMS;Integrated Security=True")
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand("spSelectFAC_WorkOrderLastID", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@yr", yr)
        cmd.Parameters.Add("@WorkOrderID", SqlDbType.VarChar).Direction = ParameterDirection.Output
        cmd.Parameters("@WorkOrderID").Size = 15
        Dim adapter As SqlDataAdapter = New SqlDataAdapter(cmd)
        cmd.ExecuteReader().ToString()
        workOrderID = cmd.Parameters("@WorkOrderID").Value
        Return workOrderID
    End Function
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New Relic: Our company recently started researching several products to figure out what were the best ways for us to increase our web page speed and to quickly identify performance problems that we may be having. One of the products we evaluated wa…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question