?
Solved

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

Posted on 2008-10-20
7
Medium Priority
?
368 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.
Suggested Courses

770 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