Solved

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

Posted on 2008-10-20
7
362 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

786 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