Solved

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

Posted on 2008-10-20
7
360 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
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…
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now