none
Parameters.AddWithValue Output parameter in ADO.NET 2

    Question

  • With ASP.NET 2.0, SqlCommand.Parameters.Add is depreciated and will no
    longer work.  It is being replaced with Parameters.AddWithValue.  Can
    anyone post an example of how to construct an OUTPUT parameter in .NET
    2.0?
    Friday, August 05, 2005 7:14 PM

Answers

  • There's only one Add method that's obsoleted, the method that accepts a string for the parameter name and an object for the value.  As you noted, you should call AddWithValue instead for this scenario.

    All other Add methods are still available.  However, there was an issue in recent releases of Visual Studio that prevented the Add methods from showing up in Intellisense drop-downs.  You should be able to escape out of the Intellisense drop-downs and manually type "Add.(".

    To handle output parameters, you could use code like.

    //C#

    SqlParameter p = cmd.Parameters.Add("@MyParam", SqlDbType.Int);

    p.Direction = ParameterDirection.Output;

     

    'VB.NET

    Dim p As SqlParameter = cmd.Parameters.Add("@foo", SqlDbType.Int)

    p.Direction = ParameterDirection.Output

     

    I hope this information proves helpful.

    David Sceppa

    ADO.NET Program Manager

    Microsoft

    Saturday, August 06, 2005 7:51 PM
    Moderator

All replies

  • There's only one Add method that's obsoleted, the method that accepts a string for the parameter name and an object for the value.  As you noted, you should call AddWithValue instead for this scenario.

    All other Add methods are still available.  However, there was an issue in recent releases of Visual Studio that prevented the Add methods from showing up in Intellisense drop-downs.  You should be able to escape out of the Intellisense drop-downs and manually type "Add.(".

    To handle output parameters, you could use code like.

    //C#

    SqlParameter p = cmd.Parameters.Add("@MyParam", SqlDbType.Int);

    p.Direction = ParameterDirection.Output;

     

    'VB.NET

    Dim p As SqlParameter = cmd.Parameters.Add("@foo", SqlDbType.Int)

    p.Direction = ParameterDirection.Output

     

    I hope this information proves helpful.

    David Sceppa

    ADO.NET Program Manager

    Microsoft

    Saturday, August 06, 2005 7:51 PM
    Moderator
  • I am not an experienced programmer and have convert a VB.NET2003 program to see the ...deprecated...use... in the underlined area of code. Please show me how to convert this snippet. The .Add() is the problem areas.

    Thank You,

    Tony

     

    Public Shared Function AddVendor(ByVal Vendor As Vendor) As Integer

    Dim conPayables As SqlConnection = GetPayablesConnection()

    Dim cmdInsertVendors As New SqlCommand("usp_InsertVendors10", conPayables)

    cmdInsertVendors.CommandType = CommandType.StoredProcedure

    With cmdInsertVendors.Parameters

    .Add("@VendorName", Vendor.Name)

    If Vendor.Address1 = "" Then

    .Add("@VendorAddress1", DBNull.Value)

    Else

    .Add("@VendorAddress1", Vendor.Address1)

    End If

    If Vendor.Address2 = "" Then

    .Add("@VendorAddress2", DBNull.Value)

    Else

    .Add("@VendorAddress2", Vendor.Address2)

    End If

    .Add("@VendorCity", Vendor.City)

    .Add("@VendorState", Vendor.State)

    .Add("@VendorZipCode", Vendor.ZipCode)

    End With

    Thursday, February 23, 2006 4:56 PM
  • Hello there ,

    I'm not sure if you're familiar with asp.net 2.0 anyway see the sample code below  I hope it helps

     

    // create a new parameter

    param = comm.CreateParameter();

    param.ParameterName =

    param.Direction =

    param.DbType =

    comm.Parameters.Add(param);

     

    "@OutPutValue";ParameterDirection.Output;DbType.Int32;// execute the stored procedure and save the results in a DataTable

     

     

    DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);// get the value

     int MyOutPutValue= Int32.Parse(comm.Parameters["@OutPutValue"].Value.ToString());

     

    Thanks

    Stephanie Johnson


    Account Manager @ Office Relocation Removals



     



    Saturday, September 09, 2006 11:58 PM
  • Agree with David Sceppa

    SEGEAT! -- Simple, Easy and Great Technique! --

    Bastari
    Senior .Net Developer
    CyberNet, INC

    Wednesday, April 25, 2007 4:11 AM
  • Very helpful, David. I was having difficulty trying to add an output parameter until I read your reply. This code worked for me.

                            SqlCommand dbCommand = dbUtil.DBCommand(scConnect, "upSaveAddPersonStudy1");
                            dbCommand.Parameters.AddWithValue("@StudyId", "Add Member");
                            dbCommand.Parameters.AddWithValue("@pInstitutionId", "FHCRC");
                            dbCommand.Parameters.AddWithValue("@psStudyId", "SAP");
                            //David Sceppa's method of adding output parameters: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/15bb16a4-0cf1-4289-b677-3b9d98f09298
                            SqlParameter ret = dbCommand.Parameters.Add("@rtnId", SqlDbType.Int);
                            ret.Direction = ParameterDirection.Output;

    Wednesday, September 01, 2010 4:54 PM