return value from sql server insert proc

Discussion in 'ASP .Net' started by Eric Effer, Jan 13, 2007.

  1. Eric Effer

    Eric Effer Guest

    Hi

    I am a newbie with vb.net. I am working with vb.net 2.0 and sql server 2005.
    I am trying to get the return value from my insert stored proc.Does anyone
    know how to do this?

    thanks

    E
     
    Eric Effer, Jan 13, 2007
    #1
    1. Advertising

  2. Eric Effer

    James Irvine Guest

    "Eric Effer" <> wrote in message
    news:%23Q4rf$...
    > Hi
    >
    > I am a newbie with vb.net. I am working with vb.net 2.0 and sql server
    > 2005.
    > I am trying to get the return value from my insert stored proc.Does anyone
    > know how to do this?
    >
    > thanks
    >
    > E


    Just include an integer to receive the results of the call to the stored
    proc. In C# it's like this:
    int rowsInserted = cmdUpdateReminders.ExecuteNonQuery();

    vb must be near identical syntax


    the full C# listing:

    public string InsertRemindersRecord(string p_eMailAddress, DateTime
    p_requestedSendDateTime, string p_timeZone, string p_messageHeader, string
    p_messageBody)
    {
    SqlConnection cq = new SqlConnection("xxx");
    SqlCommand cmdUpdateReminders = new
    SqlCommand("spInsertRemindersRecord", cq);
    cmdUpdateReminders.CommandType = CommandType.StoredProcedure;
    cq.Open();

    cmdUpdateReminders.Parameters.Add("@eMailAddress",
    SqlDbType.NVarChar, 99);
    cmdUpdateReminders.Parameters["@eMailAddress"].Value =
    p_eMailAddress;

    cmdUpdateReminders.Parameters.Add("@requestedSendDateTime",
    SqlDbType.DateTime);
    cmdUpdateReminders.Parameters["@requestedSendDateTime"].Value =
    p_requestedSendDateTime;

    cmdUpdateReminders.Parameters.Add("@timeZone", SqlDbType.NVarChar,
    50);
    cmdUpdateReminders.Parameters["@timeZone"].Value = p_timeZone;

    cmdUpdateReminders.Parameters.Add("@messageHeader",
    SqlDbType.NText);
    cmdUpdateReminders.Parameters["@messageHeader"].Value =
    p_messageHeader;

    cmdUpdateReminders.Parameters.Add("@messageBody", SqlDbType.NText);
    cmdUpdateReminders.Parameters["@messageBody"].Value = p_messageBody;

    // execute the stored proc:
    try
    {
    int rowsInserted = cmdUpdateReminders.ExecuteNonQuery();
    _msgBack = rowsInserted + " record inserted";
    cq.Close();
    return _msgBack;
    }
    catch (Exception ex)
    {
    string _full_msgBack = ex.Message;
    if (_full_msgBack.Substring(0, 24) == "Violation of PRIMARY
    KEY")
    _msgBack = "This record already exists";
    else
    _msgBack = _full_msgBack;

    return _msgBack;
    }
    }
     
    James Irvine, Jan 14, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi
    Replies:
    3
    Views:
    1,943
    David Lozzi
    Jun 1, 2005
  2. NevilleDNZ
    Replies:
    9
    Views:
    449
    NevilleDNZ
    Aug 16, 2006
  3. Scott
    Replies:
    11
    Views:
    295
    Scott
    Apr 12, 2004
  4. Jean-Hugues ROBERT

    Why no Proc##[]=() ? Why no Proc##replace() ?

    Jean-Hugues ROBERT, May 1, 2004, in forum: Ruby
    Replies:
    14
    Views:
    293
    Jean-Hugues ROBERT
    May 5, 2004
  5. Minkoo Seo

    Proc vs lambda vs proc

    Minkoo Seo, Feb 4, 2007, in forum: Ruby
    Replies:
    19
    Views:
    247
    Brian Candler
    Feb 6, 2007
Loading...

Share This Page