none
output parameters with a typed dataset

    Question

  • I need a little help.

    I have a typed dataset that references a stored proc.

    My stored proc returns a value via an output parameter.

    I am not sure of the correct way to get to the output parameter from my typed dataset.

     

    The output parameter is a guid.

    I know that I need to set the value up as a reference value, however, I'm not sure about the actual structure.

    The MSDN info is very light on this subject.

    Can someone offer some assistance please?

     

     

     

     

    Thursday, April 19, 2007 5:11 AM

Answers

  • Ok. I'm sure that I'm not the only person who has had a problem figuring out this issue so I'm going to post my code and error information so it can help someone else.

    First, I'm sure that there are other ways to do this. The MSDN documentation outlines another way, however, I received errors with that code. I realize why I got those errors now, but I came up with my solution already and it works so I'm not going to go back and break it. :-)

     

    Just so you know, I have a stored procedure that I call to do an insert which then outputs a guid.

    I created a typed dataset using Visual Studio and here is the code to declare my Guid, table adapter and call the Insert stored proc -

     

     

    ******************************************************************************************************************************

    Guid? EmailCampaignId = new Guid();

    string sEmailCampaignId = "";

    EmailManager_DatasetTableAdapters.EmailCampaignsTableAdapter myEmailCampaign = new EmailCampaignsTableAdapter();

    myEmailCampaign.InsertEmailCampaign_GetCampaignID(EmailCode, EmailCampaignName, out EmailCampaignId);

     

    sEmailCampaignId = EmailCampaignId.ToString();

    ********************************************************************************************************************************************************

     

    After I do the insert and the procedure returns my output value through my TableAdapter, I assign the value of the variable to a string that I manipulate in some other ways later on in the code.

     

    Soooo that's it. Now you can grab those output parameters from your typed datasets all day long! I hope this helps someone else!

    VMazur I appreciate your help. Maybe I wasn't clear enough when I posted my question initially which is why you said it couldn't be done.

    I apologize if I was vague. Next time I need help I'll be clearer. Posting my code probably wouldn't hurt either.

     

    Thanks,

     

    Kila

    Thursday, April 19, 2007 2:35 PM

All replies

  • You need to handle output parameters separately and you cannot bind them and get data from them through the DataSet. When you create collection of the parameters and execute stored procedure, then you need to read Value property of the specific parameter to get what was returned by stored procedure. Like

     

    Debug.Print MyCommand.Parameters("ParameterNameHereSmile.Value

     

    Following is the link that shows how to get output parameter from stored procedure

     

    http://support.microsoft.com/kb/308049/en-us

    Thursday, April 19, 2007 10:27 AM
    Moderator
  • Thank you so much for your response VMazur.

    However, I still need a little more help. I already know I can retrieve the value in the standard ways outlined in the article you posted.

    I specifically want to obtain it from the typed dataset.

    You can specify whether or not a parameter is an input or output parameter using the dataset so I know there has to be some way to retrieve that value.

    I'm just not sure of the correct syntax in my application for it.

     

    The MSDN identifies a way to get the ouput parameter data from the typed dataset in this article http://msdn2.microsoft.com/en-us/library/ms364060(VS.80).aspx.

    This is what I need - the actual syntax.

    The article says that its possible and outlines a method, however, I'm confused about how to exactly apply that method to my table adapter in my dataset.

     

    I'm going to grab some of my code and post it. I know my issue is with the syntax.

     

    Here is the part of that article that I need help with -

     

    ______________________________________________________________________________________________________________________________________

    TableAdapters are also smart about handling queries with output parameters. For example, if you have a stored procedure called Output that takes one output parameter, the associated TableAdapter method parameters will be passed by reference:

    C#

    public virtual int Output(ref System.Nullable<int> p1)
    When the method is called, p1 is passed to the stored procedure as a command parameter, 
    the stored procedure is executed, and the value of the modified command parameter is placed back into p1. 
    In code you can use this parameter in the same way you would use any other reference parameter.

    ______________________________________________________________________________________________________________________________________

     

    Thanks,

    Kila

    Thursday, April 19, 2007 12:21 PM
  • Ok. I'm sure that I'm not the only person who has had a problem figuring out this issue so I'm going to post my code and error information so it can help someone else.

    First, I'm sure that there are other ways to do this. The MSDN documentation outlines another way, however, I received errors with that code. I realize why I got those errors now, but I came up with my solution already and it works so I'm not going to go back and break it. :-)

     

    Just so you know, I have a stored procedure that I call to do an insert which then outputs a guid.

    I created a typed dataset using Visual Studio and here is the code to declare my Guid, table adapter and call the Insert stored proc -

     

     

    ******************************************************************************************************************************

    Guid? EmailCampaignId = new Guid();

    string sEmailCampaignId = "";

    EmailManager_DatasetTableAdapters.EmailCampaignsTableAdapter myEmailCampaign = new EmailCampaignsTableAdapter();

    myEmailCampaign.InsertEmailCampaign_GetCampaignID(EmailCode, EmailCampaignName, out EmailCampaignId);

     

    sEmailCampaignId = EmailCampaignId.ToString();

    ********************************************************************************************************************************************************

     

    After I do the insert and the procedure returns my output value through my TableAdapter, I assign the value of the variable to a string that I manipulate in some other ways later on in the code.

     

    Soooo that's it. Now you can grab those output parameters from your typed datasets all day long! I hope this helps someone else!

    VMazur I appreciate your help. Maybe I wasn't clear enough when I posted my question initially which is why you said it couldn't be done.

    I apologize if I was vague. Next time I need help I'll be clearer. Posting my code probably wouldn't hurt either.

     

    Thanks,

     

    Kila

    Thursday, April 19, 2007 2:35 PM
  • Oh. I was so happy that I forgot to post info on the errors I received.

     

    I received an error that said cannot convert System.Guid to out System.Guid.

    Changing the Guid declaration from

    THIS   -   Guid EmailCampaignId = new Guid();

     

    TO THIS  - Guid? EmailCampaignId = new Guid();

     

    corrected this error.

     

    I also received an error at another point in my trial and error process that gave me an error with the Guid identified like this in the error message - Guid?

     

    That question mark is C# syntax for a nullable data type.

    You need to identify the data type as nullable for the process to work. In my case, the GUID I am grabbing is the primary key for the table. It isn't nullable, but the procedure generated errors when I tried to pass the variable without identifying it as nullable.

     

    Once I added the nullable identifier to the variable declaration, the problem was solved.

     

    Hope this helps.

     

    Thanks,

     

    Kila

    Thursday, April 19, 2007 2:44 PM