Solved

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

Posted on 2008-10-20
7
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge join vs exist 3 37
Data conversion error in SSIS 3 54
RestClient.cs fails at (HttpWebResponse)request.GetResponse()) 4 48
What are MicroServices? 4 66
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.​
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

737 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