Re: How to retrieve stored procedure error code in C#?
- From: "David Carr" <David_Carr@xxxxxxxxxxxxxxxx>
- Date: Tue, 16 Aug 2005 15:53:39 -0700
Mark and William - thx for the replies, as they pointed me in the right
direction.
To summarise for any other eyes, the approach taken has been to have
different return values as indicated in the SQL below. Then the C# code can
as follows. Note that the return value cannot be obtained until the
SqlDataReader.Close() has been called.
Cheers,
David
....
SqlCommand cmdDist = new SqlCommand("PersonGet", m_oConn);
cmdDist.CommandType = CommandType.StoredProcedure;
cmdDist.Parameters.Add("@IDDistributor", lIDDistributor);
cmdDist.Parameters.Add("@sUsername", sUsername);
// Set up the return value
SqlParameter prmReturn = new SqlParameter("@ReturnValue", SqlDbType.Int);
prmReturn.Direction = ParameterDirection.ReturnValue;
cmdDist.Parameters.Add(prmReturn);
myReader = cmdDist.ExecuteReader();
// If data has been returned.
if (myReader.HasRows)
{
// Obtain the IDPerson
myReader.Read();
if (!myReader.IsDBNull(0))
{
long lIDPerson = myReader.GetInt32(0);
// Close the reader
myReader.Close();
// Now that the reader has been closed, obtain the return code from
the 'PersonGet' stored procedure.
long lResult = cmdDist.Parameters["@ReturnValue"].Value.ToString())
== 0);
....
"Mark Ashton" <markash@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23r2TLofoFHA.2916@xxxxxxxxxxxxxxxxxxxxxxx
> Add another parameter with name @RETURNVALUE and parameter.Direction =
> ParameterDirection.ReturnValue. The name doesn't matter as much, but the
> parameter direction does. The datatype will always be an Int32.
>
> All Sql Server stored procedures have a return value parameter, though on
> the client they are frequently ignored. The return value is a true output
> only were other parameters against sql server are really either input or
> inputoutput.
>
> --
> This posting is provided "AS IS", with no warranties, and confers no
rights.
> Please do not send email directly to this alias. This alias is for
newsgroup
> purposes only.
>
> "David Carr" <David_Carr@xxxxxxxxxxxxxxxx> wrote in message
> news:%23Mr9CgdoFHA.2484@xxxxxxxxxxxxxxxxxxxxxxx
> > Thx for the reply Mark.
> >
> > To better clarify things for all, my SQL stored proc is given below and
> > returns a -2 if there is are duplicate records, otherwise 0. I now want
> > to
> > retrieve this value through my C# code. Any ideas on the most simple
and
> > elegant way to achieve this?
> >
> > Thanks,
> > David
> >
> > CREATE PROCEDURE dbo.PersonGet
> > @IDDistributor int,
> > @sUsername nvarchar(50),
> > AS
> >
> > DECLARE @nCount int;
> > SELECT @nCount = Count(*)
> > FROM Person
> > WHERE (IDDistributor = @IDDistributor) AND (Username = @sUsername)
> >
> > -- Should be unique, otherwise return -2 to indicate this.
> > IF (@nCount > 1)
> > BEGIN
> > -- Clear the returned set
> > SELECT NULL AS ID;
> > RETURN -2;
> > END
> >
> > -- Otherwise, go ahead and perform the query again to return the result
> > set
> > -- CODE OMITTED
> >
> > RETURN 0;
> > GO
> >
> >
> >
> > "Mark Ashton" <markash@xxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:OTCUYw5nFHA.860@xxxxxxxxxxxxxxxxxxxxxxx
> >> By generating error codes, do you mean with the RAISEERROR or PRINT
> >> statement?
> >> You can listen to those with the SqlConnection.InfoMessage event.
> >>
> >> SqlConnection connection = new SqlConnection(connectionstring);
> >> connection.InfoMessage += new
SqlInfoMessageEventHandler(InfoMessage);
> >> connection.Open();
> >>
> >> private static void InfoMessage(object sender, SqlInfoMessageEventArgs
> > args)
> >> {
> >> foreach(SqlError arg in args.Errors) {
> >> Response.Write("Number={0}, Message={1}", arg.Number,
> >> arg.Message);
> >> }
> >> }
> >>
> >> --
> >> This posting is provided "AS IS", with no warranties, and confers no
> > rights.
> >> Please do not send email directly to this alias. This alias is for
> > newsgroup
> >> purposes only.
> >> "David Carr" <David_Carr@xxxxxxxxxxxxxxxx> wrote in message
> >> news:%2379YZl5nFHA.3304@xxxxxxxxxxxxxxxxxxxxxxx
> >> > Hi there,
> >> >
> >> > My SQL server 2000 stored procedures are generating different error
> > codes.
> >> > I am able to view these error codes when I run the stored proc in
Query
> >> > Analyzer. I am hoping to be able to obtain in my C# web service.
> >> > Specifically, I am using SqlCommand and SqlDataReader objects for
> >> > executing
> >> > the stored procedure.
> >> >
> >> > Is there a simply mechanism/method to call to obtain the return code?
> >> > I
> >> > have seen some mention of the stored procedure having to stuff it
into
> >> > a
> >> > parameter - I am hoping that I don't have to modify each of my stored
> >> > procs
> >> > to do that.
> >> >
> >> > Thanks in advance,
> >> > David
> >> >
> >> >
> >>
> >>
> >
> >
>
>
.
- References:
- How to retrieve stored procedure error code in C#?
- From: David Carr
- Re: How to retrieve stored procedure error code in C#?
- From: Mark Ashton
- Re: How to retrieve stored procedure error code in C#?
- From: David Carr
- Re: How to retrieve stored procedure error code in C#?
- From: Mark Ashton
- How to retrieve stored procedure error code in C#?
- Prev by Date: Re: Server.Execute (open form in new window)
- Next by Date: vb.net array to SQL server table
- Previous by thread: Re: How to retrieve stored procedure error code in C#?
- Next by thread: How to Update records when no duplicates allowed
- Index(es):
Relevant Pages
|
