Getting value after executing a stored procedure in ASP.net

Hello,

I am getting the value from the stored procedure

/////////////////////////////////////////////////////////////////////////////////////////////////////////
      public int LogRecipients()
      /////////////////////////////////////////////////////////////////////////////////////////////////////////
      {
            if (!Log || Db == null || Recipients.Count == 0)
                  return -1;

        Db.InitialiseProcedure("z360core_insert_mail_message");

            Db.SetParam("@subject", Msg.Subject, System.Data.DbType.String  );
            Db.SetParam("@recipient", Recipients[0].ToString(), System.Data.DbType.String  );
            Db.SetParam("@sender", Msg.FromAddress, System.Data.DbType.String  );
            Db.SetParam("@email_date", System.DateTime.Now, System.Data.DbType.DateTime );
            Db.SetParam("@processed", System.DateTime.Now, System.Data.DbType.DateTime );
            Db.SetParam("@processed_by", App.UserId, System.Data.DbType.Int32 );
            Db.SetParam("@ticket_number", TicketNumber, System.Data.DbType.Int32 );

            if (ParentMailMessageId != "")
                  Db.SetParam("@mail_message_id", ParentMailMessageId, System.Data.DbType.Int32 );

            int MailMessageId = int.Parse(Db.ExecuteScalarProcedure().ToString());
       

            if (MailMessageId < 0)
                  return -1;

Return MailMessageId ;

}


and below is my stored procedure

ALTER proc [dbo].[z360core_Insert_Mail_Message]
(
@Recipient nvarchar (100) = null,
@Subject nvarchar (250) = null,
@sender nvarchar (250)  = null,
@sender_name nvarchar (250)  = null,
@message_id varchar (250)  = null,
@content_type varchar (50)  = null,
@email_date datetime  = null,
@mail_message_id int  = null,
@processed datetime  = null,
@processed_by int  = null,
@ticket_number int = null
)
AS
if (@message_id is not null)
begin
      if Exists( select 0 from z360core_mail_message where message_id = @message_id and recipient = @Recipient )
      BEGIN
            select -1
            return
      END
end

INSERT INTO z360core_mail_message (
      recipient,
      Subject ,
      sender ,
      sender_name,
      message_id ,
      content_type ,
--      raw_message ,
      email_date,
      zmail_template_id,
      processed,
      processed_by,
      ticket_number
      )
       VALUES (
      @Recipient  ,
      @Subject  ,
      Lower(@sender)  ,
      @sender_name,
      @message_id  ,
      @content_type  ,
--      @raw_message  ,
      @email_date,
      @mail_message_id,
      @processed,
      @processed_by,
      @ticket_number
      )
       
      select  @@IDENTITY



I am getting the jsut inserted identity value like this.
int MailMessageId = int.Parse(Db.ExecuteScalarProcedure().ToString());

I just wanted to know if this is the one causing errors in other functions. Thanks in advance..

sureshrainaAsked:
Who is Participating?
 
vinodhsomasekharanCommented:

Hai,

      Since u r returning a value from a stored procedure try using output parameter..

so u r stored procedure will have a new output parameter...

ALTER proc [dbo].[z360core_Insert_Mail_Message]
(
@Recipient nvarchar (100) = null,
@Subject nvarchar (250) = null,
@sender nvarchar (250)  = null,
@sender_name nvarchar (250)  = null,
@message_id varchar (250)  = null,
@content_type varchar (50)  = null,
@email_date datetime  = null,
@mail_message_id int  = null,
@processed datetime  = null,
@processed_by int  = null,
@ticket_number int = null,
@messageId int OUTPUT                 -- output parameter to be added which returns the new identity value
)
AS
..etc

and instead of "select  @@IDENTITY"
just give
" set @messageId = @@IDENTITY "

=============

now in your asp.net code.. just add another parameter [output direction] for the stored procedure
eg..
Db.SetParam("@messageId ", System.Data.DbType.Int32 );
.Direction = ParameterDirection.Output   // mention the parameter as output param..

regards
Vinodh
0
 
Hamed ZaghaghiProgrammerCommented:
best to use SqlHelper

and do like this
SqlHelper.ExecuteScalar(ConnectionString,"SPNAME", value1, value2, ... )

or

DataReader dr = SqlHelper.ExecuteReader(ConnectionString,"SPNAME", value1, value2, ... )
dr.read()
then use dr[0] and convert it to correct type
dr.close()
0
 
CyberneticsConnoisseurCommented:
Hi Suresh,

You haven't given any details about the error you are receiving.

As per my assumption, you might be receiving an error if the insert statement itself fails in the stored procedure due to which there is an SQL Error.

There is no error handling done in the SP.

To know if there is an SQL error in the SP
write this before the insert statement


Declare @Error int, @Identity int


and just after the insert statement, instead of writing ' select  @@IDENTITY' , write


Select @Error = @@Error, @Identity = @@Identity

IF (@Error = 0)
      select @Identity
Else
      select -1



In the code you will have to check the value of
 'MailMessageId' for any errors before proceeding with your logic.

If you give the error details, I guess I'll be able to help better.

Carpe Diem
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
sureshrainaAuthor Commented:
The error I am getting is 'Input String is not in Correct Format'
0
 
Hamed ZaghaghiProgrammerCommented:
Db.ExecuteScalarProcedure don't return an int value, go ion debug and check the value
0
 
Elvio Lujan.Net Senior DeveloperCommented:
change "     select  @@IDENTITY"
for
"     return  @@IDENTITY"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.